HELP
anneN
np_excel2013_t10_p1a_1.xlsxDocumentation
| New Perspectives Excel 2013 |
| Tutorial 10: SAM Project 1a |
| Firestone Clock Company |
| WHAT-IF ANALYSES AND SCENARIOS |
| Author: |
| Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. |
Desktop Models
| Firestone Clocks - Desktop Models | |||||||||||
| Desktop Models - Profit Analysis Based on Units Sold | |||||||||||
| Sales | Units Sold | Total Sales | Total Expenses | Gross Profit | |||||||
| Units Sold | 35,000 | ||||||||||
| Price Per Unit | $ 27.00 | 20,000 | |||||||||
| Total Sales | $ 945,000.00 | 25,000 | |||||||||
| 30,000 | |||||||||||
| Expenses | 35,000 | ||||||||||
| Variable Expenses | 40,000 | ||||||||||
| Units Manufactured | 35,000 | ||||||||||
| Variable Cost per Unit | $ 16.55 | ||||||||||
| Total Variable Costs | $ 579,250.00 | Desktop Models - Gross Profit Analysis Based on Units Sold and Price Per Unit | |||||||||
| Fixed Expenses | $ 115,750 | $ 22.50 | $ 24.00 | $ 25.50 | $ 27.00 | $ 28.50 | $ 30.00 | $ 31.50 | |||
| Total Fixed Cost | $ 250,000.00 | 20,000 | |||||||||
| Expenses - Total | $ 829,250.00 | 25,000 | |||||||||
| Total Expense Per Unit Sold | $ 23.69 | 30,000 | |||||||||
| 35,000 | |||||||||||
| Profit | 40,000 | ||||||||||
| Total Sales | $ 945,000.00 | ||||||||||
| Total Expenses | $ 829,250.00 | ||||||||||
| Gross Profit | $ 115,750.00 | ||||||||||
| Break-even Analysis | |||||||||||
| Price per Unit | $ 27.00 | ||||||||||
| Cost Per Unit | $ 23.69 | ||||||||||
| Units Sold | 35,000 | ||||||||||
| Gross Profit | $ 115,750.00 |
Wall Units
| Firestone Clocks - Wall Units | |||||||
| Wall Units - Break-Even Analysis | |||||||
| Sales | Units Sold | Total Sales | Total Expenses | Gross Profit | |||
| Units Sold | 7,250 | 7,250 | 435,000.00 | 419,687.50 | 15,312.50 | ||
| Price Per Unit | $ 60.00 | 5,000 | 300,000.00 | 343,750.00 | (43,750.00) | ||
| Total Sales | $ 435,000.00 | 5,500 | 330,000.00 | 360,625.00 | (30,625.00) | ||
| 6,000 | 360,000.00 | 377,500.00 | (17,500.00) | ||||
| Expenses | 6,500 | 390,000.00 | 394,375.00 | (4,375.00) | |||
| Variable Expenses | 7,000 | 420,000.00 | 411,250.00 | 8,750.00 | |||
| Units Manufactured | 7,250 | 7,500 | 450,000.00 | 428,125.00 | 21,875.00 | ||
| Variable Cost per Unit | $ 33.75 | 8,000 | 480,000.00 | 445,000.00 | 35,000.00 | ||
| Total Variable Costs | $ 244,687.50 | 8,500 | 510,000.00 | 461,875.00 | 48,125.00 | ||
| Fixed Costs | 9,000 | 540,000.00 | 478,750.00 | 61,250.00 | |||
| Total Fixed Cost | $ 175,000.00 | ||||||
| Expenses - Total | $ 419,687.50 | ||||||
| Total Expense Per Unit Sold | $ 57.89 | ||||||
| Profit | |||||||
| Total Sales | $ 435,000.00 | ||||||
| Total Expenses | $ 419,687.50 | ||||||
| Gross Profit | $ 15,312.50 | ||||||
| Break-even Analysis | |||||||
| Price per Unit | $ 60.00 | ||||||
| Cost Per Unit | $ 57.89 | ||||||
| Units Sold | 7,250 | ||||||
| Gross Profit | $ 15,312.50 |
Custom Clocks
| Firestone Clocks - Custom Clocks | |||||||||
| Custom Clocks - Net Income Analysis | |||||||||
| Sales | Average Price per Unit | ||||||||
| Units Sold | 5,500 | 43750.0 | $ 100.00 | $ 105.00 | $ 110.00 | $ 115.00 | $ 120.00 | ||
| Price Per Unit | $ 110.00 | 3,000 | (142,500.00) | (127,500.00) | (112,500.00) | (97,500.00) | (82,500.00) | ||
| Total Sales | $ 605,000.00 | 4,000 | (90,000.00) | (70,000.00) | (50,000.00) | (30,000.00) | (10,000.00) | ||
| 4,500 | (63,750.00) | (41,250.00) | (18,750.00) | 3,750.00 | 26,250.00 | ||||
| Expenses | 5,000 | (37,500.00) | (12,500.00) | 12,500.00 | 37,500.00 | 62,500.00 | |||
| Variable Expenses | 5,500 | (11,250.00) | 16,250.00 | 43,750.00 | 71,250.00 | 98,750.00 | |||
| Units Manufactured | 5,500 | 6,000 | 15,000.00 | 45,000.00 | 75,000.00 | 105,000.00 | 135,000.00 | ||
| Variable Cost per Unit | $ 47.50 | 6,500 | 41,250.00 | 73,750.00 | 106,250.00 | 138,750.00 | 171,250.00 | ||
| Total Variable Costs | $ 261,250.00 | 7,000 | 67,500.00 | 102,500.00 | 137,500.00 | 172,500.00 | 207,500.00 | ||
| Fixed Costs | 7,500 | 93,750.00 | 131,250.00 | 168,750.00 | 206,250.00 | 243,750.00 | |||
| Total Fixed Cost | $ 300,000.00 | ||||||||
| Expenses - Total | $ 561,250.00 | ||||||||
| Total Expense Per Unit Sold | $ 102.05 | ||||||||
| Profit | |||||||||
| Total Sales | $ 605,000.00 | ||||||||
| Total Expenses | $ 561,250.00 | ||||||||
| Gross Profit | $ 43,750.00 | ||||||||
| Break-even Analysis | |||||||||
| Price per Unit | $ 110.00 | ||||||||
| Cost Per Unit | $ 97.50 | ||||||||
| Units Sold | 6,000 | ||||||||
| Gross Profit | $ 75,000.00 |
Custom Clock - Suppliers
| Firestone Clocks - Custom Suppliers | |||||
| Gerner's Woodshop | Reuter and Sons | Abe's Precision Woodworkers | Total | ||
| Units Produced | 1,500 | 2,000 | 2,000 | 5,500 | |
| Fixed Costs | $ 50,000.00 | $ 100,000.00 | $ 150,000.00 | ||
| Fixed Cost per Unit | $ 33.33 | $ 50.00 | $ 75.00 | ||
| Variable Costs | $ 72,675.00 | $ 96,599.86 | $ 96,300.00 | ||
| Variable Cost per Unit | $ 48.45 | $ 48.30 | $ 48.15 | ||
| Total Costs | $ 122,675 | $ 196,600 | $ 246,300 | $ 565,575 | |
| Minimum Total Cost Model |
All Products
| Firestone Clocks - Product Mix | ||||
| Desktop Models | Wall Units | Custom | ||
| Revenues | ||||
| Units Sold | 35,000 | 7,250 | 5,500 | |
| Price Per Unit | $ 27.00 | $ 60.00 | $ 110.00 | |
| Total Sales | $ 945,000.00 | $ 435,000.00 | $ 605,000.00 | |
| Cost of Goods Sold | ||||
| Units Manufactured | 35,000 | 7,250 | 5,500 | |
| Fixed Costs | $ 250,000.00 | $ 175,000.00 | $ 300,000.00 | |
| Variable Cost per Unit | $ 16.55 | $ 32.87 | $ 47.50 | |
| Variable Costs | $ 579,250.00 | $ 238,307.50 | $ 261,250.00 | |
| Total Costs | $ 829,250.00 | $ 413,307.50 | $ 561,250.00 | |
| Total Profit | $ 115,750 | $ 21,693 | $ 43,750 | |
| Profit per Unit Sold | $3.31 | $2.99 | $7.95 | |
