Truecor Electronics produces two types of coils used in generators. In November 2020, Jessie Higgins, the financial controller, compiled the following data:

Sales forecast for 2021 (all units to be shipped in 2021):

Product Units Price

Light coil .......................................... 60,000 $120

Heavy coil ....................................... 40,000 170

Raw material prices and inventory levels:

Raw material Expected inventories, Desired inventories, Anticipated

January 1, 2021 December 31, 2021 purchase price

Sheet metal ................................... 32,000 kg 36,000 kg $8

Copper wire ................................... 29,000 kg 32,000 kg 5

Platforms ........................................ 6,000 units 7,000 units 3

Use of raw materials:

Amount used per unit

Raw material Light coil Heavy coil

Sheet metal ................................... 4 kg 5 kg

Copper wire ................................... 2 kg 3 kg

Platforms ........................................ NA 1 unit

Direct labour requirements and rates:

Hours Rate

Product per unit per hour

Light coil .......................................... 2 $15

Heavy coil ....................................... 3 20

Finished goods inventory (in units):

Expected, Desired,

Product January 1, 2021 December 31, 2021

Light coil .......................................... 20,000 25,000

Heavy coil ....................................... 8,000 9,000

Production overhead:

Overhead cost item Activity-based budget rate

Purchasing and material handling ............... $0.25 per kg of sheet metal and copper wire

purchased

Depreciation, utilities and inspection .......... $4.00 per coil produced (either type)

Shipping ............................................................... $1.00 per coil shipped (either type)

General production overhead ......................... $3.00 per direct labour hour

REQUIRED:

Using EXCEL, prepare the following budgets for 2021:

a) Sales budget (in dollars)

b) Production budget (in units)

c) Raw-material purchases budget (in quantities and dollars)

d) Direct labour budget (in dollars)

e) Production overhead budget (in dollars)