In: Advanced Math
1. Southwest Clothing Company, produces denim jeans, is planning to introduce a new line of lime green jeans. Each pair of jeans costs $5.50 for fabric and $3.00 for labor. Each pair will be sold for $24.99 each. Fixed costs are estimated as $10,000 per month. Southwest intends to build an EXCEL model to explore the options for production.
a. Set up a spreadsheet model to calculate total profit with production quantity as the decision variable - assuming that Southwest will sell all of the jeans that are produced.
b. Use the Excel tool Data/What-If Analysis/Goal Seek to determine the break-even quantity for the jeans.
c. Use the Excel tool Data/What-If Analysis/Data Table to create a one-way table to demonstrate the sensitivity of profit to changes in selling price per pair of jeans
Use prices of 21.99 23.99 25.99, 27.99 and 31.99 in your table.
d. Create a graph of the one-way table results.
(Dollars) | (Dollars) | (Dollars) | (Dollars) | (Dollars) | (Dollars) | ||||||
Price (Per Unit) | 24.99 | 21.99 | 23.99 | 25.99 | 27.99 | 31.99 | |||||
Units Sold | 606.4281 | 606.4281 | 606.4281 | 606.4281 | 606.4281 | 606.4281 | |||||
Total Revenue | 15154.64 | 13335.35 | 14548.21 | 15761.07 | 16973.92 | 19399.64 | |||||
Fixed Cost | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | |||||
Fabric Cost (Per Unit) | 5.5 | 5.5 | 5.5 | 5.5 | 5.5 | 5.5 | |||||
Labor Cost (Per Unit) | 3 | 3 | 3 | 3 | 3 | 3 | |||||
Cost (Per Unit) | 8.5 | 8.5 | 8.5 | 8.5 | 8.5 | 8.5 | |||||
Total Cost | 15154.64 | 15154.64 | 15154.64 | 15154.64 | 15154.64 | 15154.64 | |||||
Profit | 0 | -1819.28 | -606.428 | 606.4281 | 1819.284 | 4244.997 |
Price (Per Unit) | Profit (606.4281 units sold) |
21.99 | -1819.284415 |
23.99 | -606.4281383 |
24.99 | 0 |
25.99 | 606.4281383 |
27.99 | 1819.284415 |
31.99 | 4244.996968 |