Our controller, Jimmy Treeman is negotiating with potential new wood suppliers in Wisconsin. We need the Large Box Crate Divisions Master Budget for the fiscal year ended June 30, 2023 for our corporate strategic planning process, and we cannot wait for Jims return from Wisconsin. We would like you to prepare the Large Box Crate Divisions Master Budget for the fiscal year ended June 30, 2023. The deliverables are as follows: 1. Sales budget, including a schedule of expected cash collections. 2. Production budget. 3. Direct materials budget, including a schedule of expected cash disbursements for materials. 4. Direct labor budget. 5. Manufacturing overhead budget. 6. Ending finished goods inventory budget calculating the expected value of the finished goods inventory as of June 30, 2023.* 7. Selling and administrative expense budget. 8. Cash budget. 9. Budgeted income statement for the year ended June 30, 2023.* 10. Budgeted balance sheet for June 30, 2023.* *All the Master Budget schedules except those marked with an asterisk for the Large Box Crate Division should include a column for each quarter and a total column for the fiscal year. We only need annual totals for the budgeted financial statements in schedules 9 and 10 and we only need a year-end total for the value of finished goods inventory in schedule 6. You may work alone or in a group of up to 3 people (if working as a group, make sure all names are submitted at the top of the Excel file). All of the deliverables should be prepared in Excel and use Excel formulas wherever possible. It is very important that you avoid hardcoding cells wherever possible and that you use cell references if the information is provided in an earlier schedule. You can develop your own Excel spreadsheet or utilize the provided Excel template. You should complete this assignment as soon as possible after Chapter 13 is covered in class and the recommended due date is before December 4th. The absolute deadline is December 11h. Ive attached the budget data Jimmy gave me before he left for Wisconsin. We eagerly await your results. Sincerely, Tim Packer During 2022-23 fiscal year, the average selling price for large box crates is expected to be $135 per crate. The Large Box Crate Division forecasts the following units of sales. Quarter First Second Third Fourth Box Crate sales 60,000 75,000 60,000 85,000 The collection pattern for Accounts Receivable is as follows: o 30 percent of all sales are collected within the quarter in which they are sold o 70 percent of all sales are collected in the following quarter. o There are no bad debts/uncollectibles. Due to higher than expected demand this year, the Large Box Crate Division expects to have no finished box crates in inventory on July 1, 2022, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is 0 Units). To avoid having that problem in the coming fiscal year, the Large Box Crate Division would like to have the ending inventory of box crates at the end of each of the first three quarters equal to 30% of the budgeted sales for the next quarter. They would like to have 30,000 finished Box Crates on hand on June 30, 2023. Quarter First Second Third Fourth Ending FG inventory of box crates as a % of the next quarters budgeted sales 30% 30% 30% ? Ending FG inventory of box crates ? ? ? 30,000 Each large box Crate requires an average of 3 feet of wood. The Large Box Crate Division buys wood for $5.50 per foot and they expect the price to remain constant throughout the year. They expect to have 50,000 feet of wood on hand as of July 1, 2022 (50,000 * $5.5 = 50,000 - This is beginning Direct Material Inventory), the beginning of the first quarter of the fiscal year. At the end of each of the first three quarters, the Large Box Crate Division would like to have their direct materials inventory quantity to equal 25 percent of the amount required for the following quarters planned production. On June 30, 2023, the end of the fiscal year, Large Box Crate Division would like to have 60,000 feet of wood on hand (This is ending Direct Material Inventory).. Quarter First Second Third Fourth Ending DM inventory as a % of the next quarters production requirement 25% 25% 25% ? Ending DM inventory in pounds ? ? ? 60,000 The Large Box Crate Division buys its wood on account. It pays for 25% of its purchases of direct materials in the quarter in which they were purchased and 75% in the quarter after they were purchased. Each large box crate requires 5.00 hours (240 minutes) of direct labor. Employees engaged in direct labor will be paid an estimated $10.00 per labor hour. Wages and salaries are paid on the 15th and 30th of each month. Variable manufacturing overhead is estimated to be $4.50 per direct labor hour for the coming fiscal year. All variable manufacturing overhead expenses are paid for in the quarter incurred. Fixed manufacturing overhead is estimated to total $120,000 each quarter, with $60,000 out of the total amount of $120,000 representing depreciation on machinery, equipment and the factory. All other fixed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate will be computed by dividing the years total fixed manufacturing overhead by the years budgeted direct labor hours. Round the fixed overhead rate to the nearest penny. Variable selling and administrative expenses are estimated to be $13.00 per box crate sold. Fixed selling and administrative expenses are expected to total $95,000 each quarter, with $35,000 out of the total amount of $95,000 representing depreciation on the office space, furniture and equipment. Other than depreciation, all selling and administrative expenses are paid for in the quarter they occur. On June 30, 2023 the Large Box Crate Division plans to buy new machinery and equipment for $1,200,000. The new machinery and equipment will be acquired at the very end of the fiscal year, so it will not be used in production and sales during the coming year and it will not be depreciated until the following year. The Large Box Crate Division expects to pay 50% down in cash and finance the remaining 50% of the equipment cost with a note payable from a local bank with whom they do business with. No interest payable will accrue on the equipment note payable until after June 30, 2023. The division must maintain a minimum cash balance of $100,000. If after accounting for cash receipts and disbursements (including dividends) in the cash budget, the budgeted cash available cash falls below $100,000 in any quarter, the division will need to borrow cash. They have arranged a line of credit allowing it to borrow in $10,000 increments (i.e. they can borrow 10,000 or 20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the available cash would otherwise be below $100,000 so that at no time during the quarter will the cash balance fall below $100,000 (after payment of interest). If there is extra cash at the end of the quarter and there is borrowing outstanding, the division should pay down principal (also in increments of $10,000). The bank charges the Division interest at the rate of 2% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1s interest is not paid in cash until Q2). As a fully owned subsidiary, the Large Box Crate Division does not pay income taxes. All income taxes are charged to Tims Box Crates, the parent company. Large Box Crate Division will pay dividends of $65,000 each quarter to its corporate parent, Tims Box Crates. The dividends must be paid, even if the Large Box Crate Division has to borrow on its line of credit to make the payment The budgeted balance sheet for the Large Box Crate Division on June 30, 2022 (which is the same as the budgeted balance sheet at the beginning of business July 1, 2022) is presented below. Tims Box Crates owns 100% of the Capital Stock of the Large Box Crate Division. LARGE BOX CRATE DIVISION TIMS BOX CRATES BUDGETED BALANCE SHEET JUNE 30, 2022 ASSETS LIABILITIES & EQUITY Cash $800,000 Accounts Payable $250,000 Accounts Receivable 3,500,000 Notes Payable 0 Raw Material Inventory 275,000 Capital Stock 4,000,000 Plant and Equipment 9,500,000 Retained Earnings 9,825,000 TOTAL ASSETS $14,075,000 TOTAL LIAB. & SE $14,075,000