Electro Works Co. ("EW") is preparing its quarterly budget for 2021 and 2022. EW manufactures and sells electric cars.

The following information is available:

Each unit requires 150 hours of direct labour and 2,000 kg of inventory to build.

Currently, they have 3,000,000 kg of inventory in raw materials inventory. To prepare for supply disruptions, the company likes to keep monthly ending inventory of high enough to meet 90% of the next quarter's production requirements. In practice, their attempts at this might not always work out.

Direct labour costs $23 per hour.

Accounts payable are paid 90% in the quarter the good or service is purchased and 10% in the following quarter.

The company's year-end is December 31.

Each KG of raw materials inventory costs $2 in 2021 and 2022. This was the same price in 2020.

In December 2020, 3.2M kg of raw material was purchased and of Direct Labour cost $6M.

Sales are estimated as follows:

Sales ( units)

2021

Q1 1535

Q2 2250

Q3 2550

Q4 2130

2022

Q1 1611

Q2 2362

Q3 2678

Q4 2237

2023

Q1 1725

Q2 2543

EW keeps a minimum amount of the following:

Cash

A minimum of $6,000,000

Finished units

Equal to 20% of next quarter's sales requirements

Inventory of finished units from December 2020 is equal to the targeted inventory policy.

All sales are on account.

Selling and Administration expenses are $600 per unit and $1,000,000 per quarter. There is no depreciation included in this figure.

Dividends of $500,000 will be paid in December of 2021 and $550,000 in December 2022.

Customer collections are received 85% in the quarter of sale, 11% in the next quarter, and 2% in the second quarter following the sale. No further collections are received.

The company uses the absorption method of costing finished goods inventory. EW allocates manufacturing overhead on the basis of direct labour hours.

Sales ($) in Q3 and Q4 2020 were $35M and $31M, respectively.

Due to payroll processing times, 80% of the wages earned by workers is paid in the quarter of production, and the balance is paid in the following quarter.

The selling price per unit for 2021 is $15,500 to the dealers who mark it up to $25,000. The price will be increased by 2% (rounded to the next highest $100) for 2022.

Overhead is $1,200,000 per month plus $2,400 per unit. Overhead costs are all paid in the quarter they occur, and there is $250,000 per month in depreciation included.

The balance sheet as at December 31, 2020 is below:

Electric Work Co.

Balance Sheet

As at December 31,2020

Cash 8000000

A/R 4,730,000

RM Inventory 6000000

FG Inventory 3545258

Land 10,000,000

Equipment 50,000,000

Accumulated Depreciation(22,000,000)

Line of Credit

A/P 640,000

Wages Payable 1200000

Bond Payable 20,000,000

Common Share 35,000,000

Retained Earnings 3,435,258

Total liabilities and Equity 60,275,258

$2,000,000 of the equipment will be sold in August 2021. For simplicity, assume that the equipment sold does not depreciate and was sold at its original cost. Therefore, there will be no gain or loss on disposal.

$10,500,000 of equipment will be purchased in November 2021. Assume the current forecast of depreciation expense was close enough and that it remains unchanged.

Repayment of the $20M bond will be completed in January 2022.

The company has a $100M unused line of credit at an interest rate of 4% per annum. Borrowing happens on the first day of the quarter and repayments are made at the end of each quarter. Interest is calculated and deducted from the bank account at the end of each quarter based on the loan balance outstanding.

The amount of interest expense paid on the line of credit will drop your balance below the $6M minimum. Don't worry about that. The resulting balance is close enough to the minimum for our purposes.

Required:

Complete the master budget including cash projections, balance sheet and income statement. A template has been provided for you to work with. All formulas should be linked to the data input area or other cells. All input assumption amounts should be shown at the top of the spreadsheet in the designated section. Direct input of numbers in the formulas of the document should be avoided. You should link your formulas to the data input section.

Note: You must use the Excel file provided.