the task must fill in this template

and please add new column with the excel formula so i can understand where the calculation come!

Part 2: Budgeting (24 marks) The Penticton Region was so happy with the expertise that you provided to them last year that they have Below is the information that they have provided respecting their sales forecast for the upcoming fiscal year: Budgeted sales (units) Budgeted sales revenue Cash beginning Accounts Receivable Accounts Payable Beginning finished goods inventory (units) Desired ending finished goods inventory (units) Desired ending finished goods inventory Beginning raw material inventory (kg) Desired ending raw material inventory (kg) Desired ending raw material inventory Variable MOH rate per direct labour-hour Fixed manufacturing overhead (FMOH) 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 7,400 8,000 5,900 7,000 $444,000 $480,000 $354,000 $420,000 $18,800 $31,300 $15,100 $1,570 1,740 19% of next quarter's budgeted sales 3,450 3,180 19% of next quarter's budgeted sales $2.00 $163,900 $163,900 $163,900 $163,900 You have gathered the following information from management respecting operations. a) Cash and borrowings: Required minimum cash balance each period 11,000 The region has an open line of credit to support operational needs. All borrowing is done at the beginning of a month, and all repayments are made at the end of a month. Borrowings and repayments must be in multiples of $1,000. Annual interest rate is: 11% Interest is paid only at the time of principal repayment b) The Accounts receivable balance at the beginning of the 1st quarter is from sales made the previous month. The entire amount will be received in the 1st quarter. Typically, sales are 40% in cash and 60% on credit. Seventy percent (70%) of credit sales are collected in the quarter that the sale occurs, 30% is collected in the following quarter. c) d) e) f) ) GO h) i) Each unit requires two (2) kilograms of raw material that costs $8 per kilogram. Management plans to pay for 65% of raw material purchases in the quarter acquired and 35% in the following quarter. The accounts payable balance is related to raw material purchases from the previous quarter and will be paid in full in the first quarter. Each unit produced requires 1.2 direct labour-hours. The hourly rate budgeted is $22 per hour. Dynamadics expects to purchase and pay for new equipment in the 1st quarter at a price of $25,000. Management also plans to sell equipment that no longer supports operations in the 2nd quarter for a price of $100,000. There are no depreciation considerations for this equipment. Annual depreciation included in fixed manufacturing overhead is $216,000 Monthly operating expenses incurred include: Salaries (see note 1 below) $12,000 Rent $5,000 Amortization or pre-paid insurance $1,200 Depreciation of office equipment $1,500 (note 1) Due to timing of payroll for the first quarter, 2/3 of the 1st quarter salary costs will be paid in the first quarter, the remaining 1/3 will be paid the next quarter. There is no payable for salaries outstanding at the beginning of the first quarter and there will not be any salary payables at the end of the 2nd quarter. Part 2, Question 1 (24 marks) Prepare the following budgets for the upcoming fiscal year. a) Production budget b) Direct material budget c) Schedule of cash disbursements for direct materials d) Labour budget e) Manufacturing overhead budget f) Cash budget for the 1st and 2nd quarter (round results to the nearest dollar) Round all $ results to nearest dollar and all units to whole unit A template has been created in Excel for your use if desired. However some budget information is missing. Be sure to input the missing information in the template. If you feel there are not enough rows for your entries, adjust the template as necessary. H H A B D E F 1 Master Budget Name: 2 Student Number: 3 1. Production budget 1st quarter 2nd quarte 3rd quarte 4th quarte Annual 4 Budget sales (units) 5 6 7 8 Required production units 9 10 2. Direct Materials Budget 1st quarter 2nd quarte 3rd quarte 4th quarte Annual 11 Production needs in kilograms 12 13 14 15 16 Cost of raw materials to be purchased 17 18 3. Cash disbursements for materi 1st quarter 2nd quarte 3rd quarte 4th quarte Annual 19 Accounts payable, beginning balance 20 1st quarter purchase 21 22 23 24 Total cash disbursement for materials 25 26 4. Direct labour budget 27 28 29 Total Direct Labour Cost 30 31 5. Manufacturing overhead budget 32 33 34 35 36 Cash Disbursement for MOH 37 38 6. Cash Budget 39 1st Quarte 2nd Quarter 40 Cash balance beginning 41 Add: Cash from AR collection 42 Cash Collections from sales 43 44 45 46 Total Cash available 47 Deduct: Disbursements 48 49 50 51 52 53 54 55 56 Total disbursements 57 Excess (deficiency) of cash 58 Financing: 59 Borrowing 60 Repayments 61 Interest 62 Total Financing 63 Cash balance, ending 64 Part 2 - Template Accessibility: Good to go Ready