Question

Create an Excel file to show the budgets and calculations for the questions below. Assume no beginning or ending inventory. Ignore taxes. Analysis or explanations can be included in the Excel cells or in a textbox. Use contribution margin income statement formatting.

HammerTime is preparing their 2019 budget. They want to look at static budgets and flexible budgets to determine which is best for them. They estimate sales/production will be between 2,000,000 and 4,000,000 boxes of nails per month. They want to be able to understand their budget variances as well.

Data for Question 2:                                                        January Actual

Production                                                                          3,250,500 boxes

Sales                                                                                      $16,250,000

Ingredient Costs                                                               $4,795,000

Packaging Costs                                                                $2,600,000

Salary and Wages Costs                                                      $280,000

Overtime (OT)                                                                        $195,500

Fringe Benefits                                                                      $237,750

Electricity                                                                                  $650,000

Waste and Other Costs                                                       $365,000

Rent Costs                                                                                $500,000

Insurance Costs                                                                       $65,000

Depreciation Costs                                                               $240,000

Question 1: Prepare some budgets in Excel for HammerTime.

a) Show the static budget based on 3,000,000 units (boxes) produced.

b) Show the flexible budget based on 2,000,000 units (boxes) produced.

c) Show the flexible budget based on 4,000,000 units (boxes) produced.

d) Show the flexible budget cost formula(s) for HammerTime.

e) Explain the difference between static and flexible budgets and when each should be used.

Monthly Budget Data:

Selling price per unit:                                                                      $5.00 per box

Raw Material Costs                                                                         $1.50 per box

Packaging Costs                                                                                $0.80 per box

Salary and Wages Costs                                                         $300,000 per month

OT for production over 3,000,000 units                                  $0.70 per box

Fringe Benefits                                                                                 50% of Wages and OT

Electricity                                                                                             $0.10 per box

Rent Costs                                                                                    $500,000 per month

Insurance Costs                                                                                $60,000 per month

Depreciation Costs                                                                          $240,000 per month

Question 2: The month of January 2019 is complete, and HammerTime wants to compare their budget to their actual results. Actual results are shown in the table above.

a) Compare January’s actual results to the static budget you created in Question 1.

b) Analyze the static budget variances. Be sure to break out price and volume variances and whether they are favorable or unfavorable for each line item. Provide possible explanations.

c) Create the flexible budget based on actual units produced for January.

d) Compare actual results to budgeted results for the flexible budget.

e) Analyze the flexible budget variances. Be sure to include the variance amount and whether the variance is favorable or unfavorable for each line item. Provide possible explanations.

f) HammerTime wants to determine whether they should use a flexible budget or a static budget going forward. Write a memo to their CFO explaining some pros and cons of each option. Provide a recommendation including the reason(s) you recommend that approach.

Answer 1

As per our policy, we can not able to post solution more than four sub parts of question.

Answer	A	B	C
Budget	Static	Flexible	Flexible
Unit Produced	3,000,000	2,000,000	4,000,000
Sales (5*Unit Produced)	15,000,000	10,000,000	20,000,000
Less: variable cost
Material cost (1.5*Unit Produced)	4,500,000	3,000,000	6,000,000
Packaging Costs (0.80*Unit Produced)	2,400,000	1,600,000	3,200,000
Overtime (OT) ((4000000-3000000)*0.70)			700,000.00
Fringe Benefits on overtime (OT) (((4000000-3000000)*0.70)*50%)			350,000.00
Electricity (0.10*Unit Produced)	300,000	200,000	400,000
Total variable cost	7,200,000	4,800,000	10,650,000
Contribution margin	7,800,000	5,200,000	9,350,000
Less: fixed cost
Salary and Wages Costs	300,000	300,000	300,000
Fringe Benefits on Salary and Wages Costs (300000*50%)	150,000	150,000	150,000
Rent Costs	500,000	500,000	500,000
Insurance Costs	60,000	60,000	60,000
Depreciation Costs	240,000	240,000	240,000
Total Fixed cost	1,250,000	1,250,000	1,250,000
Operating Income	6,550,000	3,950,000	8,100,000
Total cost	8,450,000	6,050,000	11,900,000

Answer D
Cost Formula
Raw material	1.50
Packing	0.80
Electricity	0.10
Variable cost per unit	2.40
Fixed cost per month	1,250,000
When Unit produced not exceed 3,000,000 units then cost formula	1,250,000 + (2.40*Unit Produced)
Overtime per unit (include fringe benefits) (0.70+(0.70*50%))	1.05
When unit produced more than 3,000,000 units	1,250,000 + (2.40*Unit Produced) + (1.05*(unit produced - 3000000)
When Unit produced not exceed 3,000,000 units then cost formula	1,250,000 + (2.40*Unit Produced)
When unit produced more than 3,000,000 units	1,250,000 + (2.40*Unit Produced) + (1.05*(unit produced - 3000000)