Excel Case
Sales Calculation
| Wheels of Fortune Sales | ||
| Year 1 | Year 2 | |
| Total Bicycles Sold | ||
| Sales Mix | ||
| Professional | ||
| Novice | ||
| Bicycles Sold (Units) | ||
| Professional | ||
| Novice | ||
| Total | ||
| Selling Price per Bicycle | ||
| Professional | ||
| Novice | ||
| Total Sales (Dollars) | ||
| Professional | ||
| Novice | ||
| Total | ||
Std Variable Cost_2a
| Wheels of Fortune Variable Cost and | ||
| Contribution Margin per Bicycle | ||
| Direct Materials Per Bicycle | ||
| Professional | Novice | |
| Bicycle Kit (Direct Material) | ||
| Direct Labor Cost Per Bicycle | ||
| Professional | Novice | |
| Hours | ||
| Rate per Hour | ||
| Total Direct Labor Cost per Bicycle | ||
| Variable Overhead Cost Per Bicycle | ||
| Professional | Novice | |
| Total Variable Overhead Cost per Bicycle | ||
| 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 | ||
| Direct Labor | ||
| Variable Overhead | ||
| Total Variable Cost per Bicycle | ||
| Selling Price per Bicycle | ||
| Contribution Margin per Bicycle | ||
| Contribution Margin Percent per Bicycle | ||
Sales Mix Variance_2b
| Wheels of Fortune - Sales Mix Variance | ||||||||||
| Year 1 | Year 2 | |||||||||
| Budgeted | Actual | Variance | Budgeted | Actual | Variance | |||||
| Unit Sales - Novice | ||||||||||
| Contribution Margin per bicycle (Sales - DM, DL, VOH) | ||||||||||
| Unit Sales - Professional | ||||||||||
| Contribution Margin per bicycle (Sales - DM, DL, VOH) | ||||||||||
| Total contribution margin ((Novice Sales * Novice CM/unit) + (Prof Sales * Prof CM/Unit)) | ||||||||||
Direct Material Variance_2c
| Wheels of Fortune Direct Materials Variance | ||||||
| Direct Material Standard Cost | Per Kit | Bicycles Assembled | 2016 | 2017 | ||
| Professional | Year 1 | Year 2 | ||||
| Novice | Professional | |||||
| Novice | ||||||
| Direct Materials Actual Cost Per Unit | Total | |||||
| Bicycle Kits (per unit) | Year 1 | Year 2 | ||||
| Professional | Direct Materials Actual Usage | |||||
| Novice | Bicycle Kits | Year 1 | Year 2 | |||
| Professional | ||||||
| Direct Material Price Variance | Novice | |||||
| Total Material Price | Year 1 | Year 2 | ||||
| Professional | ||||||
| Novice | ||||||
| Total Direct Material Price Var | ||||||
| Direct Material Usage Variance | ||||||
| Total Direct Material Usage | Year 1 | Year 2 | ||||
| Professional | ||||||
| Novice | ||||||
| Total Direct Material Usage | ||||||
| Total Direct Material Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | ||||||
| Novice | ||||||
| Total Direct Material Variance | ||||||
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 | Professional | |||||
| Novice | Novice | |||||
| Standard Labor Rate | Total | |||||
| Professional | ||||||
| Novice | Actual Direct Labor Rates, Hours, Dollars | |||||
| Total Standard Hours | Actual Labor Rate | Year 1 | Year 2 | |||
| Professional | Professional | |||||
| Novice | Novice | |||||
| Total Standard Hours | ||||||
| Total Actual Hours | ||||||
| Direct Labor Rate Variance | Professional | |||||
| Year 1 | Year 2 | Novice | ||||
| Professional | Total Actual Hours | |||||
| Novice | ||||||
| Total Direct Labor Rate | Total Actual Hours per Bicycle | |||||
| Professional | ||||||
| Direct Labor Efficiency Variance | Novice | |||||
| Year 1 | Year 2 | |||||
| Professional | Total Actual Dollars | |||||
| Novice | Professional | |||||
| Total Labor Efficiency | Novice | |||||
| Total Actual Dollars | ||||||
| Total Direct Labor Variance | ||||||
| Year 1 | Year 2 | |||||
| Professional | ||||||
| Novice | ||||||
| Total Direct Labor Variance | ||||||
Variable Overhead Variances_2c
| Wheels of Fortune Variable Overhead Variance | ||||||
| Bicycles Assembled | 2016 | 2017 | ||||
| Year 1 | Year 2 | |||||
| Professional | ||||||
| Novice | ||||||
| Total | ||||||
| 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 | Professional | |||||
| Novice | Novice | |||||
| Total Standard Variable Overhead | Total Actual Variable Overhead | |||||
| Professional | Professional | |||||
| Novice | Novice | |||||
| Total Standard Variable Overhead | Total Actual Variable Overhead | |||||
| Variable Overhead Variance | ||||||
| Variable Overhead Variance | Year 1 | Year 2 | ||||
| Professional | ||||||
| Novice | ||||||
| Total Variable Overhead Variance | ||||||
SG&A_2d
| Wheels of Fortune Selling & General Admin Variance | ||
| Actual Selling & General Admistration | ||
| Actual Selling & General Administration | Year 1 | Year 2 |
| Director Salaries | ||
| Total Acutal Selling & General Admin | ||
| Selling & General Admin Variances | ||
| Year 2 | ||
| Director Salaries | ||
| Total Selling & General Admin Variances | ||
Income Reconciliation_2e
| Wheels of Fortune | |
| Income Reconciliation | |
| Difference | |
| Operating Income 2020 | |
| Total Sales Mix Variance | |
| Manufacturing Variances | |
| Direct Materials Variance | |
| Usage | |
| Price | |
| Direct Labor Variance | |
| Efficiency | |
| Rate | |
| Variable Overhead Variance | |
| Spending | |
| Total Manufacturing Variances | |
| Selling & General Administration | |
| Operating Income 2021 | |
| (unfavorable) | |
| **This number needs to balance from your financials with a formula. | |
Contribution Margin IS_2f
| Wheels of Fortune | ||||
| Comparative Income Statement | ||||
| Bicycles Sold | - 0 | - 0 | ||
| 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 | ||||||