Could someone please check my work? Thank you!
Prepare a flexible budget in Excel for Vroom-Vroom.
- Show the flexible budget for December in Contribution Margin Income Statement format.
- Compare Decembers flexible budget to Decembers actual results. Specify which line items are favorable or unfavorable and how much.
- For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for December. Provide potential explanations
- Show the flexible budget for January in Contribution Margin Income Statement format.
- Compare Januarys flexible budget to Januarys actual results. Specify which line items are favorable or unfavorable and how much.
- For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for January. Provide potential explanations for each one.
| Monthly Budget Data : | | | | Actual Data: | | December | January |
| Selling Price Per Unit: | | $ 59.00 | per each | | Production (Units): | | 275,000 | 150,000 |
| Raw Materials Costs | | $ 24.00 | per each | | Revenue | $ 16,375,000.00 | $ 8,845,000.00 |
| Packaging Costs | | $ 15.00 | per each | | Raw Materials | | $ 6,450,000.00 | $ 3,450,000.00 |
| Electricity | $ 4.00 | per each | | Packaging Materials | | $ 4,150,000.00 | $ 2,250,000.00 |
| Waste and Other Costs | | $ 6.00 | per each | | Electricity | $ 1,095,000.00 | $ 575,000.00 |
| Salary and Wages Costs | | $ 350,000.00 | per month | | Waste and Other Costs | | $ 1,775,000.00 | $ 1,025,000.00 |
| Fringe Benefits | | 50% | of salaries | | Wages | $ 375,000.00 | $ 345,000.00 |
| Rent Costs | $ 750,000.00 | per month | | Fringe and Benefits | | $ 175,000.00 | $ 175,000.00 |
| Insurance Costs | | $ 50,000.00 | per month | | Rent | $ 750,000.00 | $ 750,000.00 |
| Depreciation Costs | | $ 250,000.00 | per month | | Insurance | $ 50,000.00 | $ 50,000.00 |
| Assumption | 200,000 | units sold | | Depreciation | | $ 250,000.00 | $ 250,000.00 |
Particulars December Budget Break Down Requested 275,000 $ 24.00 23.45 $ (151,250.00 Raw Material Variance is favorable because the cost is less than budgeted price Raw Material Cost per Unit Packaging Cost per Unit Raw Material Price Variance Actual Price Budget Price Actual Quantity Packaging Price Variance (Actual Price Budget Price) Actual Quantity Raw Material Volume Variance Actual Quantity - Budget Quantity Sundard Price Packaging Volume Variance (Actual Quantity - Budget Quantity Sundard Price $ 24.750.00 Packaging Price Variance is unfavorable because the cost is greater than budgeted price Vroom-Vroom DEC Fleable Budget Description Actual Resuts Actual Quantity Bdgt. Rates Fleable Bagt. Varia Production 275.000 275.000 $ 16,375,000.00 275,000 $ 59.00 16,225,000.00 $ 150,000.00 Variable Expenses Direct Material Raw Materials $ 6,450,000.00 275,000 $ 24.00 $ 6.500,000.00 $ (150,000.00 F Packaging Materials $ 4,150,000.00 275,000 $ 15.00$ 4,125,000.00 $ 25,000.00 U Variable Manufacturing Overheads Electricity $ 1.095,000.00 275,000 $ 4.00 $ 1,100,000.00 $ 5.000,00) F Waste and Other Costs $ 1,775,000.00 275,000 $ 6.00 1.650,000.00 $ 125,000.00 U Total Variable Expenses $ 13,470,000.00 $ 49,00 $ 13.475.000.00 50001 Contribution Martin $ 2.905.000.00 S 10.00 $ 2.750,000.00 $ 155,000.00 U Feed Overheads Wages S 375,000.00 $ 350,000.00 $ 25,000.00 U Fringe Benefits $ 175.000.00 $ 175,000.00 $ Rent $ 750,000.00 $ 750,000.00 $ . Insurance $ 50.000.00 $ 50,000.00 $ Depreciation 250,000.00 $ 250,000.00 $ . Total Fed Overheads $ 1,600,000.00 $ 1,575,000.00 $ 25,000.00 U Net Profit LOSS) $ 1,305,000.00 $ 1,175,000.00 $ 130,000.00 U 1023.45-24.00) = -0.55 10.55) 275,000--151,250 las 09-15.00)09 10.09) 275.000- 24,750 10275,000-275,000) 0 lo) 24.0 10275,000 275,000) 0 lo) 150 $ w Material Volume Variance is indifferent because both budgeted and actual price is the sam S . Packaging Volume Variance is indifferent because both budgeted and actual price is the same Particulars January $ $ 150,000 23.00 15.00 Budget Break Down Requested 150.000 $ 24.00 $ 15.00 102.00-24.00) 100 (-1.00) 150,000 2015.00 - 15.00) = 0.00 (0.00) 150,000 (150.000-150,000) (0) 240 150.000-150.000) = 0 S $ (150,000.00 150.000.000 Raw Material Variance is favorable because the cost is less than budgeted price Raw Material Cost per Unit Packaging Cost per Unit Raw Material Price Variance (Actual Price Budget Price) Actual Quantity Packaging Price Variance Actual Price Budget Price) Actual Quantity Raw Material Volume Variance Actual Quantity - Standard Quantity) Standard P Packaging Volume Variance - Packaging Price Variance is indifferent because both budgeted and actual price is the same. . Vroom-Vroom JAN Flexible Budget Description Actual Results Actual Quantity Bdgt. Rates Flexible Bagt. Variance F/U Production 150.000 150.000 Revenue S 8,845,000.00 150,000 $ 59.00 8,850,000.00 $ 15,000.00 U Variable Expenses Direct Material Raw Materials $ 3,450,000.00 150,000 $ 24.00 $ 3,600,000.00 $ (150,000.00 F Packaging Materials $ 2.250,000.00 150.0ool S 15.00 $ 2.250,000.00 $ Variable Manufacturing Overheads Electricity $ $75,000.00 150.000 S 4.00 $ 600,000.00 1 S 125.000 oil Waste and Other Costs $ 1,025,000.00 150,000 $ 6,00 $ 900.000.00 $ 125.000,00 lu Total Variable Expenses S 7,300,000.00 $ 49.00 $ 7,350,000.00 $ 150,000.00) F Contribution Margin $ 1,545,000.00 S 10.00 $ 1.500.000,00 $ 45,000.00 U Flued Overheads Wages $ 345,000.00 $ 350,000.00 $ 5.000,00) F Fringe Benefits $ 175000.00 S 125.000,00 S Rent $ 750,000.00 $ 750,000.00 $ - - Insurance $ 50.000.00 S 50.000.00 Depreciation s 250.000.00 $ 250,000.00 $ - 1 Total Fixed Overheads S 1.570,000.00 S 1.575,000.00 $ 15,000.00) F Net Profit LOSS) $ 25,000.00 $ 75,000.00 $ 50,000.00 LU w Material Volume Variance is indifferent because both budgeted and actual price is the sam Packaging Volume Variance is indifferent because both budgeted and actual price is the same Particulars December Budget Break Down Requested 275,000 $ 24.00 23.45 $ (151,250.00 Raw Material Variance is favorable because the cost is less than budgeted price Raw Material Cost per Unit Packaging Cost per Unit Raw Material Price Variance Actual Price Budget Price Actual Quantity Packaging Price Variance (Actual Price Budget Price) Actual Quantity Raw Material Volume Variance Actual Quantity - Budget Quantity Sundard Price Packaging Volume Variance (Actual Quantity - Budget Quantity Sundard Price $ 24.750.00 Packaging Price Variance is unfavorable because the cost is greater than budgeted price Vroom-Vroom DEC Fleable Budget Description Actual Resuts Actual Quantity Bdgt. Rates Fleable Bagt. Varia Production 275.000 275.000 $ 16,375,000.00 275,000 $ 59.00 16,225,000.00 $ 150,000.00 Variable Expenses Direct Material Raw Materials $ 6,450,000.00 275,000 $ 24.00 $ 6.500,000.00 $ (150,000.00 F Packaging Materials $ 4,150,000.00 275,000 $ 15.00$ 4,125,000.00 $ 25,000.00 U Variable Manufacturing Overheads Electricity $ 1.095,000.00 275,000 $ 4.00 $ 1,100,000.00 $ 5.000,00) F Waste and Other Costs $ 1,775,000.00 275,000 $ 6.00 1.650,000.00 $ 125,000.00 U Total Variable Expenses $ 13,470,000.00 $ 49,00 $ 13.475.000.00 50001 Contribution Martin $ 2.905.000.00 S 10.00 $ 2.750,000.00 $ 155,000.00 U Feed Overheads Wages S 375,000.00 $ 350,000.00 $ 25,000.00 U Fringe Benefits $ 175.000.00 $ 175,000.00 $ Rent $ 750,000.00 $ 750,000.00 $ . Insurance $ 50.000.00 $ 50,000.00 $ Depreciation 250,000.00 $ 250,000.00 $ . Total Fed Overheads $ 1,600,000.00 $ 1,575,000.00 $ 25,000.00 U Net Profit LOSS) $ 1,305,000.00 $ 1,175,000.00 $ 130,000.00 U 1023.45-24.00) = -0.55 10.55) 275,000--151,250 las 09-15.00)09 10.09) 275.000- 24,750 10275,000-275,000) 0 lo) 24.0 10275,000 275,000) 0 lo) 150 $ w Material Volume Variance is indifferent because both budgeted and actual price is the sam S . Packaging Volume Variance is indifferent because both budgeted and actual price is the same Particulars January $ $ 150,000 23.00 15.00 Budget Break Down Requested 150.000 $ 24.00 $ 15.00 102.00-24.00) 100 (-1.00) 150,000 2015.00 - 15.00) = 0.00 (0.00) 150,000 (150.000-150,000) (0) 240 150.000-150.000) = 0 S $ (150,000.00 150.000.000 Raw Material Variance is favorable because the cost is less than budgeted price Raw Material Cost per Unit Packaging Cost per Unit Raw Material Price Variance (Actual Price Budget Price) Actual Quantity Packaging Price Variance Actual Price Budget Price) Actual Quantity Raw Material Volume Variance Actual Quantity - Standard Quantity) Standard P Packaging Volume Variance - Packaging Price Variance is indifferent because both budgeted and actual price is the same. . Vroom-Vroom JAN Flexible Budget Description Actual Results Actual Quantity Bdgt. Rates Flexible Bagt. Variance F/U Production 150.000 150.000 Revenue S 8,845,000.00 150,000 $ 59.00 8,850,000.00 $ 15,000.00 U Variable Expenses Direct Material Raw Materials $ 3,450,000.00 150,000 $ 24.00 $ 3,600,000.00 $ (150,000.00 F Packaging Materials $ 2.250,000.00 150.0ool S 15.00 $ 2.250,000.00 $ Variable Manufacturing Overheads Electricity $ $75,000.00 150.000 S 4.00 $ 600,000.00 1 S 125.000 oil Waste and Other Costs $ 1,025,000.00 150,000 $ 6,00 $ 900.000.00 $ 125.000,00 lu Total Variable Expenses S 7,300,000.00 $ 49.00 $ 7,350,000.00 $ 150,000.00) F Contribution Margin $ 1,545,000.00 S 10.00 $ 1.500.000,00 $ 45,000.00 U Flued Overheads Wages $ 345,000.00 $ 350,000.00 $ 5.000,00) F Fringe Benefits $ 175000.00 S 125.000,00 S Rent $ 750,000.00 $ 750,000.00 $ - - Insurance $ 50.000.00 S 50.000.00 Depreciation s 250.000.00 $ 250,000.00 $ - 1 Total Fixed Overheads S 1.570,000.00 S 1.575,000.00 $ 15,000.00) F Net Profit LOSS) $ 25,000.00 $ 75,000.00 $ 50,000.00 LU w Material Volume Variance is indifferent because both budgeted and actual price is the sam Packaging Volume Variance is indifferent because both budgeted and actual price is the same
