Question

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.

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.

Monthly Budget Data:

Selling price per unit: $5.00 per box

Raw Materials 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.20 per box

Waste and other costs $500,000 per month

Rent Costs $60,000 per month

Insurance Costs $60,000 per month

Depreciation Costs $240,000 per month

Answer 1

a)

Here, We need to make 2019 budget. It means an yearly budget

Monthly production=3000000

Yearly production=3000000*12=36000000

$

Sales revenue (A)

(36000000*5)

180000000

Less: Expenses

Raw materials

(36000000*1.5)

54000000

Packaging cost

(36000000*0.8)

28800000

Salary and wages cost

(300000*12)

3600000

OT

(Since production <3000000 boxes)

0

Fringe benefits

50%*(3600000+0)

1800000

Electricity

(36000000*0.2)

7200000

Waste and other costs

(500000*12)

6000000

Rent costs

(60000*12)

720000

Insurance costs

(60000*12)

720000

Depreciation costs

(240000*12)

2880000

Total expenses (B)

105720000

Net operating income

(A)-(B)

74280000

b)

Monthly production=2000000

Yearly production=2000000*12=24000000

$

Sales revenue (A)

(24000000*5)

120000000

Less: Expenses

Raw materials

(24000000*1.5)

36000000

Packaging cost

(24000000*0.8)

19200000

Salary and wages cost

(300000*12)

3600000

OT

(Since production <3000000 boxes)

0

Fringe benefits

50%*(3600000+0)

1800000

Electricity

(24000000*0.2)

4800000

Waste and other costs

(500000*12)

6000000

Rent costs

(60000*12)

720000

Insurance costs

(60000*12)

720000

Depreciation costs

(240000*12)

2880000

Total expenses (B)

75720000

Net operating income

(A)-(B)

44280000

c)

Monthly production=4000000

Yearly production=4000000*12=48000000

$

Sales revenue (A)

(48000000*5)

240000000

Less: Expenses

Raw materials

(48000000*1.5)

72000000

Packaging cost

(48000000*0.8)

38400000

Salary and wages cost

(300000*12)

3600000

OT

(48000000-36000000)*0.70

8400000

Fringe benefits

50%*(3600000+8400000)

6000000

Electricity

(48000000*0.2)

9600000

Waste and other costs

(500000*12)

6000000

Rent costs

(60000*12)

720000

Insurance costs

(60000*12)

720000

Depreciation costs

(240000*12)

2880000

Total expenses (B)

148320000

Net operating income

(A)-(B)

91680000

d)

Flexible budget cost formulas

Number of units produced=x

Formula

Comments

Sales revenue

5x

Raw material cost

1.5x

Packaging cost

0.8x

Salary and wages cost (S)

3600000

OT

(O)

(Note)

(x-36000000)*0.70

If (x-36000000) is negative take it as 0.

Fringe benefits

50%*(S+O)

Electricity

0.2x

Waste and other costs

6000000

Rent costs

720000

Insurance costs

720000

Depreciation costs

2880000

Note: Overtime is applied when More than 3000000 units produced per month.Hence,OT is applicable when yearly production exceeds (3000000*12)36000000 boxes