You were recently hired as an assistant controller at Sunshine Surfboards (ss). SS manufactures and sells one product, surfboards. You have been charged with preparing a cash budget for the second quarter (April to June) so that management can be aware of any financing requirements. You have collected the following information to assist you in preparation of the quarterly budget: Recent and forecasted sales in units are as follows: 80 140 January (actual) February (actual) March (actual) April May 170 250 290 June 350 350 July August September 300 290 Surfboards are sold to retailers for $410 each. The company has a policy of having an ending inventory each month equal to 150% of the next month's sales. The inventory balance at March 31" was 375 surfboards. Each Surfboard requires 2 meters of polystyrene, which the company purchases for $40 per meter. To protect against disruptions in production, management likes to keep enough polystyrene on hand at all times equal to 50% of the next month's production needs. This requirement had been met on March 31* in that the company had 310 meters of polystyrene in the warehouse. Purchases of polystyrene are paid for 50% at the time of purchase with the other 50% being paid the next month. All sales are on credit with 30% being collected in the month of sale, 50% being collected in the month following the month of sale and the remaining 20% being collected two months after the month of sale. SS has a tight credit policy and, as a result, does not have any bad debts. Surfboards are hand crafted taking, on average, 4 hours to assemble. Employees are paid $20 per hour and never work overtime. Manufacturing overhead includes all the costs of production other than the polystyrene and direct labour. The variable component is $15 per surfboard manufactured and the fixed component is $22,000 per month. Fixed manufacturing overhead includes $10,000 of depreciation. Manufacturing overhead is applied to surfboards on the basis of surfboards manufactured. SS's monthly operating expenses are given below: Variable: Sales commissions $10 per surfboard sold Fixed: Wages and salaries Utilities $15,000 1,700 1,500 2,100 Insurance Depreciation Miscellaneous 2,200 All operating expenses are paid in the month for cash, with the exception of depreciation and insurance. Insurance is paid once a year in January ($18,000) then expensed over the entire year. The company plans to purchase some new manufacturing equipment in May for $12,000. SS declares a dividend of $10,000 on the last day of every quarter (i.e. March 31st, June 30th, September 30+ and December 31-) and pays it one month later (i.e. the March dividend is paid April 30th, the June dividend is paid July 31s, etc.) The balance sheet at March 31st is given below: Assets Cash $ 25,000 60,270 Accounts receivable Inventory, polystyrene (raw materials) Inventory, surfboards (finished goods) Prepaid insurance 12,400 89,437 13,500 Fixed assets 110,000 (62.000) Accumulated depreciation Total Assets 48,000 248,607 Liabilities and Shareholder's Equity Accounts payable, purchases Dividends payable Capital stock Retained earnings Total liabilities and shareholder's equity 12,000 10,000 15,000 211,607 248,607 Management believes in keeping a minimum cash balance of $20,000 at the end of each month. The company can borrow from the bank at 12% annual interest. Borrowings and repayments must also be in increments of $1,000. Interest is paid each month based on the previous months ending short term loan balance. Round all interest payments to the nearest whole dollar. The company wishes to use any excess cash to pay off the loan as rapidly as possible. Required: 1. Prepare a Sales Budget, Production Budget, Direct Materials Budget, Direct Labour Budget, Manufacturing Overhead Budget and Sales and Administration Budget. 2. Prepare Schedules of Expected Cash Collections and Expected Cash Disbursements for Materials as well as a Cash Budget. 3. Prepare a Budgeted Income Statement for the Quarter Ended June 30th. 4. Prepare a Budgeted Balance Sheet at June 30th. Use excel to do your work. Use cell references and formulas in your schedules