Maple Magic Corporation is preparing the master budget for the quarter ending March 31. It sells a single product for $45 a unit. Sales are 20% cash and 80% credit. The credit sales are collected 65% the following month and the remaining 35% is collected in the next month. No credit sales and no purchases of merchandise occurred in December of the prior year. The December 31 inventory of finished goods is 420 units and projected sales are 750, 725, 880, and 620 units for the first four months of the year. The desired ending inventory for each month is 20% of the next month's sales. The inventory of Finished Goods expected to be on hand on April 30 is 380 units. Each Finished Unit requires 3 kilograms of materials at a cost of $2.50 per kilo and it takes 1 and a quarter hours to complete one unit. The company requires 20% of the next month production materials needs to be available before the start of the month. Labor is paid at the rate of $12.80 per hour and is paid when incurred. Production overhead is incurred based on labor hours per unit of production and costs $1.20 per hour. Twenty-five percent of the purchases are paid in the month of purchase and 75% are paid in the following month.
Operating expenses are paid in the month incurred and consist of sales commissions (14.5% of sales), freight cost (3.5% of sales), office salaries of $6,500 a month, office supplies $200 per month, rent of $2,200 per month, and amortization of $750 per month. The cash balance must not be negative. The beginning cash balance is $23,000. Loans are obtained at the beginning of the month in which a cash shortage occurs and are issued in multiples of $1,000. Interest is 1% per month based on the beginning-of-month loan balance and must be paid at the end of each month when the loan is repaid. Maple Magic sold some securities for $25,000 cash in February. During January it became clear that a new vehicle was needed so one was bought for $18,900 cash.
Required: Prepare a master budget for the first quarter on the excel template provided, that includes:
1) Sales budget,
2) Cash receipts schedule
3) Production budget
4) Materials budget
5) Labourbudger
6) Overhead budget
7) Schedule of cash disbursements for materials
8) Selling and administrative expense budget
9) Cash budget for each month.
10) Also prepare a budgeted income statement for the quarter.