Paper Analysis
orangeflavor
WheelsCaseExcel-3.xlsxSales Calculation
| Wheels of Fortune Sales | ||
| Year 1 | Year 2 | |
| Total Bicycles Sold | 4,500 | 4,500 |
| Sales Mix | ||
| Professional | 75% | 25% |
| Novice | 25% | 75% |
| Bicycles Sold (Units) | ||
| Professional | 3,375 | 1,125 |
| Novice | 1,125 | 3,375 |
| 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 | $ 6,187,500 |
| Novice | $ 1,237,500 | $ 3,712,500 |
| Total | $ 19,800,000 | $ 9,900,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 |
| 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 | 1125 | 1,125 | - | 1,125 | 3,375 | - | ||||
| Contribution Margin per bicycle (Sales - DM, DL, VOH) | $ 270 | $ 270 | $ 270 | $ 270 | $ 270 | $ 270 | ||||
| Unit Sales - Professional | 3,375 | 3,375 | - | 3,375 | 1,125 | (2,250) | ||||
| Contribution Margin per bicycle (Sales - DM, DL, VOH) | 1950 | $ 1,950 | $ 1,950 | $ 1,950 | $ 1,950 | $ 1,950 | ||||
| Total contribution margin ((Novice Sales * Novice CM/unit) + (Prof Sales * Prof CM/Unit)) | $ 6,885,000 | $ 6,885,000 | $ - 0 | $ 6,885,000 | $ 3,105,000 | $ (3,780,000) | ||||
Direct Material Variance_2c
| Wheels of Fortune Direct Materials Variance Frames | ||||||
| Direct Material Standard Cost | Frames | Bicycles Assembled | ||||
| Professional | $ 900 | Year 1 | Year 2 | |||
| Novice | $ 180 | Professional | 3,375 | 1,125 | ||
| Novice | 1,125 | 3,375 | ||||
| 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 | 3375 | 1181 | ||||
| Direct Material Price Variance | Novice | 1125 | 3476 | |||
| Total Material Price | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ 29,525 | ||||
| Novice | $ - 0 | $ 34,760 | ||||
| Total Direct Material Price Var | $ - 0 | $ 64,285 | ||||
| Direct Material Usage Variance | ||||||
| Total Direct Material Usage | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ 50,400 | ||||
| Novice | $ - 0 | $ 18,180 | ||||
| Total Direct Material Usage | $ - 0 | $ 68,580 | ||||
| Total Direct Material Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | $ - 0 | $ 79,925 | ||||
| Novice | $ - 0 | $ 52,940 | ||||
| Total Direct Material Variance | $ - 0 | $ 132,865 | ||||
| Wheels of Fortune Direct Materials Variance Parts | ||||||
| Direct Material Standard Cost | Parts | Bicycles Assembled | ||||
| Professional | $ 2,100 | Year 1 | Year 2 | |||
| Novice | $ 420 | Professional | 3,375 | 1,125 | ||
| Novice | 1,125 | 3,375 | ||||
| Direct Materials Actual Cost Per Unit | Total | 4,500 | 4,500 | |||
| 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 | 3375 | 1148 | ||||
| Direct Material Price Variance | Novice | 1125 | 3544 | |||
| Total Material Price | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ 74,620 | ||||
| Novice | $ - 0 | $ 106,320 | ||||
| Total Direct Material Price Var | $ - 0 | $ 180,940 | ||||
| Direct Material Usage Variance | ||||||
| Total Direct Material Usage | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ 48,300 | ||||
| Novice | $ - 0 | $ 70,980 | ||||
| Total Direct Material Usage | $ - 0 | $ 119,280 | ||||
| Total Direct Material Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | $ - 0 | $ 122,920 | ||||
| Novice | $ - 0 | $ 177,300 | ||||
| Total Direct Material Variance | $ - 0 | $ 300,220 | ||||
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 | 1,125 | |
| Novice | 6 | 6 | Novice | 1,125 | 3,375 | |
| 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 | 11,250 | Professional | $ 40 | $ 42 | |
| Novice | 6,750 | 20,250 | Novice | $ 30 | $ 35 | |
| Total Standard Hours | 40,500 | 31,500 | ||||
| Total Actual Hours | ||||||
| Direct Labor Rate Variance | Professional | 33,750 | 11,588 | |||
| Year 1 | Year 2 | Novice | 6,750 | 20,858 | ||
| Professional | $ - 0 | $ 23,176 | Total Actual Hours | 40,500 | 32,446 | |
| Novice | $ - 0 | $ 104,290 | ||||
| Total Direct Labor Rate | $ - 0 | $ 127,466 | 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 | $ 13,520 | Total Actual Dollars | |||
| Novice | $ - 0 | $ 18,240 | Professional | $ 1,350,000 | $ 486,696 | |
| Total Labor Efficiency | $ - 0 | $ 31,760 | Novice | $ 202,500 | $ 730,030 | |
| Total Actual Dollars | $ 1,552,500 | $ 1,216,726 | ||||
| Total Direct Labor Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | $ - 0 | $ 36,696 | ||||
| Novice | $ - 0 | $ 122,530 | ||||
| Total Direct Labor Variance | $ - 0 | $ 159,226 | ||||
Variable Overhead Variances_2c
| Wheels of Fortune Variable Overhead Variance | ||||||
| \ | ||||||
| Bicycles Assembled | ||||||
| Year 1 | Year 2 | |||||
| Professional | 3,375 | 1,125 | ||||
| Novice | 1,125 | 3,375 | ||||
| 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 | $ 168,750 | Professional | $ 506,250 | $ 202,500 | |
| Novice | $ 56,250 | $ 168,750 | Novice | $ 56,250 | $ 219,375 | |
| Total Standard Variable Overhead | $ 562,500 | $ 337,500 | Total Actual Variable Overhead | $ 562,500 | $ 421,875 | |
| Variable Overhead Variance | ||||||
| Variable Overhead Variance | Year 1 | Year 2 | ||||
| Professional | $ - 0 | $ 33,750 | ||||
| Novice | $ - 0 | $ 50,625 | ||||
| Total Variable Overhead Variance | $ - 0 | $ 84,375 | ||||
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 | $ 615,000 | $ 765,000 |
| Selling & General Admin Variances | ||
| Year 2 | ||
| Director Salaries | $ (150,000) | |
| Total Selling & General Admin Variances | $ (150,000) | unfavorable |
Income Reconciliation_2e
| Wheels of Fortune | |
| Income Reconciliation | |
| Difference | |
| Operating Income 2020 | $ 6,270,000 |
| Total Sales Mix Variance | $ (3,780,000) |
| Manufacturing Variances | |
| Direct Materials Variance | |
| Usage | $ (187,860) |
| Price | (245,225) |
| Direct Labor Variance | |
| Efficiency | (31,760) |
| Rate | -127466 |
| Variable Overhead Variance | |
| Spending | (84,375) |
| Total Manufacturing Variances | $ (676,686) |
| Selling & General Administration | $ (150,000) |
| Operating Income 2021 | $ 1,663,314 |
| (unfavorable) | |
| **This number needs to balance from your financials with a formula. | |
