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 quarters 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 companys 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)2021Q11,535Q22,250Q32,550Q42,1302022Q11,611Q22,362Q32,678Q42,2372023Q11,725Q22,543EW keeps a minimum amount of the following:CashA minimum of $6,000,000Finished unitsEqual to 20% of next quarters sales requirements

AC436Master Budget Assignment10% of gradeInventory 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:ElectricWorksCo.BalanceSheetAsatDecember31,20202020Cash8,000,000A/R4,730,000RMInventory6,000,000FGInventory3,545,258Land10,000,000Equipment50,000,000AccumulatedDepreciation(22,000,000)TotalAssets60,275,258LineofCredit-A/P640,000WagesPayable1,200,000BondPayable20,000,000CommonShares35,000,000RetainedEarnings3,435,258TotalLaibilitiesandEquity60,275,258

AC436Master Budget Assignment10% of grade$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. Dont 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. Dont create your own or copy someone elses to start yours.Feel free to work together, but keep your work separate and submit only your own work. Late files are not accepted for any reason.It is very important that your budget is linked with formulas to the data input section. If you just type in numbers into the cells in the body of the budget, you will receive a grade of zero