1 Jesse Jones a budding entrepreneur has set up a small back

1. Jesse Jones, a budding entrepreneur, has set up a small backpack company and wants to distribute the backpacks exclusively through the local bookstore. There are three models, of backpacks: canvas, nylon, and leather. The bookstore expects demand to be somewhere between 15 and 40 for each model and Jesse wants to develop a spreadsheet model to analyze the sensitivity of profit to various inputs. The bookstore has agreed to pay Jesse $35.50 for each canvas backpack, $42.50 for each nylon backpack, and $69.50 for each leather backpack. Labor costs will be $7 per hour for sewing canvas, $8 per hour for sewing nylon and $9 per hour for sewing leather. The following table provides additional information about the production costs for each backpack.

Canvas

Nylon

Leather

Yards per backpack

2.25

2.1

2.6

Cost per yard

$4.50

$7.65

$9.45

Sewing hours per backpack

1.5

1.7

1.9

a. Create a spreadsheet model for Jesse to calculate profit for sales of the backpacks (one model.) Demand and production quantity for each of the three models are uncertain variables.

b. Use Excel Data Table (one-way) to show the profit sensitivity to the per yard cost of leather.

Use cost per yard values 9.00, 9.50, 10.00, 10.50, 11.00, 11.50, 12.00

Create a graph to display results from the Data Table

c. Use Excel Data Table (two-way) to show the profit sensitivity to the demand and production quantity for nylon backpacks.

            Use quantities of 10, 20, 30, 40, 50

(must us IF function)

	Canvas	Nylon	Leather
Yards per backpack	2.25	2.1	2.6
Cost per yard	$4.50	$7.65	$9.45
Sewing hours per backpack	1.5	1.7	1.9

Solution

Answer: Profit per bag canvas nylon leather Demand (in units) canvas nylon leather yards per backpack 2.25 2.1 2.6 15 $223.13 $192.53 $417.45 cost per yard $4.50 $7.65 $9.45 16 $238.00 $205.36 $445.28 Sewing hours per backpack 1.5 1.7 1.9 17 $252.88 $218.20 $473.11 Labour cost/hr $7 $8 $9 18 $267.75 $231.03 $500.94 Selling price $35.50 $42.50 $69.50 19 $282.63 $243.87 $528.77 20 $297.50 $256.70 $556.60 Demand 15 to 40 backpacks 21 $312.38 $269.54 $584.43 22 $327.25 $282.37 $612.26 23 $342.13 $295.21 $640.09 labour cost $10.50 $13.60 $17.10 24 $357.00 $308.04 $667.92 cost(other) $10.13 $16.07 $24.57 25 $371.88 $320.88 $695.75 Total $20.63 $29.67 $41.67 26 $386.75 $333.71 $723.58 27 $401.63 $346.55 $751.41 Selling price $35.50 $42.50 $69.50 28 $416.50 $359.38 $779.24 29 $431.38 $372.22 $807.07 Profit per bag $14.88 $12.84 $27.83 30 $446.25 $385.05 $834.90 31 $461.13 $397.89 $862.73 32 $476.00 $410.72 $890.56 33 $490.88 $423.56 $918.39 34 $505.75 $436.39 $946.22 35 $520.63 $449.23 $974.05 36 $535.50 $462.06 $1,001.88 37 $550.38 $474.90 $1,029.71 38 $565.25 $487.73 $1,057.54 39 $580.13 $500.57 $1,085.37 40 $595.00 $513.40 $1,113.20