I have solved the first 6 parts of the problem, I need help with the budgets 8,9,10

Our controller, Tommy Swain is negotiating with potential new Wood suppliers in Kentucky. We need the Large Box Car Division's Master Budget for the fiscal year ended (36) June 30, 2021 for our corporate strategic planning process, and we cannot wait for Tommy's return from Kentucky. We would like you to prepare the Large Box Car Division's Master Budget for the fiscal year ended June 30, 2021. 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 (36) June 30, 2021. * and administrative expense budget. 8. Cash budget. 9. Budgeted income statement for the year ended (36) June 30, 2021. * 10. Budgeted balance sheet for (36) June 30, 2021. * All the Master Budget schedules except those marked with an asterisk for the Large Box Car 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 (schedules 9 and 10) and we only need a year-end total for the value of finished goods inventory (schedule 6). The hard copies of these budget schedules should be delivered by the company deadline. You can print more than one schedule per page, but do not have a page break in the middle of a budget schedule. I like to be able to view an entire budget schedule without flipping back and forth between pages. Please also use a type font of between 10-12 points for printing. We also need you to submit (via e-mail) the Excel spreadsheet that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Upload the Excel spreadsheet on Blackboard. We need that spreadsheet file the night before the meeting. I've attached a brief description of the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your results. Sincerely, mark During 2019-20 fiscal year, the average selling price for large box cars is expected to be (1) $145 per car. The Large Box Car Division forecasts the following units of sales. Quarter Box Car UNIT Sales (2-5) First 65,000 Second 70,000 Third 75,000 Fourth 85,000 The collection pattern for Accounts Receivable is as follows: (6) 35 percent of all sales are collected within the quarter in which they are sold (7) 65 percent of all sales are collected in the following quarter. There are no bad debts/uncollectible accounts. Due to high demand last year, the Large Box Car Division expects to have (8) zero finished box cars in inventory on (35) July 1, 2020, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is (8) Zero). To avoid having that problem in the coming fiscal year, the Large Box Car Division would like to have the ending inventory of Box Car at the end of each of the first three quarters equal to (9) 35% of the budgeted sales for the next quarter. They would like to have (10) 30,000 finished Box Cars on hand on (36) June 30, 2021. First Second Third | Fourth Quarter Ending FG inventory of Box Cars as a % of the next quarter's budgeted sales (9) Ending FG inventory of Box Cars (10) 35% 35% 35% 30,000 Each large box car requires an average of (11) 4.0 feet of wood. The Large Box Car Division buys wood for (13) $5.00 per foot and they expect the price to remain constant throughout the year. They expect to have (12) 60,000 feet of wood (RAW MATERIALS) on hand as of July 1, 2019 ((12) 60,000*((13) $5.00 = (14) $300,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 Car Division would like to have their direct materials inventory quantity to equal (15) 25 percent of the amount required for the following quarter's planned production. On (33) June 30, 2020, the end of the fiscal year, Large Box Car Division would like to have (16) 80,000 feet of wood on hand (This is ending Direct Material Inventory).. Quarter First Second Third Fourth 25% 25% 25% Ending DM inventory as a % of the next quarter's production requirement (15) Ending DM inventory in feet (16) 80,000 The Large Box Car Division buys its wood on account. It pays for (17) 30% of its purchases of direct materials in the quarter in which they were purchased and (18) 70% in the quarter after they were purchased. Each large box car requires (19) 6 hours of direct labor. Employees engaged in direct labor will be paid an estimated (20) 59.00 per labor hour. Wages and salaries are paid on the 15 and 30 of each month. Variable manufacturing overhead is estimated to be (21) $4.50 per direct labor hour for the coming fiscal year. Al variable manufacturing overhead expenses are paid for in the quarter incurred. Forced manufacturing overhead is estimated to total (22) $125,000 each quarter, with (23) $60,000 out of the total amount of (22) $125,000 representing depreciation on machinery, equipment and the factory. All other fuced manufacturing overhead expenses are paid in cash in the quarter they occur. The fored manufacturing overhead rate will be computed by dividing the year's total faced manufacturing overhead by the year's budgeted direct labor hours. Round the foxed overhead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $14.00 per box car sold. Foed seling and administrative expenses are expected to total (25) 595,000 each quarter, with (26) $30,000 out of the total amount of (25) 595,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. protocolos eed On (33) June 30, 2020 the Large Box Car Division plans to buy new machinery and equipment for (27) $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 Car Division expects to pay (28) 40% down in cash and finance the remaining (29) 60% 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 (33) June 30, 2020 The Division must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dividends) in the cash budget, the budgeted cash available cash falls below (30) $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 (30) $100,000 so that at no time during the quarter will the cash balance fall below (30) $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 (31) 2% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Qi's interest is not paid in cash until Q2 and Q2's Interest will be paid in Q3). As a fully owned subsidiary, the Large Box Car Division does not pay income taxes. All income taxes are charged to Tommy's Box Car's, the parent company. Large Box Car Division will pay dividends of (32) $70,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be paid, even if the Large Box Car Division has to borrow on its line of credit to make the payment The budgeted balance sheet for the Large Box Car Division on (34) June 30, 2020 (which is the same as the budgeted balance sheet at the beginning of business (35) July 1, 2020) is presented below. Tommy's Box Cars owns 100% of the Capital Stock of the Large Box Car Division. LARGE BOX CAR DIVISION - TOMMY'S BOX CARS BUDGETED BALANCE SHEET (34) JUNE 30, 2020 ASSETS Cash Accounts Receivable Raw Material Inventory (14) Plant and Equipment $1,450,000 3,900,000 300,000 8.900,000 LIABILITIES & EQUITY Accounts Payable $450.000 Notes Payable Capital Stock 3,500,000 Retained Earnings 10,650,000 TOTAL ASSETS $14.550.000 TOTAL LIAB. & SE $14.550.000 Value Notes Ttem $145) Sales Budget (July 1, 2020 - June 30, 2021) Q1 - Q2 03 65,000 70,000 75,000 $145 $145 $145 $9,425,000 $10,150,000 $10,875,000 Year Total 295,000 Units Sold Selling Price Per Unit Total Budgeted Sales Revenues 04 85,000 $145 $12,325,000 $145 5 $42.775,000 65,000 70,000 75,000 85,000 35.00% 65.00% Must = 100% 7 8 9 10 11 12 12 Item Budgeted Sales in Units Add: Desired Ending Invtory = Total Needed Less: Beginning Inventory Units to be produced Production Budget a 65,000 24500 89,500 02 70,000 26250 96,250 24500 71,750 08 1 75.000 29750 104,750 26250 78,500 04 85. 85,000 30000 115,000 29750 85,250 Year Total 295,000 30000 325,000 35.00% 30,000 89,500 325,000 Variable 1 Selng Price Per Unit 2 01_UnitSales 3 02_UnitSales 4 103_UnitSales 5 Q4_UnitSales 6 AR_Current_QTR_Collection_% | 7 AR_Following OTR Collection % 8 Beginning FG_Inventory_In_Units 9 Ending_Desired_FG_Inventory_% 10 Ending_FG_Inventory_Of_Box_Cars 11 AverageWoodFeetPerBoxca 12 Beginning_DM_Inventory_n_Feet 13 CostOfWoodPerfoot 14 TotalCostOfBeginning Direct Materias On Hand 15 Ending_Desired_DM_Inventory_% 16 Ending_DM_Inventory_In_Feet 17 Payment_%_In_Month_Of Purchase 18 Payment_%_In_Month_After_Purchase 19 DirectaborHoursRequiredPerBoxca 20 DirectLaborRatePer Hour 21 Variable_MOH_RatePe DirectLaborHour | 22 Total Fixed_MOH_Per Quarter 23 Depreciation AmountOffxed_MOH 24 Variable_Saling&AdminPerBoxca 25 TotalFixed_Seling&AdminExpenses Direct Materials Budget QQ 89,500 71,750 3 78,500 04 85,250 Year Total 325,000 60,000 $5.00 $300,000 25.00% 80,000 30.00% 70.00% Must = 100% Item 17 Total Production of Box Cars 18 Wood Feet per Car 19Total Wood Required (feet) 20 Add: Desired Ending Wood (feet) Total Needs (feet) 22 Less: Beginning Inventory Total DM to be purchased (feet) Cost per foot 25 Total cost of direct materials purchases $9.00 3580001 2870001 3140001 341000 1300000 71750 78500 85250 80.000 80,000 429750 365500 399250 421000 1380000 60,000 71750 78500 85250 60,000 369,750 293750 320750 335750 1,320,000 $5.00 $5.00 $5.00 $5.00 $5.00 $1,848,750.00 $1,468,750.00 $1,603,750.00 $1,678,750.00 $6,600,000.00 $4.50 $125,000 $60,000 $14.00 $95,000 Direct Labor Budget 0B 04 85,250 Year Total 325,000 89,500 71,750 78,500 20 31 32 23 Item Total Production in Units X Direct Labor Hours per Unit = Total Direct Labor Hous Required X Labor Wage Rate = Total Direct Labor Costs 537000 430500) 4710001 511500 1 950000 $9.00 89.00 $9.00 99.00 99.00 $4,833,000.00 $3,874,500.00 $4,239,000.00 $4,603,500.00 $17,550,000.00 Manufacturing Overhead Budget (MOH) Q1 Q2 T 3 1 04 Year Total 537000 430500 4 71000 511500 1950000 37 29 29 40 41 Item Variable MEG Overhead: Total direct labor hours x Variable overhead rate per DL Hour = Total Variable MFG Overhead + Total Fixed Manufacturing Overhead = Total Manufacturing Overhead Less: Depreciation (non-cash expense) = Cash Disbursements for MFG Overhead 25 Totalfixed_Seling&AdminExpenses $95,000 26 DepreciationAmountOfSeng&AdminExpense $30,000 27 TotalNewMachinery &EquipmentPurchase $1,200,000 28 CashDownpayment%.OnPlanned EquipmentPurchase 40.00% 29 Financing%OnEquipmentPlannedEquipmentPurchase 60.00% Must = 100% 30 Minimum CashBalance $100,000 31 BankinterestRatePer Quarter 2.00% 32 DividendsPadPerQuarter $70,000 33 PriorFiscal Year BeginDate July 1, 2019 Fomatas Text with 'inf 34 PriorFiscalYear EndDate June 30, 2020 Formatas Text with 'inf 35 Current scalYear BeginDate July 1, 2020 Formatas Text with 'in f 36 CurrentFiscalYear EndDate June 30, 2021 Formatas Text with 'inf BELOW IS THE BEGINNING BALANCE SHEET AMOUNTS 38 Cash $1,450,000 39 AccountsReceivable $3,900,000 40 Raw Materialsinventory (14) $300,000 41 Finished Goodsinventory (Box Cars) 42 PlandAndEquipment $8,900,000 43 TotalAssets 14,550,00 44 AccountsPayable $450,000 45 NotesPayable 46 LeterOfCreditOutstanding 47 Capitalstock $3,500,000 48 RetainedEarnings $10,600,000 49 Total Liabilities & Equity $14,550,00 FOTOLT OF BALANCE $0 $2,416,500.00 $1,937,250.00 $2,119,500.00 $2,301,750.00 $8,775,000.00 $125,000 $125,000 $125,000 $125,000 $500,000 $2,541,500.00 $2,062,250.00 $2,244,500.00 $2,426,750.00 $9,275,000.00 $60,000 $60,000 $60,000 $60,000 240,000 $2,481,500.00 $2,002,250.00 $2,184,500.00 $2,366,750.00 $9,035,000.00 44 & 48 Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH $9,275,000.00 - "Cost Driver" 1950000 51 48 Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH $9,275,000.00 - "Cost Driver" 1950000 = MOH to be "Applied Per Driver Unit $4.76 46 LeterOfCreditOutstanding 47 Capitastock 48 RetainedEarnings 49 Total Liabilities & Equity 50 OUT OF BALANCE $0 $3,500,000 $10,600,000 $14,550,00 $0 Selling and Admin Budget (S & A) qi I Q2 I 3 | 4 Year Total 56 $14.00 59 59 60 61 62 63 Ttem Variable Sales & Admin Expenses: Sales in Units X Variable Sales & Admin Rate per unit = Variable Sales & Admin Expense =+ Total Fixed Sales & Admin Expense = Total Sales & Admin Expenses Less: Depreciation (non-cash expense) = Cash Disbisbursements for S & A Exp's 65,000 70,000 75,000 85,000 295,000 $14.00 $14.00 $14.00 $14.00 $910,000.00 $990,000.00 $1,050,000.00 $1,190,000.00 $4,130,000.00 595,000 $95.000 395,000 595,000 $380,000 $1,005,000.00 $1,075,000.00 $1,145,000.00 $1,285,000.00 $4,510,000.00 $30,000 $30,000 $30,000 $30,000 $120,000 $975,000.00 $1,045,000.00 $1,115,000.00 $1,255,000.00 $4,390,000.00 Ttem Q3 04 68 69 70 71 72 73 Accts Rec Balance Forward First quarter sales Second quarter sales Third quarter sales Fourth quarter sales Total Cash Collections Schedule of Cash Collections (Receipts) Q2 $3,900,000 83,298,750 $6,126,250 $3,552,500 56,597,500 $3,806,250] Year Total $3,900,000 $9,425,000 $10,150,000 $10,875,000 $4,313,750 $38,663,750 $7,068,750 $4,313,750 $11,382,500 $7,198,750 $9,678,750 $10,403,750 77 78 79 80 81 82 Ttem Accts Payable Balance Forward First quarter Purchases Second quarter Purchases Third quarter Purchases Fourth quarter Purchases Total Cash Payments Schedule of Cash Disbursements 02 | 3 | 4 $450,000 $554,625.00 $1,294,125.00 $440,625.00 $1,028,125.00 $481,125.00 $1,122,625.00 $503,625.00 $1,004,625 $1,734,750.00 $1,509,250.00 $1,626,250.00 Year Total $450,000 $1,848,750.00 $1,468,750.00 $1,603,750.00 $503,625.00 $5,874,875 Our controller, Tommy Swain is negotiating with potential new Wood suppliers in Kentucky. We need the Large Box Car Division's Master Budget for the fiscal year ended (36) June 30, 2021 for our corporate strategic planning process, and we cannot wait for Tommy's return from Kentucky. We would like you to prepare the Large Box Car Division's Master Budget for the fiscal year ended June 30, 2021. 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 (36) June 30, 2021. * and administrative expense budget. 8. Cash budget. 9. Budgeted income statement for the year ended (36) June 30, 2021. * 10. Budgeted balance sheet for (36) June 30, 2021. * All the Master Budget schedules except those marked with an asterisk for the Large Box Car 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 (schedules 9 and 10) and we only need a year-end total for the value of finished goods inventory (schedule 6). The hard copies of these budget schedules should be delivered by the company deadline. You can print more than one schedule per page, but do not have a page break in the middle of a budget schedule. I like to be able to view an entire budget schedule without flipping back and forth between pages. Please also use a type font of between 10-12 points for printing. We also need you to submit (via e-mail) the Excel spreadsheet that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Upload the Excel spreadsheet on Blackboard. We need that spreadsheet file the night before the meeting. I've attached a brief description of the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your results. Sincerely, mark During 2019-20 fiscal year, the average selling price for large box cars is expected to be (1) $145 per car. The Large Box Car Division forecasts the following units of sales. Quarter Box Car UNIT Sales (2-5) First 65,000 Second 70,000 Third 75,000 Fourth 85,000 The collection pattern for Accounts Receivable is as follows: (6) 35 percent of all sales are collected within the quarter in which they are sold (7) 65 percent of all sales are collected in the following quarter. There are no bad debts/uncollectible accounts. Due to high demand last year, the Large Box Car Division expects to have (8) zero finished box cars in inventory on (35) July 1, 2020, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is (8) Zero). To avoid having that problem in the coming fiscal year, the Large Box Car Division would like to have the ending inventory of Box Car at the end of each of the first three quarters equal to (9) 35% of the budgeted sales for the next quarter. They would like to have (10) 30,000 finished Box Cars on hand on (36) June 30, 2021. First Second Third | Fourth Quarter Ending FG inventory of Box Cars as a % of the next quarter's budgeted sales (9) Ending FG inventory of Box Cars (10) 35% 35% 35% 30,000 Each large box car requires an average of (11) 4.0 feet of wood. The Large Box Car Division buys wood for (13) $5.00 per foot and they expect the price to remain constant throughout the year. They expect to have (12) 60,000 feet of wood (RAW MATERIALS) on hand as of July 1, 2019 ((12) 60,000*((13) $5.00 = (14) $300,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 Car Division would like to have their direct materials inventory quantity to equal (15) 25 percent of the amount required for the following quarter's planned production. On (33) June 30, 2020, the end of the fiscal year, Large Box Car Division would like to have (16) 80,000 feet of wood on hand (This is ending Direct Material Inventory).. Quarter First Second Third Fourth 25% 25% 25% Ending DM inventory as a % of the next quarter's production requirement (15) Ending DM inventory in feet (16) 80,000 The Large Box Car Division buys its wood on account. It pays for (17) 30% of its purchases of direct materials in the quarter in which they were purchased and (18) 70% in the quarter after they were purchased. Each large box car requires (19) 6 hours of direct labor. Employees engaged in direct labor will be paid an estimated (20) 59.00 per labor hour. Wages and salaries are paid on the 15 and 30 of each month. Variable manufacturing overhead is estimated to be (21) $4.50 per direct labor hour for the coming fiscal year. Al variable manufacturing overhead expenses are paid for in the quarter incurred. Forced manufacturing overhead is estimated to total (22) $125,000 each quarter, with (23) $60,000 out of the total amount of (22) $125,000 representing depreciation on machinery, equipment and the factory. All other fuced manufacturing overhead expenses are paid in cash in the quarter they occur. The fored manufacturing overhead rate will be computed by dividing the year's total faced manufacturing overhead by the year's budgeted direct labor hours. Round the foxed overhead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $14.00 per box car sold. Foed seling and administrative expenses are expected to total (25) 595,000 each quarter, with (26) $30,000 out of the total amount of (25) 595,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. protocolos eed On (33) June 30, 2020 the Large Box Car Division plans to buy new machinery and equipment for (27) $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 Car Division expects to pay (28) 40% down in cash and finance the remaining (29) 60% 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 (33) June 30, 2020 The Division must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dividends) in the cash budget, the budgeted cash available cash falls below (30) $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 (30) $100,000 so that at no time during the quarter will the cash balance fall below (30) $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 (31) 2% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Qi's interest is not paid in cash until Q2 and Q2's Interest will be paid in Q3). As a fully owned subsidiary, the Large Box Car Division does not pay income taxes. All income taxes are charged to Tommy's Box Car's, the parent company. Large Box Car Division will pay dividends of (32) $70,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be paid, even if the Large Box Car Division has to borrow on its line of credit to make the payment The budgeted balance sheet for the Large Box Car Division on (34) June 30, 2020 (which is the same as the budgeted balance sheet at the beginning of business (35) July 1, 2020) is presented below. Tommy's Box Cars owns 100% of the Capital Stock of the Large Box Car Division. LARGE BOX CAR DIVISION - TOMMY'S BOX CARS BUDGETED BALANCE SHEET (34) JUNE 30, 2020 ASSETS Cash Accounts Receivable Raw Material Inventory (14) Plant and Equipment $1,450,000 3,900,000 300,000 8.900,000 LIABILITIES & EQUITY Accounts Payable $450.000 Notes Payable Capital Stock 3,500,000 Retained Earnings 10,650,000 TOTAL ASSETS $14.550.000 TOTAL LIAB. & SE $14.550.000 Value Notes Ttem $145) Sales Budget (July 1, 2020 - June 30, 2021) Q1 - Q2 03 65,000 70,000 75,000 $145 $145 $145 $9,425,000 $10,150,000 $10,875,000 Year Total 295,000 Units Sold Selling Price Per Unit Total Budgeted Sales Revenues 04 85,000 $145 $12,325,000 $145 5 $42.775,000 65,000 70,000 75,000 85,000 35.00% 65.00% Must = 100% 7 8 9 10 11 12 12 Item Budgeted Sales in Units Add: Desired Ending Invtory = Total Needed Less: Beginning Inventory Units to be produced Production Budget a 65,000 24500 89,500 02 70,000 26250 96,250 24500 71,750 08 1 75.000 29750 104,750 26250 78,500 04 85. 85,000 30000 115,000 29750 85,250 Year Total 295,000 30000 325,000 35.00% 30,000 89,500 325,000 Variable 1 Selng Price Per Unit 2 01_UnitSales 3 02_UnitSales 4 103_UnitSales 5 Q4_UnitSales 6 AR_Current_QTR_Collection_% | 7 AR_Following OTR Collection % 8 Beginning FG_Inventory_In_Units 9 Ending_Desired_FG_Inventory_% 10 Ending_FG_Inventory_Of_Box_Cars 11 AverageWoodFeetPerBoxca 12 Beginning_DM_Inventory_n_Feet 13 CostOfWoodPerfoot 14 TotalCostOfBeginning Direct Materias On Hand 15 Ending_Desired_DM_Inventory_% 16 Ending_DM_Inventory_In_Feet 17 Payment_%_In_Month_Of Purchase 18 Payment_%_In_Month_After_Purchase 19 DirectaborHoursRequiredPerBoxca 20 DirectLaborRatePer Hour 21 Variable_MOH_RatePe DirectLaborHour | 22 Total Fixed_MOH_Per Quarter 23 Depreciation AmountOffxed_MOH 24 Variable_Saling&AdminPerBoxca 25 TotalFixed_Seling&AdminExpenses Direct Materials Budget QQ 89,500 71,750 3 78,500 04 85,250 Year Total 325,000 60,000 $5.00 $300,000 25.00% 80,000 30.00% 70.00% Must = 100% Item 17 Total Production of Box Cars 18 Wood Feet per Car 19Total Wood Required (feet) 20 Add: Desired Ending Wood (feet) Total Needs (feet) 22 Less: Beginning Inventory Total DM to be purchased (feet) Cost per foot 25 Total cost of direct materials purchases $9.00 3580001 2870001 3140001 341000 1300000 71750 78500 85250 80.000 80,000 429750 365500 399250 421000 1380000 60,000 71750 78500 85250 60,000 369,750 293750 320750 335750 1,320,000 $5.00 $5.00 $5.00 $5.00 $5.00 $1,848,750.00 $1,468,750.00 $1,603,750.00 $1,678,750.00 $6,600,000.00 $4.50 $125,000 $60,000 $14.00 $95,000 Direct Labor Budget 0B 04 85,250 Year Total 325,000 89,500 71,750 78,500 20 31 32 23 Item Total Production in Units X Direct Labor Hours per Unit = Total Direct Labor Hous Required X Labor Wage Rate = Total Direct Labor Costs 537000 430500) 4710001 511500 1 950000 $9.00 89.00 $9.00 99.00 99.00 $4,833,000.00 $3,874,500.00 $4,239,000.00 $4,603,500.00 $17,550,000.00 Manufacturing Overhead Budget (MOH) Q1 Q2 T 3 1 04 Year Total 537000 430500 4 71000 511500 1950000 37 29 29 40 41 Item Variable MEG Overhead: Total direct labor hours x Variable overhead rate per DL Hour = Total Variable MFG Overhead + Total Fixed Manufacturing Overhead = Total Manufacturing Overhead Less: Depreciation (non-cash expense) = Cash Disbursements for MFG Overhead 25 Totalfixed_Seling&AdminExpenses $95,000 26 DepreciationAmountOfSeng&AdminExpense $30,000 27 TotalNewMachinery &EquipmentPurchase $1,200,000 28 CashDownpayment%.OnPlanned EquipmentPurchase 40.00% 29 Financing%OnEquipmentPlannedEquipmentPurchase 60.00% Must = 100% 30 Minimum CashBalance $100,000 31 BankinterestRatePer Quarter 2.00% 32 DividendsPadPerQuarter $70,000 33 PriorFiscal Year BeginDate July 1, 2019 Fomatas Text with 'inf 34 PriorFiscalYear EndDate June 30, 2020 Formatas Text with 'inf 35 Current scalYear BeginDate July 1, 2020 Formatas Text with 'in f 36 CurrentFiscalYear EndDate June 30, 2021 Formatas Text with 'inf BELOW IS THE BEGINNING BALANCE SHEET AMOUNTS 38 Cash $1,450,000 39 AccountsReceivable $3,900,000 40 Raw Materialsinventory (14) $300,000 41 Finished Goodsinventory (Box Cars) 42 PlandAndEquipment $8,900,000 43 TotalAssets 14,550,00 44 AccountsPayable $450,000 45 NotesPayable 46 LeterOfCreditOutstanding 47 Capitalstock $3,500,000 48 RetainedEarnings $10,600,000 49 Total Liabilities & Equity $14,550,00 FOTOLT OF BALANCE $0 $2,416,500.00 $1,937,250.00 $2,119,500.00 $2,301,750.00 $8,775,000.00 $125,000 $125,000 $125,000 $125,000 $500,000 $2,541,500.00 $2,062,250.00 $2,244,500.00 $2,426,750.00 $9,275,000.00 $60,000 $60,000 $60,000 $60,000 240,000 $2,481,500.00 $2,002,250.00 $2,184,500.00 $2,366,750.00 $9,035,000.00 44 & 48 Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH $9,275,000.00 - "Cost Driver" 1950000 51 48 Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH $9,275,000.00 - "Cost Driver" 1950000 = MOH to be "Applied Per Driver Unit $4.76 46 LeterOfCreditOutstanding 47 Capitastock 48 RetainedEarnings 49 Total Liabilities & Equity 50 OUT OF BALANCE $0 $3,500,000 $10,600,000 $14,550,00 $0 Selling and Admin Budget (S & A) qi I Q2 I 3 | 4 Year Total 56 $14.00 59 59 60 61 62 63 Ttem Variable Sales & Admin Expenses: Sales in Units X Variable Sales & Admin Rate per unit = Variable Sales & Admin Expense =+ Total Fixed Sales & Admin Expense = Total Sales & Admin Expenses Less: Depreciation (non-cash expense) = Cash Disbisbursements for S & A Exp's 65,000 70,000 75,000 85,000 295,000 $14.00 $14.00 $14.00 $14.00 $910,000.00 $990,000.00 $1,050,000.00 $1,190,000.00 $4,130,000.00 595,000 $95.000 395,000 595,000 $380,000 $1,005,000.00 $1,075,000.00 $1,145,000.00 $1,285,000.00 $4,510,000.00 $30,000 $30,000 $30,000 $30,000 $120,000 $975,000.00 $1,045,000.00 $1,115,000.00 $1,255,000.00 $4,390,000.00 Ttem Q3 04 68 69 70 71 72 73 Accts Rec Balance Forward First quarter sales Second quarter sales Third quarter sales Fourth quarter sales Total Cash Collections Schedule of Cash Collections (Receipts) Q2 $3,900,000 83,298,750 $6,126,250 $3,552,500 56,597,500 $3,806,250] Year Total $3,900,000 $9,425,000 $10,150,000 $10,875,000 $4,313,750 $38,663,750 $7,068,750 $4,313,750 $11,382,500 $7,198,750 $9,678,750 $10,403,750 77 78 79 80 81 82 Ttem Accts Payable Balance Forward First quarter Purchases Second quarter Purchases Third quarter Purchases Fourth quarter Purchases Total Cash Payments Schedule of Cash Disbursements 02 | 3 | 4 $450,000 $554,625.00 $1,294,125.00 $440,625.00 $1,028,125.00 $481,125.00 $1,122,625.00 $503,625.00 $1,004,625 $1,734,750.00 $1,509,250.00 $1,626,250.00 Year Total $450,000 $1,848,750.00 $1,468,750.00 $1,603,750.00 $503,625.00 $5,874,875