Assignment
Case Study:
Calculations and backup should be completed and submitted in one Excel file. Word documents or other file types will not be accepted. Use proper Contribution Margin Income Statement formatting (see below). Each question refers to the same initial data. Treat each question separately. Ignore income taxes. Assume no beginning or ending inventories. Calculations and backup should be completed and submitted in Excel. Leave any formulas used in the Excel cells (do not hard code or type in results); this allows for partial credit if possible. Analysis can either be typed into cells in Excel (formatted to be easily legible) or typed into a text box in Excel. Students are expected to work independently.
Vroom-Vroom manufactures ride-on cars for toddlers and young children. They have a fiscal year of August through July. When they were preparing their budget, they couldn't decide if a static or flexible budget would be best for their company - so they did both. It is now February, and their accounting department is catching up on analyzing variances for both December and January. Vroom-Vroom would like to use this opportunity to determine whether they would be better off with a static or flexible budget going forward. They want to choose which budget and related variance analysis provides them the best information for decision-making.
Here is the data that Vroom-Vroom used for their budgets:
Monthly Budget Data:
Selling price per unit: $59.00 per each
Raw Material Costs $24.00 per each
Packaging Costs $15.00 per each
Electricity $4.00 per each
Waste and Other Costs $6.00 per each
Salary and Wages Costs $350,000 per month
Fringe Benefits 50% of Salaries
Rent Costs $750,000 per month
Insurance Costs $50,000 per month
Depreciation Costs $250,000 per month
Vroom-Vroom estimated sales/production will be between 100,000 and 300,000 cars per month. Their static budget is based on 200,000 cars sold per month. Assume that all units produced in a month are also sold in that month. Vroom-Vroom's unit of production/sale is a car (unit/each).
Here are the Actual Results in December and January:
|
Actual Data:
|
December
|
January
|
|
Production (Units)
|
275,000
|
150,000
|
|
Revenue
|
$ 16,375,000
|
$ 8,845,000
|
|
Raw Materials
|
$ 6,450,000
|
$ 3,450,000
|
|
Packaging Materials
|
$ 4,150,000
|
$ 2,250,000
|
|
Electricity
|
$ 1,095,000
|
$ 575,000
|
|
Waste and Other Costs
|
$ 1,775,000
|
$ 1,025,000
|
|
Wages
|
$ 375,000
|
$ 345,000
|
|
Fringe Benefits
|
$ 175,000
|
$ 175,000
|
|
Rent
|
$ 750,000
|
$ 750,000
|
|
Insurance
|
$ 50,000
|
$ 60,000
|
|
Depreciation
|
$ 250,000
|
$ 250,000
|
Question I: Prepare a static budgetin Excel for Vroom-Vroom based on 200,000 units produced.
i. Show the static budget for December in Contribution Margin Income Statement format.
ii. Compare December's static budget to December's actual results. Specify which line items are favorable or unfavorable and how much.
iii. For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for December. Provide potential explanations for each one.
iv. Show the static budget for January in Contribution Margin Income Statement format.
v. Compare January's static budget to January's actual results. Specify which line items are favorable or unfavorable and how much.
vi. For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for January. Provide potential explanations for each one.
Question II: Prepare a flexible budgetin Excel for Vroom-Vroom.
i. Show the flexible budget for December in Contribution Margin Income Statement format.
ii. Compare December's flexible budget to December's actual results. Specify which line items are favorable or unfavorable and how much.
iii. For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for December. Provide potential explanations
iv. Show the flexible budget for January in Contribution Margin Income Statement format.
v. Compare January's flexible budget to January's actual results. Specify which line items are favorable or unfavorable and how much.
vi. For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for January. Provide potential explanations for each one.
Question III: Analyze the differences between static and flexible budgets.
i. What is the difference between the static and flexible budgets?
ii. What are the pros and cons for each - static and flexible budgets?
iii. Write a letter to Vroom-Vroom's CFO. Explain the results in December and January. Provide your recommendation for either static or flexible budgets. Provide explanations and backup for your recommendation.
Format your assignment according to the give formatting requirements:
1. The answer must be double spaced, typed, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the course title, the student's name, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The references and Citations should follow APA format. The reference page is not included in the required page length.