Advantage Manufacturing is preparing a master budget for the quarter ending 30 September 2021, and has compiled the data shown below. The firm sells a single product at a price of $60 per unit. The sales forecast (in units) prepared by the marketing department for the quarter ending 30 June 2021 and the first 7 months of the next financial year is as follows. April May June July August September October November December January Number of units 28 800 28 800 30 000 31 200 33600 33600 37200 38 400 43 200 57600 The ending finished goods inventory should equal 15% of the sales requirements for the next 3 months, and the raw materials ending inventory should equal 20% of the next month's production requirements. Cash sales account for 50% of sales. Credit sales are collected 40% in the month of sale and 60% in the following month. 80% of the materials purchases are paid in the month of purchase and 20% are paid in the following month. The production requirements are below. Direct materials 1.1 kg Direct labour 0.5 hour Per unit The direct materials are purchased for $8.00 a kilogram. The direct labour wage rate is $24 an hour. The budgeted factory overhead per month is as follows: fixed overhead cost $ Rates 36 400 Insurance 45 000 Maintenance 22 600 Depreciation 28 000 Variable overhead cost: Indirect labour $1.50, per month's direct labour hour Indirect material $2.30, per month's direct material kg The 1 July 2021 cash balance is expected to be $40 320. Selling expenses are budgeted as follows: Advertising expense: $900 per month, paid in the month in which incurred. Depreciation expense: $18 500 per year Sales salaries: $5 000 per month and paid as incurred. Administrative expenses are budged as follows: Rent on administration premises is $3 000 per month, paid by the last day of the month. Variable administrative expenses per month are 7% of sales. Required (All calculations to be rounded to the nearest dollar value.) (a) Calculate the number of units to be produced in the first quarter of the financial year starting 1 July 2021. (b) Prepare a cash budget for the first quarter of the financial year starting 1 July 2021 including any necessary schedules. (e) Prepare a budgeted income statement for the first quarter of the financial year starting 1 July 2021. (d) Prepare a partial budgeted balance sheet for the first quarter of the financial year starting 1 July 2021, based on the available information. Instructions: You are required at least, to have a sheet in excel for The above requirements (can be in one sheet or more) Workings and calculations (including any relevant statement/schedule such as cash collection, direct material, and direct labour)
