Please supply notes for all figures that require calculations so that I can track your work. Use the answers in the Check Figures section to ensure you are submitting correct answers. Thank you!
You are the assistant controller of Boomstick Corp. Boomstick sells one product, baseball bats made out of white ash. You have been charged with preparing a cash budget for the first quarter (January to March) 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: October (actual) 6,000 November (actual) 4,500 December (actual) 12,000 January 10,000 February 26,000 March 35,000 April 30,000 May 30,000 June 28,000 Bats are sold to retailers for $50 each. The company has a policy of having an ending inventory each month equal to 75% of the next month's sales. The inventory balance at December 31st was 7,500 bats. Each bat requires 3 board feet of white ash, which the company purchases for $5 per board foot. To protect against disruptions in production, management likes to keep enough white ash on hand at all times equal to 150% of the next month's production needs. This requirement had been met on December 31st in that the company had 99,000 board feet of white ash in the warehouse. Purchases of white ash are paid for 50% at the time of purchase with the other 50% being paid the next month. All sales are on credit with 20% being collected in the month of sale, 55% being collected in the month following the month of sale and the remaining 25% being collected two months after the month of sale. Boom Stick has a tight credit policy and, as a result, does not have any bad debts. Bats are hand cut taking, on average, 6 minutes to produce (i.e. 0.1 hours). Employees cutting the bats are paid $20 per hour and never work overtime. Manufacturing overhead includes all the costs of production other than the white ash and direct labour. The variable component of manufacturing overhead is $3 per bat produced and the fixed component is $80,000 per month. Fixed manufacturing overhead includes $45,000 of depreciation. Manufacturing overhead is applied to bats produced on the basis of units of production. Boomstick's monthly operating expenses are given below: Variable: Licensing fee paid to Nelson Cruz $1 per bat sold Fixed: Wages and salaries Utilities Insurance Depreciation Miscellaneous $30,000 5,500 2,500 15,000 6,000 All operating expenses are paid in the month for cash, with the exception of depreciation and insurance. Insurance is paid once a year at the end of December ($30,000) then expensed monthly over the following year. The company plans to purchase some new manufacturing equipment in February for $60,000. Boomstick declares a dividend of $20,000 on the last day of every quarter (i.e. March 31st, June 30th, September 30th and December 31st) and pays it one month later (i.e. the March dividend is paid April 30th, the June dividend is paid July 31st, etc.) The balance sheet at December 31st is given below: Assets Cash Accounts receivable Inventory, white ash (raw materials) Inventory, bats (finished goods) Prepaid insurance Fixed assets 840,000 Accumulated depreciation (270,000) Total Assets $ 28,000 536,250 495,000 169,251 30,000 570,000 1,828,501 Liabilities and Shareholder's Equity Accounts payable, purchases Dividends payable Capital stock Retained earnings Total liabilities and shareholder's equity 208,125 20,000 10,000 1,590,376 1,828,501 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. All borrowing must be done at the beginning of the month, and repayments must be done at the end of the month. Borrowings and repayments must also be in increments of $1,000. Interest is paid each month based on the short term loan balance at the end of the previous month. 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, Ending Inventory 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 March 31st. 4. Prepare a Budgeted Balance Sheet at March 31st This is an individual assignment. Every student must complete and submit their own work All schedules and statements should be labelled clearly and easy to follow. Use excel to do your work. Use cell references and formulas in your schedules. If you're having difficulty you can email me a copy of your excel file and I'll point you in the right direction. Note that I will not answer any questions regarding the budget project received after noon on March 28th Upload your excel file to the Dropbox under the Budget Project section in Moodle by 11:59 PM on April 01st, 2022. Check figures: Total cash collections for the quarter 2,361,250 Total required production in units 86,000 Raw materials purchased in the quarter (board feet) 294,000 Ending cash balance 115,018 Total cost of goods manufactured for the quarter 1,960,000 Total assets 3,500,308
