Account Question
Samna
WheelsofFortuneExcel.xlsxSales Calculation
| Wheels of Fortune Sales | ||
| Year 1 | Year 2 | |
| Total Bicycles Sold | 4,500 | 4,500 |
| Sales Mix | ||
| Professional | 75% | 50% |
| Novice | 25% | 50% |
| Bicycles Sold (Units) | ||
| Professional | 3,375 | 2,250 |
| Novice | 1,125 | 2,250 |
| Total | 4,500 | 4,500 |
| Selling Price per Bicycle | ||
| Professional | $ 5,500 | $ 5,500 |
| Novice | $ 1,100 | $ 1,100 |
| Total Sales (Dollars) | ||
| Professional | $ 18,562,500 | $ 12,375,000 |
| Novice | $ 1,237,500 | $ 2,475,000 |
| Total | $ 19,800,000 | $ 14,850,000 |
Std Variable Cost_2a
| Wheels of Fortune Variable Cost and | ||||
| Contribution Margin per Bicycle | ||||
| Direct Materials Per Bicycle | ||||
| Professional | Novice | |||
| Bicycle Kit (Direct Material) | $ 3,000 | $ 600 | ||
| Direct Labor Cost Per Bicycle | ||||
| Professional | Novice | |||
| Hours | 10 | 6 | ||
| Rate per Hour | $ 40 | $ 30 | ||
| Total Direct Labor Cost per Bicycle | $ 400 | $ 180 | ||
| Variable Overhead Cost Per Bicycle | ||||
| Professional | Novice | |||
| Total Variable Overhead Cost per Bicycle | $ 150 | $ 50 | ||
| Total Variable Cost and Contribution Margin per Bicycle | ||||
| Budget Production/Sales | ||||
| Budgeted Direct Labor Hours | ||||
| Budgeted Fixed Overhead Dollars | ||||
| Direct Labor Hours per Bicycyle | ||||
| Fixed Overhead Cost per Bicycle | ||||
| Direct Materials | $ 3,000 | $ 600 | ||
| Direct Labor | $ 400 | $ 180 | ||
| Variable Overhead | $ 150 | $ 50 | ||
| Total Variable Cost per Bicycle | $ 3,550 | $ 830 | ||
| Selling Price per Bicycle | $ 5,500 | $ 1,100 | ||
| Contribution Margin per Bicycle | $ 1,950 | $ 270 | $ 6,885,000 | |
| Contribution Margin Percent per Bicycle | 35.45% | 24.55% | ||
Sales Mix Variance_2b
| Wheels of Fortune - Sales Mix Variance | ||||||||||
| Year 1 | Year 2 | |||||||||
| Budgeted | Actual | Variance | Budgeted | Actual | Variance | |||||
| Unit Sales - Novice | 1,125 | 1,125 | - | 1,125 | 2,250 | 1,125 | ||||
| Contribution Margin per bicycle (Sales - DM, DL, VOH) | ||||||||||
| Unit Sales - Professional | 3,375 | 3,375 | - | 3,375 | 2,250 | (1,125) | ||||
| Contribution Margin per bicycle (Sales - DM, DL, VOH) | ||||||||||
| Total contribution margin ((Novice Sales * Novice CM/unit) + (Prof Sales * Prof CM/Unit)) | $ 6,885,000 | $ 6,885,000 | $ - 0 | $ 6,885,000 | $ 4,995,000 | $ (1,890,000) | ||||
Direct Material Variance_2c
| Wheels of Fortune Direct Materials Variance Frames | ||||||
| Direct Material Standard Cost | Frames | Bicycles Assembled | 2016 | 2017 | ||
| Professional | $ 900 | Year 1 | Year 2 | |||
| Novice | $ 180 | Professional | 3,375 | 2,250 | ||
| Novice | 1,125 | 2,250 | ||||
| Direct Materials Actual Cost Per Unit | Total | 4,500 | 4,500 | |||
| Bicycle Kits (per unit) | Year 1 | Year 2 | ||||
| Professional | $ 900 | $ 925 | Direct Materials Actual Usage | |||
| Novice | $ 180 | $ 190 | Bicycle Frame | Year 1 | Year 2 | |
| Professional | 3,375 | 2,363 | ||||
| Direct Material Price Variance | Novice | 1,125 | 2,318 | |||
| Total Material Price | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ (56,250) | ||||
| Novice | $ - 0 | $ (22,500) | ||||
| Total Direct Material Price Var | ||||||
| Direct Material Usage Variance | ||||||
| Total Direct Material Usage | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ (101,700) | ||||
| Novice | $ - 0 | $ (12,240) | ||||
| Total Direct Material Usage | $ - 0 | $ (113,940) | ||||
| Total Direct Material Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | $ - 0 | $ (157,950) | ||||
| Novice | $ - 0 | $ (34,740) | ||||
| Total Direct Material Variance | $ - 0 | $ (192,690) | ||||
| Wheels of Fortune Direct Materials Variance Parts | ||||||
| Direct Material Standard Cost | Parts | Bicycles Assembled | 2016 | 2017 | ||
| Professional | $ 2,100 | Year 1 | Year 2 | |||
| Novice | $ 420 | Professional | 3,375 | 2,250 | ||
| Novice | 1,125 | 2,250 | ||||
| Direct Materials Actual Cost Per Unit | Total | |||||
| Bicycle Kits (per unit) | Year 1 | Year 2 | ||||
| Professional | $ 2,100 | $ 2,165 | Direct Materials Actual Usage | |||
| Novice | $ 420 | $ 450 | Bicycle Frame | Year 1 | Year 2 | |
| Professional | 3,375 | 2,295 | ||||
| Direct Material Price Variance | Novice | 1,125 | 2,363 | |||
| Total Material Price | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ (146,250) | ||||
| Novice | $ - 0 | $ (67,500) | ||||
| Total Direct Material Price Var | $ - 0 | $ (213,750) | ||||
| Direct Material Usage Variance | ||||||
| Total Direct Material Usage | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ (94,500) | ||||
| Novice | $ - 0 | $ (47,460) | ||||
| Total Direct Material Usage | $ - 0 | $ (141,960) | ||||
| Total Direct Material Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | $ - 0 | $ (240,750) | ||||
| Novice | $ - 0 | $ (114,960) | ||||
| Total Direct Material Variance | $ - 0 | $ (355,710) | ||||
| Direct Materials Price Variance | 2020 | 2021 | ||||
| Professional Frames | $ - 0 | $ (56,250) | ||||
| Professional Parts | $ - 0 | $ (146,250) | ||||
| Novice Frames | $ - 0 | $ (22,500) | ||||
| Novice Parts | $ - 0 | $ (67,500) | ||||
| Total Direct Materials Price Variance | $ - 0 | $ (292,500) | ||||
| Direct Materials Usage Variance | ||||||
| Professional Frames | $ - 0 | $ (101,700) | ||||
| Professional Parts | $ - 0 | $ (94,500) | ||||
| Novice Frames | $ - 0 | $ (12,240) | ||||
| Novice Parts | $ - 0 | $ (47,460) | ||||
| Total Direct Materials Usage Variance | $ - 0 | $ (255,900) | ||||
Direct Labor Variances_2c
| Wheels of Fortune Direct Labor Variance | ||||||
| Standard Direct Labor Rates and Hours | Bicycles Assembled | |||||
| Standard Hours per Bicycle | Year 1 | Year 2 | Year 1 | Year 2 | ||
| Professional | 10 | 10 | Professional | 3,375 | 2,250 | |
| Novice | 6 | 6 | Novice | 1,125 | 2,250 | |
| Standard Labor Rate | Total | 4,500 | 4,500 | |||
| Professional | $ 40 | $ 40 | ||||
| Novice | $ 30 | $ 30 | Actual Direct Labor Rates, Hours, Dollars | |||
| Total Standard Hours | Actual Labor Rate | Year 1 | Year 2 | |||
| Professional | 33,750 | 22,500 | Professional | $ 40 | $ 42 | |
| Novice | 6,750 | 13,500 | Novice | $ 30 | $ 35 | |
| Total Standard Hours | 40,500 | 36,000 | ||||
| Total Actual Hours | ||||||
| Direct Labor Rate Variance | Professional | 33,750 | 23,175 | |||
| Year 1 | Year 2 | Novice | 6,750 | 13,905 | ||
| Professional | $ - 0 | $ (45,000) | Total Actual Hours | 40,500 | 37,080 | |
| Novice | $ - 0 | $ (67,500) | ||||
| Total Direct Labor Rate | $ - 0 | $ (112,500) | Total Actual Hours per Bicycle | |||
| Professional | 10.00 | 10.30 | ||||
| Direct Labor Efficiency Variance | Novice | 6.00 | 6.18 | |||
| Year 1 | Year 2 | |||||
| Professional | $ - 0 | $ (27,000) | Total Actual Dollars | |||
| Novice | $ - 0 | $ (12,150) | Professional | $ 1,350,000 | $ 973,350 | |
| Total Labor Efficiency | $ - 0 | $ (39,150) | Novice | $ 202,500 | $ 486,675 | |
| Total Actual Dollars | $ 1,552,500 | $ 1,460,025 | ||||
| Total Direct Labor Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | $ - 0 | $ (72,000) | ||||
| Novice | $ - 0 | $ (79,650) | ||||
| Total Direct Labor Variance | $ - 0 | $ (151,650) | ||||
Variable Overhead Variances_2c
| Wheels of Fortune Variable Overhead Variance | ||||||
| Bicycles Assembled | 2016 | 2017 | ||||
| Year 1 | Year 2 | |||||
| Professional | 3,375 | 2,250 | ||||
| Novice | 1,125 | 2,250 | ||||
| Total | 4,500 | 4,500 | ||||
| Standard Variable Overhead | Actual Variable Overhead | |||||
| Standard Variable Overhead per Bicycle | Year 1 | Year 2 | Actual Variable Overhead per Bicycle | Year 1 | Year 2 | |
| Professional | $ 150 | $ 150 | Professional | $ 150 | $ 180 | |
| Novice | $ 50 | $ 50 | Novice | $ 50 | $ 65 | |
| Total Standard Variable Overhead | Total Actual Variable Overhead | |||||
| Professional | $ 506,250 | $ 337,500 | Professional | $ 506,250 | $ 405,000 | |
| Novice | $ 56,250 | $ 112,500 | Novice | $ 56,250 | $ 146,250 | |
| Total Standard Variable Overhead | $ 562,500 | $ 450,000 | Total Actual Variable Overhead | $ 562,500 | $ 551,250 | |
| Variable Overhead Variance | ||||||
| Variable Overhead Variance | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ (67,500) | ||||
| Novice | $ - 0 | $ (33,750) | ||||
| Total Variable Overhead Variance | $ - 0 | $ (101,250) | ||||
SG&A_2d
| Wheels of Fortune Selling & General Admin Variance | ||
| Actual Selling & General Admistration | ||
| Actual Selling & General Administration | Year 1 | Year 2 |
| Director Salaries | $ 615,000 | $ 765,000 |
| Total Acutal Selling & General Admin | ||
| Selling & General Admin Variances | ||
| Year 2 | ||
| Director Salaries | ||
| Total Selling & General Admin Variances | $ (150,000) | |
Income Reconciliation_2e
| Wheels of Fortune | |||||
| Income Reconciliation | |||||
| Difference | |||||
| Operating Income 2020 | $ 6,270,000 | ||||
| Total Sales Mix Variance | $ (1,890,000) | ||||
| Manufacturing Variances | |||||
| Direct Materials Variance | |||||
| Usage | $ (255,900) | ||||
| Price | (292,500) | ||||
| Total | (548,400) | ||||
| Direct Labor Variance | |||||
| Efficiency | (39,150) | ||||
| Rate | (151,650) | ||||
| Total | (190,800) | ||||
| Variable Overhead Variance | |||||
| Spending | (101,250) | ||||
| Total Manufacturing Variances | |||||
| Selling & General Administration | $ (150,000) | ||||
| Operating Income 2021 | $ 3,389,550 | 2021 Income for IS | Difference | ||
| (unfavorable) | (2,880,450) | 3,415,505 | $ (25,955) | ||
| **This number needs to balance from your financials with a formula. | |||||
Contribution Margin IS_2f
| Wheels of Fortune | ||||
| Comparative Income Statement | ||||
| Bicycles Sold | 4,500 | 4,500 | ||
| Year 1 | Per Bicycle | Year 2 | Per Bicycle | |
| Sales Dollars | ||||
| Variable Cost (at standard) | ||||
| Contribution Margin | ||||
| Contribution Margin Percent | ||||
| Manufacturing Variances (Unfavorable) | ||||
| Adjusted Contribution Margin | ||||
| Adjusted Contribution Margin Percent | ||||
| Selling & General Administration | ||||
| Operating Income | ||||
| (Unfavorable) | ||||
MarginofSafety_2g
| Wheels of Fortune Margin of Safety | ||||||
| Bicycles Sold | Actual Sales and Selling & General Administration | |||||
| Year 1 | Year 2 | Year 1 | Year 2 | |||
| Professional | Total Sales | |||||
| Novice | ||||||
| Total Sales | Selling & General Administration | |||||
| Weighted Average Sales Price | Weighted Average Variable Costs | |||||
| Sales Price per Unit | Year 1 | Year 2 | Variable Cost per Unit | Year 1 | Year 2 | |
| Professional | Professional | |||||
| Novice | Novice | |||||
| WA Selling Price per Unit | WA Variable Cost per Unit | |||||
| Professional | Professional | |||||
| Novice | Novice | |||||
| Average Sales Price per Unit | Average Sales Price per Unit | |||||
| Weighted Average Contribution Margin | Margin of Safety | |||||
| Year 1 | Year 2 | Year 1 | Year 2 | |||
| Weighted Average CM | Margin of Safety Dollars | |||||
| Weighted Average CM per Unit | Margin of Safety as % of Sales | |||||
| Weighted Average CM Ratio | Margin of Safety per Unit | |||||
| Breakeven | ||||||
| Year 1 | Year 2 | |||||
| Breakeven in Sales Dollars | ||||||
| Breakeven in Sales Units | ||||||
