just need to andwer the excel

answer

Dung 2022-2023 fiscal year, the average selling price for large box cars is eqpected to be (1) $70 per car. The Laroe Box Car Ohision forecasts the following units of sales. The collection pattern for Accounts Receivabic is as follows: (6) 70 percent of all sales are collected within the quarter in which they are sold (7) 30 percent of all sales are collected in the following quarter D. There art no bad debts/uncollectible accounts. Due to high denand lart yeas, the Large Box Car Division expects to have ( 8 ) zero finithed boxcars in inventory on (35) July 1, 2022, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is (8) Zero) To avoid harving that peoblem in the corring fiscal yeor, the Large Box Car Division would the to have the ending inventory of Elox Car at the end of each of the first three quarters equal to (9) 20% of the budgeted sales for the next quarter. They would tike to have (10) 4,000 finished BoxCars on hand on (36) June 30, 2023. Each large boxc car requires an average of (11) 4.0 teet of wood. The Large Box Car Diveson tury hood for (13)$2.00 per foot and they expect the price to remain constant throughout the year, They expect to have (12) 10,000 feet of wood (RAW MATERIALS) on hand as of (35) July 1, 202 ((12) 10,000* ((13) $2.00=(14)$20,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 Omision would like to have their direct materials imventory quantity to equal (15) 20% percent of the amount required for the following quarter's planned production, On (36) June 30, 2024, the end of the fiscal year, Large Box Car Division would like to have (16) 12,000 feet of wood on hand (This is ending Direct Material Inventory in units). The Large Hax Car Devision buys is wood on accounc. It pays for (17) 50% of its purchases of direct materists in the quarter in which they were purchased and (1s) 50% in the following quarter. Each large box car requires (19) 2 hours of direct tabor. Emplopees engaged in direct hobr will be paid an estimated (20) $12.00 per hoor hour. Wigers and salaries are paid on the 15 and 30 of each month. Varable manufacturing overhead be elimuled to be (21) $2.00 per clirect tabor hour for the coming fiscal ves. All variable manulacturing onerfeod expences are pad for in the quarter incurred. Foxd manufacturng owecheud is estmuted to total (22)$148,500 each quarter. with (23) $15,000 out of the total amount of (22)$148,500 repiesenting Tomany's Hox Cars - Large Pro Forma Income statement the Fiscal Year Ended June 30. 202 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Sales Budget (July 1, 2022 - June 30, 2023) } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Units Sold } \\ \hline \multicolumn{6}{|l|}{ Selling Price Per Unit } \\ \hline \multicolumn{6}{|l|}{ Total Budpeted Sales Revenues } \\ \hline & & & & 10 & \\ \hline \multicolumn{6}{|c|}{ Schedule of Cash Collections (Receipts) } \\ \hline Item: & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Aocts Rec Balance Forward & 150,000 & & & & 150,000 \\ \hline \multicolumn{6}{|l|}{ First quarter sales } \\ \hline \multicolumn{6}{|l|}{ Second quarter sales } \\ \hline \multicolumn{6}{|l|}{ Third quarter sales } \\ \hline \multicolumn{6}{|l|}{ Fourth quarter sales } \\ \hline Total Cash Collections & & & & & + \\ \hline \end{tabular} *Fourth quarter sales NOT collected in Q4 is the ending AR on the balance sheet \begin{tabular}{ccc|c|} \hline Fourth & Percent \\ Quarter Sales & Uncollected & Ending AR \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Production Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Budoeted Sales in Units \\ Add: Desired Ending Invtory \end{tabular}} \\ \hline \multirow{2}{*}{\multicolumn{6}{|c|}{\begin{tabular}{l} Add: Desired Ending Invtory \\ = Total Needed \end{tabular}}} \\ \hline \multicolumn{2}{|l|}{\begin{tabular}{l} = Total Needed \\ Less: Beginning Inventory \end{tabular}} & & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Less: Deginning Inventory \\ s Units to be Produced \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ - Units to be Produced } \\ \hline & & & & & \\ \hline \multicolumn{6}{|c|}{ Direct Materials Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production of Box Cars \\ Woed feet per Car \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Wood feet per Car \\ Total Wood Reouired (feet) \end{tabular}} \\ \hline \begin{tabular}{l} Total Wood Reouired (feet) \\ Template \end{tabular} & & & & & \\ \hline \end{tabular} Template \begin{tabular}{|l|l|l|} \hline TOTAL ASSETS & $2,620,000 \\ \hline TOTAL LAB. \& SE & $2,620,000 \\ \hline \end{tabular} depreciation on machinery, equipment and the factory. All other foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate wil be computed by dividing the year's total fued manufacturing overtyead by the year's budgeted direct labor hours. Round the fored overtvead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed seling and administrative expenses are expected to total (25) $40,000 each quarter, with (26)$20,000 out of the total amount of (25) $40,000 representing depreciation on the office space, furniture and equipment. Other than depreclation, all selling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4 "quarter, the Large Hox Car Divion plans to boy new machirery and equipment for (27)$400,000. The new machinery and equipment wil be acquired at the wery end of the fiscal year, so it wall 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 Dowbion expects to par (2a) 50% down in cash and finance the remaining (29) 50% of the equipenent cost with a note payable from a local bank weth whorn ther do business weth. Wo interest payoble will accrue on the equipment note payable until affer (36) June 30,2024 . You must record the initial cost outiy for the entire cost of the new machinery as well as who the cost inflow related to the finuncing from the local bank. The DNesion must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dindends) in the cash budget, the budgeted carh walable cash fals below (30) 5100,000 in any quarter, the Dinsion will need to borrow cash. They have arranged a line of credit. alowing it to barrow in $10,000 increments 0.6 . they can borrow 510,000 of $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the avalable cash would othenwise 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 increntents of $10,000 ). The bank charges the Division interest at the rate of (31) 6% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1's interest is not paid in cash until Q2 and Q2's Interest wil be paid in Q3). As a fully owned subsidiary, the Large BoxCar Divson does not pay income taves. Alincome taxes are charged to Tommy's BoxCar's, the parent company. Larpe Box Car Division wa pay dividends of (32) $15,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be pasd, even if the Large Box Car Dision has to borrow on its ine of credit to make the payment The budgeted batance sheet for the Large fox Car Divion on (34) June 30, 2023 (which is the camen as the budgeted balance sheet at the beginning of business (25) July 1, 2023) is presented below, Tommys Bor Cars owns toosi of the Copital Stock of the Large Box Car Divion. (no) Aure se nose DATE: June 1, 2023 TO: cosuconsulting FROM: Mark Swain, President, Tommy's Box Cars: SUBJECT: Master Budoet for the fiscal vear July 1,2023 - June 30, 2024 Our controlier, Tommy Swain is negotating with potental new Wood suppliers in Kentucky. We need the Large Box Car Divison's Marter Budget for the fiscal yoar ended (36) June 30, 2024 for our corporate strategic planning process, and we canoot wait for Tomrmys retum from Kentuchy. We would like you to prepare the Large fox Car Division's Master Budget for the fiscal year ended (36) June 30 , 2024. The deliverables are as follows: 1. Sales budget, inclueing a schedule of expected cash collections. 2. Production budget. 3. Drect materials budget, including a schodube of expected cash disbursements for matenak. 4. Direct labor budget 5. Manufacturing ovitiead budget 6. Ending fineshed goods inventory budget calculating the opected value of the finistred goods inventacy as of (36) June 30, 2024 7. Selimg and administrative experse budget: 8. Cawh budget. 9. Budgeted income statement for the year ended (36) June 30,2024. 10. Budgeted balance sheet for (36) June 30, 2024. All the Master Budget schedules except those marked with an asterisk for the Large Box Car Division should include a column for euch quarter and a total column for the fiscal year. We only need annual totals for the budpeted 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 deadine. You can print more than one schedule per page, but do not have a page break, in the middle of a budget schedule. I lake to be able to view an entire bodget schedule without flipping back and forth between pages: Please atso use a type font of between 10-12 points for printing. We also need you to submit (Ma e-mail) the Excel spresdshect that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Uplosd the Excel spreadsheet on Btackboard, We need that spreudsheet fle the night before the meeting. Tive attached a brief description or the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your resuits. sincerely. Mark Mark Swain Tommy's Box Cars - Latge Pro Forma Balance Sheet. \begin{tabular}{|l|l|l|} \hline TOTAL ASSETS & $2,620,000 \\ \hline TOTAL LAB. \& SE & $2,620,000 \\ \hline \end{tabular} depreciation on machinery, equipment and the factory. All other foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate wil be computed by dividing the year's total fued manufacturing overtyead by the year's budgeted direct labor hours. Round the fored overtvead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed seling and administrative expenses are expected to total (25) $40,000 each quarter, with (26)$20,000 out of the total amount of (25) $40,000 representing depreciation on the office space, furniture and equipment. Other than depreclation, all selling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4 "quarter, the Large Hox Car Divion plans to boy new machirery and equipment for (27)$400,000. The new machinery and equipment wil be acquired at the wery end of the fiscal year, so it wall 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 Dowbion expects to par (2a) 50% down in cash and finance the remaining (29) 50% of the equipenent cost with a note payable from a local bank weth whorn ther do business weth. Wo interest payoble will accrue on the equipment note payable until affer (36) June 30,2024 . You must record the initial cost outiy for the entire cost of the new machinery as well as who the cost inflow related to the finuncing from the local bank. The DNesion must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dindends) in the cash budget, the budgeted carh walable cash fals below (30) 5100,000 in any quarter, the Dinsion will need to borrow cash. They have arranged a line of credit. alowing it to barrow in $10,000 increments 0.6 . they can borrow 510,000 of $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the avalable cash would othenwise 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 increntents of $10,000 ). The bank charges the Division interest at the rate of (31) 6% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1's interest is not paid in cash until Q2 and Q2's Interest wil be paid in Q3). As a fully owned subsidiary, the Large BoxCar Divson does not pay income taves. Alincome taxes are charged to Tommy's BoxCar's, the parent company. Larpe Box Car Division wa pay dividends of (32) $15,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be pasd, even if the Large Box Car Dision has to borrow on its ine of credit to make the payment The budgeted batance sheet for the Large fox Car Divion on (34) June 30, 2023 (which is the camen as the budgeted balance sheet at the beginning of business (25) July 1, 2023) is presented below, Tommys Bor Cars owns toosi of the Copital Stock of the Large Box Car Divion. (no) Aure se nose "Fourth quarter purchase NOr paid in Q4 is the ending AP on the balance sheet \begin{tabular}{|c|c|c|c|c|} \hline \begin{tabular}{l} Fourth \\ Quarter \\ Purchases \end{tabular} & \begin{tabular}{c} Percent Not \\ Paid \end{tabular} & Ending AP & & \\ \hline & & 4 & & \\ \hline \multicolumn{5}{|c|}{ Direct Labor Budget } \\ \hline Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline & & & & \\ \hline & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Direct Labor Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Total Production in Units & & + & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production in Units \\ X Direct Labor Hours per Unit \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} X Direct Labor Hours per Unit \\ = Total Direct Labor Hous Required \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} = Total Direct Labor Hous Required \\ X Labor Wage Rate \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ = Total Direct Labor Costs } \\ \hline & \multicolumn{5}{|c|}{ Manufacturing Overhead Budget (MOH)} \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Variable MFG Overhead: } \\ \hline \multicolumn{6}{|l|}{ Total direct labor hours } \\ \hlinex Variable overihead rate per DL Hour & +1 & & & & \\ \hline - Total Variable MFo Overhead & & & & & \\ \hline + Total Fixed Manufacturing Overhead & & 5 & & & \\ \hline 8 - Total Manufacturing Overhead & & & & & \\ \hline Less: Depreciation (non-cash expense) & & & & & \\ \hline = Cash Disbursements for MFG Overhead & & & & & \\ \hline \end{tabular} Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH + "Cost Driver" = MoH to be "Applied" Per Driver Unit depreciation on machinery, equipment and the factory. All other foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate wil be computed by dividing the year's total fued manufacturing overtyead by the year's budgeted direct labor hours. Round the fored overtvead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed seling and administrative expenses are expected to total (25) $40,000 each quarter, with (26)$20,000 out of the total amount of (25) $40,000 representing depreciation on the office space, furniture and equipment. Other than depreclation, all selling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4 "quarter, the Large Hox Car Divion plans to boy new machirery and equipment for (27)$400,000. The new machinery and equipment wil be acquired at the wery end of the fiscal year, so it wall 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 Dowbion expects to par (2a) 50% down in cash and finance the remaining (29) 50% of the equipenent cost with a note payable from a local bank weth whorn ther do business weth. Wo interest payoble will accrue on the equipment note payable until affer (36) June 30,2024 . You must record the initial cost outiy for the entire cost of the new machinery as well as who the cost inflow related to the finuncing from the local bank. The DNesion must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dindends) in the cash budget, the budgeted carh walable cash fals below (30) 5100,000 in any quarter, the Dinsion will need to borrow cash. They have arranged a line of credit. alowing it to barrow in $10,000 increments 0.6 . they can borrow 510,000 of $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the avalable cash would othenwise 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 increntents of $10,000 ). The bank charges the Division interest at the rate of (31) 6% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1's interest is not paid in cash until Q2 and Q2's Interest wil be paid in Q3). As a fully owned subsidiary, the Large BoxCar Divson does not pay income taves. Alincome taxes are charged to Tommy's BoxCar's, the parent company. Larpe Box Car Division wa pay dividends of (32) $15,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be pasd, even if the Large Box Car Dision has to borrow on its ine of credit to make the payment The budgeted batance sheet for the Large fox Car Divion on (34) June 30, 2023 (which is the camen as the budgeted balance sheet at the beginning of business (25) July 1, 2023) is presented below, Tommys Bor Cars owns toosi of the Copital Stock of the Large Box Car Divion. (no) Aure se nose \begin{tabular}{|l|l|l|} \hline TOTAL ASSETS & $2,620,000 \\ \hline TOTAL LAB. \& SE & $2,620,000 \\ \hline \end{tabular} Dung 2022-2023 fiscal year, the average selling price for large box cars is eqpected to be (1) $70 per car. The Laroe Box Car Ohision forecasts the following units of sales. The collection pattern for Accounts Receivabic is as follows: (6) 70 percent of all sales are collected within the quarter in which they are sold (7) 30 percent of all sales are collected in the following quarter D. There art no bad debts/uncollectible accounts. Due to high denand lart yeas, the Large Box Car Division expects to have ( 8 ) zero finithed boxcars in inventory on (35) July 1, 2022, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is (8) Zero) To avoid harving that peoblem in the corring fiscal yeor, the Large Box Car Division would the to have the ending inventory of Elox Car at the end of each of the first three quarters equal to (9) 20% of the budgeted sales for the next quarter. They would tike to have (10) 4,000 finished BoxCars on hand on (36) June 30, 2023. Each large boxc car requires an average of (11) 4.0 teet of wood. The Large Box Car Diveson tury hood for (13)$2.00 per foot and they expect the price to remain constant throughout the year, They expect to have (12) 10,000 feet of wood (RAW MATERIALS) on hand as of (35) July 1, 202 ((12) 10,000* ((13) $2.00=(14)$20,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 Omision would like to have their direct materials imventory quantity to equal (15) 20% percent of the amount required for the following quarter's planned production, On (36) June 30, 2024, the end of the fiscal year, Large Box Car Division would like to have (16) 12,000 feet of wood on hand (This is ending Direct Material Inventory in units). The Large Hax Car Devision buys is wood on accounc. It pays for (17) 50% of its purchases of direct materists in the quarter in which they were purchased and (1s) 50% in the following quarter. Each large box car requires (19) 2 hours of direct tabor. Emplopees engaged in direct hobr will be paid an estimated (20) $12.00 per hoor hour. Wigers and salaries are paid on the 15 and 30 of each month. Varable manufacturing overhead be elimuled to be (21) $2.00 per clirect tabor hour for the coming fiscal ves. All variable manulacturing onerfeod expences are pad for in the quarter incurred. Foxd manufacturng owecheud is estmuted to total (22)$148,500 each quarter. with (23) $15,000 out of the total amount of (22)$148,500 repiesenting Tommy's Box Cars - Latge Pro Forma Balance Sheet. \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Sales Budget (July 1, 2022 - June 30, 2023) } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Units Sold } \\ \hline \multicolumn{6}{|l|}{ Selling Price Per Unit } \\ \hline \multicolumn{6}{|l|}{ Total Budpeted Sales Revenues } \\ \hline & & & & 10 & \\ \hline \multicolumn{6}{|c|}{ Schedule of Cash Collections (Receipts) } \\ \hline Item: & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Aocts Rec Balance Forward & 150,000 & & & & 150,000 \\ \hline \multicolumn{6}{|l|}{ First quarter sales } \\ \hline \multicolumn{6}{|l|}{ Second quarter sales } \\ \hline \multicolumn{6}{|l|}{ Third quarter sales } \\ \hline \multicolumn{6}{|l|}{ Fourth quarter sales } \\ \hline Total Cash Collections & & & & & + \\ \hline \end{tabular} *Fourth quarter sales NOT collected in Q4 is the ending AR on the balance sheet \begin{tabular}{ccc|c|} \hline Fourth & Percent \\ Quarter Sales & Uncollected & Ending AR \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Production Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Budoeted Sales in Units \\ Add: Desired Ending Invtory \end{tabular}} \\ \hline \multirow{2}{*}{\multicolumn{6}{|c|}{\begin{tabular}{l} Add: Desired Ending Invtory \\ = Total Needed \end{tabular}}} \\ \hline \multicolumn{2}{|l|}{\begin{tabular}{l} = Total Needed \\ Less: Beginning Inventory \end{tabular}} & & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Less: Deginning Inventory \\ s Units to be Produced \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ - Units to be Produced } \\ \hline & & & & & \\ \hline \multicolumn{6}{|c|}{ Direct Materials Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production of Box Cars \\ Woed feet per Car \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Wood feet per Car \\ Total Wood Reouired (feet) \end{tabular}} \\ \hline \begin{tabular}{l} Total Wood Reouired (feet) \\ Template \end{tabular} & & & & & \\ \hline \end{tabular} Template "Fourth quarter purchase NOr paid in Q4 is the ending AP on the balance sheet \begin{tabular}{|c|c|c|c|c|} \hline \begin{tabular}{l} Fourth \\ Quarter \\ Purchases \end{tabular} & \begin{tabular}{c} Percent Not \\ Paid \end{tabular} & Ending AP & & \\ \hline & & 4 & & \\ \hline \multicolumn{5}{|c|}{ Direct Labor Budget } \\ \hline Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline & & & & \\ \hline & & & & \\ \hline \end{tabular} Tomany's Hox Cars - Large Pro Forma Income statement the Fiscal Year Ended June 30. 202 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Direct Labor Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Total Production in Units & & + & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production in Units \\ X Direct Labor Hours per Unit \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} X Direct Labor Hours per Unit \\ = Total Direct Labor Hous Required \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} = Total Direct Labor Hous Required \\ X Labor Wage Rate \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ = Total Direct Labor Costs } \\ \hline & \multicolumn{5}{|c|}{ Manufacturing Overhead Budget (MOH)} \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Variable MFG Overhead: } \\ \hline \multicolumn{6}{|l|}{ Total direct labor hours } \\ \hlinex Variable overihead rate per DL Hour & +1 & & & & \\ \hline - Total Variable MFo Overhead & & & & & \\ \hline + Total Fixed Manufacturing Overhead & & 5 & & & \\ \hline 8 - Total Manufacturing Overhead & & & & & \\ \hline Less: Depreciation (non-cash expense) & & & & & \\ \hline = Cash Disbursements for MFG Overhead & & & & & \\ \hline \end{tabular} Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH + "Cost Driver" = MoH to be "Applied" Per Driver Unit "Fourth quarter purchase NOr paid in Q4 is the ending AP on the balance sheet \begin{tabular}{|c|c|c|c|c|} \hline \begin{tabular}{l} Fourth \\ Quarter \\ Purchases \end{tabular} & \begin{tabular}{c} Percent Not \\ Paid \end{tabular} & Ending AP & & \\ \hline & & 4 & & \\ \hline \multicolumn{5}{|c|}{ Direct Labor Budget } \\ \hline Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline & & & & \\ \hline & & & & \\ \hline \end{tabular} DATE: June 1, 2023 TO: cosuconsulting FROM: Mark Swain, President, Tommy's Box Cars: SUBJECT: Master Budoet for the fiscal vear July 1,2023 - June 30, 2024 Our controlier, Tommy Swain is negotating with potental new Wood suppliers in Kentucky. We need the Large Box Car Divison's Marter Budget for the fiscal yoar ended (36) June 30, 2024 for our corporate strategic planning process, and we canoot wait for Tomrmys retum from Kentuchy. We would like you to prepare the Large fox Car Division's Master Budget for the fiscal year ended (36) June 30 , 2024. The deliverables are as follows: 1. Sales budget, inclueing a schedule of expected cash collections. 2. Production budget. 3. Drect materials budget, including a schodube of expected cash disbursements for matenak. 4. Direct labor budget 5. Manufacturing ovitiead budget 6. Ending fineshed goods inventory budget calculating the opected value of the finistred goods inventacy as of (36) June 30, 2024 7. Selimg and administrative experse budget: 8. Cawh budget. 9. Budgeted income statement for the year ended (36) June 30,2024. 10. Budgeted balance sheet for (36) June 30, 2024. All the Master Budget schedules except those marked with an asterisk for the Large Box Car Division should include a column for euch quarter and a total column for the fiscal year. We only need annual totals for the budpeted 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 deadine. You can print more than one schedule per page, but do not have a page break, in the middle of a budget schedule. I lake to be able to view an entire bodget schedule without flipping back and forth between pages: Please atso use a type font of between 10-12 points for printing. We also need you to submit (Ma e-mail) the Excel spresdshect that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Uplosd the Excel spreadsheet on Btackboard, We need that spreudsheet fle the night before the meeting. Tive attached a brief description or the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your resuits. sincerely. Mark Mark Swain Dung 2022-2023 fiscal year, the average selling price for large box cars is eqpected to be (1) $70 per car. The Laroe Box Car Ohision forecasts the following units of sales. The collection pattern for Accounts Receivabic is as follows: (6) 70 percent of all sales are collected within the quarter in which they are sold (7) 30 percent of all sales are collected in the following quarter D. There art no bad debts/uncollectible accounts. Due to high denand lart yeas, the Large Box Car Division expects to have ( 8 ) zero finithed boxcars in inventory on (35) July 1, 2022, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is (8) Zero) To avoid harving that peoblem in the corring fiscal yeor, the Large Box Car Division would the to have the ending inventory of Elox Car at the end of each of the first three quarters equal to (9) 20% of the budgeted sales for the next quarter. They would tike to have (10) 4,000 finished BoxCars on hand on (36) June 30, 2023. Each large boxc car requires an average of (11) 4.0 teet of wood. The Large Box Car Diveson tury hood for (13)$2.00 per foot and they expect the price to remain constant throughout the year, They expect to have (12) 10,000 feet of wood (RAW MATERIALS) on hand as of (35) July 1, 202 ((12) 10,000* ((13) $2.00=(14)$20,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 Omision would like to have their direct materials imventory quantity to equal (15) 20% percent of the amount required for the following quarter's planned production, On (36) June 30, 2024, the end of the fiscal year, Large Box Car Division would like to have (16) 12,000 feet of wood on hand (This is ending Direct Material Inventory in units). The Large Hax Car Devision buys is wood on accounc. It pays for (17) 50% of its purchases of direct materists in the quarter in which they were purchased and (1s) 50% in the following quarter. Each large box car requires (19) 2 hours of direct tabor. Emplopees engaged in direct hobr will be paid an estimated (20) $12.00 per hoor hour. Wigers and salaries are paid on the 15 and 30 of each month. Varable manufacturing overhead be elimuled to be (21) $2.00 per clirect tabor hour for the coming fiscal ves. All variable manulacturing onerfeod expences are pad for in the quarter incurred. Foxd manufacturng owecheud is estmuted to total (22)$148,500 each quarter. with (23) $15,000 out of the total amount of (22)$148,500 repiesenting \begin{tabular}{|l|l|l|} \hline TOTAL ASSETS & $2,620,000 \\ \hline TOTAL LAB. \& SE & $2,620,000 \\ \hline \end{tabular} depreciation on machinery, equipment and the factory. All other foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate wil be computed by dividing the year's total fued manufacturing overtyead by the year's budgeted direct labor hours. Round the fored overtvead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed seling and administrative expenses are expected to total (25) $40,000 each quarter, with (26)$20,000 out of the total amount of (25) $40,000 representing depreciation on the office space, furniture and equipment. Other than depreclation, all selling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4 "quarter, the Large Hox Car Divion plans to boy new machirery and equipment for (27)$400,000. The new machinery and equipment wil be acquired at the wery end of the fiscal year, so it wall 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 Dowbion expects to par (2a) 50% down in cash and finance the remaining (29) 50% of the equipenent cost with a note payable from a local bank weth whorn ther do business weth. Wo interest payoble will accrue on the equipment note payable until affer (36) June 30,2024 . You must record the initial cost outiy for the entire cost of the new machinery as well as who the cost inflow related to the finuncing from the local bank. The DNesion must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dindends) in the cash budget, the budgeted carh walable cash fals below (30) 5100,000 in any quarter, the Dinsion will need to borrow cash. They have arranged a line of credit. alowing it to barrow in $10,000 increments 0.6 . they can borrow 510,000 of $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the avalable cash would othenwise 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 increntents of $10,000 ). The bank charges the Division interest at the rate of (31) 6% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1's interest is not paid in cash until Q2 and Q2's Interest wil be paid in Q3). As a fully owned subsidiary, the Large BoxCar Divson does not pay income taves. Alincome taxes are charged to Tommy's BoxCar's, the parent company. Larpe Box Car Division wa pay dividends of (32) $15,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be pasd, even if the Large Box Car Dision has to borrow on its ine of credit to make the payment The budgeted batance sheet for the Large fox Car Divion on (34) June 30, 2023 (which is the camen as the budgeted balance sheet at the beginning of business (25) July 1, 2023) is presented below, Tommys Bor Cars owns toosi of the Copital Stock of the Large Box Car Divion. (no) Aure se nose Tommy's Box Cars - Latge Pro Forma Balance Sheet. DATE: June 1, 2023 TO: cosuconsulting FROM: Mark Swain, President, Tommy's Box Cars: SUBJECT: Master Budoet for the fiscal vear July 1,2023 - June 30, 2024 Our controlier, Tommy Swain is negotating with potental new Wood suppliers in Kentucky. We need the Large Box Car Divison's Marter Budget for the fiscal yoar ended (36) June 30, 2024 for our corporate strategic planning process, and we canoot wait for Tomrmys retum from Kentuchy. We would like you to prepare the Large fox Car Division's Master Budget for the fiscal year ended (36) June 30 , 2024. The deliverables are as follows: 1. Sales budget, inclueing a schedule of expected cash collections. 2. Production budget. 3. Drect materials budget, including a schodube of expected cash disbursements for matenak. 4. Direct labor budget 5. Manufacturing ovitiead budget 6. Ending fineshed goods inventory budget calculating the opected value of the finistred goods inventacy as of (36) June 30, 2024 7. Selimg and administrative experse budget: 8. Cawh budget. 9. Budgeted income statement for the year ended (36) June 30,2024. 10. Budgeted balance sheet for (36) June 30, 2024. All the Master Budget schedules except those marked with an asterisk for the Large Box Car Division should include a column for euch quarter and a total column for the fiscal year. We only need annual totals for the budpeted 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 deadine. You can print more than one schedule per page, but do not have a page break, in the middle of a budget schedule. I lake to be able to view an entire bodget schedule without flipping back and forth between pages: Please atso use a type font of between 10-12 points for printing. We also need you to submit (Ma e-mail) the Excel spresdshect that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Uplosd the Excel spreadsheet on Btackboard, We need that spreudsheet fle the night before the meeting. Tive attached a brief description or the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your resuits. sincerely. Mark Mark Swain Tomany's Hox Cars - Large Pro Forma Income statement the Fiscal Year Ended June 30. 202 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Sales Budget (July 1, 2022 - June 30, 2023) } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Units Sold } \\ \hline \multicolumn{6}{|l|}{ Selling Price Per Unit } \\ \hline \multicolumn{6}{|l|}{ Total Budpeted Sales Revenues } \\ \hline & & & & 10 & \\ \hline \multicolumn{6}{|c|}{ Schedule of Cash Collections (Receipts) } \\ \hline Item: & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Aocts Rec Balance Forward & 150,000 & & & & 150,000 \\ \hline \multicolumn{6}{|l|}{ First quarter sales } \\ \hline \multicolumn{6}{|l|}{ Second quarter sales } \\ \hline \multicolumn{6}{|l|}{ Third quarter sales } \\ \hline \multicolumn{6}{|l|}{ Fourth quarter sales } \\ \hline Total Cash Collections & & & & & + \\ \hline \end{tabular} *Fourth quarter sales NOT collected in Q4 is the ending AR on the balance sheet \begin{tabular}{ccc|c|} \hline Fourth & Percent \\ Quarter Sales & Uncollected & Ending AR \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Production Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Budoeted Sales in Units \\ Add: Desired Ending Invtory \end{tabular}} \\ \hline \multirow{2}{*}{\multicolumn{6}{|c|}{\begin{tabular}{l} Add: Desired Ending Invtory \\ = Total Needed \end{tabular}}} \\ \hline \multicolumn{2}{|l|}{\begin{tabular}{l} = Total Needed \\ Less: Beginning Inventory \end{tabular}} & & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Less: Deginning Inventory \\ s Units to be Produced \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ - Units to be Produced } \\ \hline & & & & & \\ \hline \multicolumn{6}{|c|}{ Direct Materials Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production of Box Cars \\ Woed feet per Car \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Wood feet per Car \\ Total Wood Reouired (feet) \end{tabular}} \\ \hline \begin{tabular}{l} Total Wood Reouired (feet) \\ Template \end{tabular} & & & & & \\ \hline \end{tabular} Template Dung 2022-2023 fiscal year, the average selling price for large box cars is eqpected to be (1) $70 per car. The Laroe Box Car Ohision forecasts the following units of sales. The collection pattern for Accounts Receivabic is as follows: (6) 70 percent of all sales are collected within the quarter in which they are sold (7) 30 percent of all sales are collected in the following quarter D. There art no bad debts/uncollectible accounts. Due to high denand lart yeas, the Large Box Car Division expects to have ( 8 ) zero finithed boxcars in inventory on (35) July 1, 2022, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is (8) Zero) To avoid harving that peoblem in the corring fiscal yeor, the Large Box Car Division would the to have the ending inventory of Elox Car at the end of each of the first three quarters equal to (9) 20% of the budgeted sales for the next quarter. They would tike to have (10) 4,000 finished BoxCars on hand on (36) June 30, 2023. Each large boxc car requires an average of (11) 4.0 teet of wood. The Large Box Car Diveson tury hood for (13)$2.00 per foot and they expect the price to remain constant throughout the year, They expect to have (12) 10,000 feet of wood (RAW MATERIALS) on hand as of (35) July 1, 202 ((12) 10,000* ((13) $2.00=(14)$20,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 Omision would like to have their direct materials imventory quantity to equal (15) 20% percent of the amount required for the following quarter's planned production, On (36) June 30, 2024, the end of the fiscal year, Large Box Car Division would like to have (16) 12,000 feet of wood on hand (This is ending Direct Material Inventory in units). The Large Hax Car Devision buys is wood on accounc. It pays for (17) 50% of its purchases of direct materists in the quarter in which they were purchased and (1s) 50% in the following quarter. Each large box car requires (19) 2 hours of direct tabor. Emplopees engaged in direct hobr will be paid an estimated (20) $12.00 per hoor hour. Wigers and salaries are paid on the 15 and 30 of each month. Varable manufacturing overhead be elimuled to be (21) $2.00 per clirect tabor hour for the coming fiscal ves. All variable manulacturing onerfeod expences are pad for in the quarter incurred. Foxd manufacturng owecheud is estmuted to total (22)$148,500 each quarter. with (23) $15,000 out of the total amount of (22)$148,500 repiesenting Tomany's Hox Cars - Large Pro Forma Income statement the Fiscal Year Ended June 30. 202 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Sales Budget (July 1, 2022 - June 30, 2023) } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Units Sold } \\ \hline \multicolumn{6}{|l|}{ Selling Price Per Unit } \\ \hline \multicolumn{6}{|l|}{ Total Budpeted Sales Revenues } \\ \hline & & & & 10 & \\ \hline \multicolumn{6}{|c|}{ Schedule of Cash Collections (Receipts) } \\ \hline Item: & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Aocts Rec Balance Forward & 150,000 & & & & 150,000 \\ \hline \multicolumn{6}{|l|}{ First quarter sales } \\ \hline \multicolumn{6}{|l|}{ Second quarter sales } \\ \hline \multicolumn{6}{|l|}{ Third quarter sales } \\ \hline \multicolumn{6}{|l|}{ Fourth quarter sales } \\ \hline Total Cash Collections & & & & & + \\ \hline \end{tabular} *Fourth quarter sales NOT collected in Q4 is the ending AR on the balance sheet \begin{tabular}{ccc|c|} \hline Fourth & Percent \\ Quarter Sales & Uncollected & Ending AR \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Production Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Budoeted Sales in Units \\ Add: Desired Ending Invtory \end{tabular}} \\ \hline \multirow{2}{*}{\multicolumn{6}{|c|}{\begin{tabular}{l} Add: Desired Ending Invtory \\ = Total Needed \end{tabular}}} \\ \hline \multicolumn{2}{|l|}{\begin{tabular}{l} = Total Needed \\ Less: Beginning Inventory \end{tabular}} & & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Less: Deginning Inventory \\ s Units to be Produced \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ - Units to be Produced } \\ \hline & & & & & \\ \hline \multicolumn{6}{|c|}{ Direct Materials Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production of Box Cars \\ Woed feet per Car \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Wood feet per Car \\ Total Wood Reouired (feet) \end{tabular}} \\ \hline \begin{tabular}{l} Total Wood Reouired (feet) \\ Template \end{tabular} & & & & & \\ \hline \end{tabular} Template \begin{tabular}{|l|l|l|} \hline TOTAL ASSETS & $2,620,000 \\ \hline TOTAL LAB. \& SE & $2,620,000 \\ \hline \end{tabular} depreciation on machinery, equipment and the factory. All other foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate wil be computed by dividing the year's total fued manufacturing overtyead by the year's budgeted direct labor hours. Round the fored overtvead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed seling and administrative expenses are expected to total (25) $40,000 each quarter, with (26)$20,000 out of the total amount of (25) $40,000 representing depreciation on the office space, furniture and equipment. Other than depreclation, all selling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4 "quarter, the Large Hox Car Divion plans to boy new machirery and equipment for (27)$400,000. The new machinery and equipment wil be acquired at the wery end of the fiscal year, so it wall 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 Dowbion expects to par (2a) 50% down in cash and finance the remaining (29) 50% of the equipenent cost with a note payable from a local bank weth whorn ther do business weth. Wo interest payoble will accrue on the equipment note payable until affer (36) June 30,2024 . You must record the initial cost outiy for the entire cost of the new machinery as well as who the cost inflow related to the finuncing from the local bank. The DNesion must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dindends) in the cash budget, the budgeted carh walable cash fals below (30) 5100,000 in any quarter, the Dinsion will need to borrow cash. They have arranged a line of credit. alowing it to barrow in $10,000 increments 0.6 . they can borrow 510,000 of $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the avalable cash would othenwise 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 increntents of $10,000 ). The bank charges the Division interest at the rate of (31) 6% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1's interest is not paid in cash until Q2 and Q2's Interest wil be paid in Q3). As a fully owned subsidiary, the Large BoxCar Divson does not pay income taves. Alincome taxes are charged to Tommy's BoxCar's, the parent company. Larpe Box Car Division wa pay dividends of (32) $15,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be pasd, even if the Large Box Car Dision has to borrow on its ine of credit to make the payment The budgeted batance sheet for the Large fox Car Divion on (34) June 30, 2023 (which is the camen as the budgeted balance sheet at the beginning of business (25) July 1, 2023) is presented below, Tommys Bor Cars owns toosi of the Copital Stock of the Large Box Car Divion. (no) Aure se nose DATE: June 1, 2023 TO: cosuconsulting FROM: Mark Swain, President, Tommy's Box Cars: SUBJECT: Master Budoet for the fiscal vear July 1,2023 - June 30, 2024 Our controlier, Tommy Swain is negotating with potental new Wood suppliers in Kentucky. We need the Large Box Car Divison's Marter Budget for the fiscal yoar ended (36) June 30, 2024 for our corporate strategic planning process, and we canoot wait for Tomrmys retum from Kentuchy. We would like you to prepare the Large fox Car Division's Master Budget for the fiscal year ended (36) June 30 , 2024. The deliverables are as follows: 1. Sales budget, inclueing a schedule of expected cash collections. 2. Production budget. 3. Drect materials budget, including a schodube of expected cash disbursements for matenak. 4. Direct labor budget 5. Manufacturing ovitiead budget 6. Ending fineshed goods inventory budget calculating the opected value of the finistred goods inventacy as of (36) June 30, 2024 7. Selimg and administrative experse budget: 8. Cawh budget. 9. Budgeted income statement for the year ended (36) June 30,2024. 10. Budgeted balance sheet for (36) June 30, 2024. All the Master Budget schedules except those marked with an asterisk for the Large Box Car Division should include a column for euch quarter and a total column for the fiscal year. We only need annual totals for the budpeted 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 deadine. You can print more than one schedule per page, but do not have a page break, in the middle of a budget schedule. I lake to be able to view an entire bodget schedule without flipping back and forth between pages: Please atso use a type font of between 10-12 points for printing. We also need you to submit (Ma e-mail) the Excel spresdshect that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Uplosd the Excel spreadsheet on Btackboard, We need that spreudsheet fle the night before the meeting. Tive attached a brief description or the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your resuits. sincerely. Mark Mark Swain Tommy's Box Cars - Latge Pro Forma Balance Sheet. \begin{tabular}{|l|l|l|} \hline TOTAL ASSETS & $2,620,000 \\ \hline TOTAL LAB. \& SE & $2,620,000 \\ \hline \end{tabular} depreciation on machinery, equipment and the factory. All other foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate wil be computed by dividing the year's total fued manufacturing overtyead by the year's budgeted direct labor hours. Round the fored overtvead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed seling and administrative expenses are expected to total (25) $40,000 each quarter, with (26)$20,000 out of the total amount of (25) $40,000 representing depreciation on the office space, furniture and equipment. Other than depreclation, all selling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4 "quarter, the Large Hox Car Divion plans to boy new machirery and equipment for (27)$400,000. The new machinery and equipment wil be acquired at the wery end of the fiscal year, so it wall 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 Dowbion expects to par (2a) 50% down in cash and finance the remaining (29) 50% of the equipenent cost with a note payable from a local bank weth whorn ther do business weth. Wo interest payoble will accrue on the equipment note payable until affer (36) June 30,2024 . You must record the initial cost outiy for the entire cost of the new machinery as well as who the cost inflow related to the finuncing from the local bank. The DNesion must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dindends) in the cash budget, the budgeted carh walable cash fals below (30) 5100,000 in any quarter, the Dinsion will need to borrow cash. They have arranged a line of credit. alowing it to barrow in $10,000 increments 0.6 . they can borrow 510,000 of $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the avalable cash would othenwise 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 increntents of $10,000 ). The bank charges the Division interest at the rate of (31) 6% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1's interest is not paid in cash until Q2 and Q2's Interest wil be paid in Q3). As a fully owned subsidiary, the Large BoxCar Divson does not pay income taves. Alincome taxes are charged to Tommy's BoxCar's, the parent company. Larpe Box Car Division wa pay dividends of (32) $15,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be pasd, even if the Large Box Car Dision has to borrow on its ine of credit to make the payment The budgeted batance sheet for the Large fox Car Divion on (34) June 30, 2023 (which is the camen as the budgeted balance sheet at the beginning of business (25) July 1, 2023) is presented below, Tommys Bor Cars owns toosi of the Copital Stock of the Large Box Car Divion. (no) Aure se nose "Fourth quarter purchase NOr paid in Q4 is the ending AP on the balance sheet \begin{tabular}{|c|c|c|c|c|} \hline \begin{tabular}{l} Fourth \\ Quarter \\ Purchases \end{tabular} & \begin{tabular}{c} Percent Not \\ Paid \end{tabular} & Ending AP & & \\ \hline & & 4 & & \\ \hline \multicolumn{5}{|c|}{ Direct Labor Budget } \\ \hline Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline & & & & \\ \hline & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Direct Labor Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Total Production in Units & & + & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production in Units \\ X Direct Labor Hours per Unit \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} X Direct Labor Hours per Unit \\ = Total Direct Labor Hous Required \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} = Total Direct Labor Hous Required \\ X Labor Wage Rate \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ = Total Direct Labor Costs } \\ \hline & \multicolumn{5}{|c|}{ Manufacturing Overhead Budget (MOH)} \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Variable MFG Overhead: } \\ \hline \multicolumn{6}{|l|}{ Total direct labor hours } \\ \hlinex Variable overihead rate per DL Hour & +1 & & & & \\ \hline - Total Variable MFo Overhead & & & & & \\ \hline + Total Fixed Manufacturing Overhead & & 5 & & & \\ \hline 8 - Total Manufacturing Overhead & & & & & \\ \hline Less: Depreciation (non-cash expense) & & & & & \\ \hline = Cash Disbursements for MFG Overhead & & & & & \\ \hline \end{tabular} Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH + "Cost Driver" = MoH to be "Applied" Per Driver Unit depreciation on machinery, equipment and the factory. All other foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate wil be computed by dividing the year's total fued manufacturing overtyead by the year's budgeted direct labor hours. Round the fored overtvead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed seling and administrative expenses are expected to total (25) $40,000 each quarter, with (26)$20,000 out of the total amount of (25) $40,000 representing depreciation on the office space, furniture and equipment. Other than depreclation, all selling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4 "quarter, the Large Hox Car Divion plans to boy new machirery and equipment for (27)$400,000. The new machinery and equipment wil be acquired at the wery end of the fiscal year, so it wall 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 Dowbion expects to par (2a) 50% down in cash and finance the remaining (29) 50% of the equipenent cost with a note payable from a local bank weth whorn ther do business weth. Wo interest payoble will accrue on the equipment note payable until affer (36) June 30,2024 . You must record the initial cost outiy for the entire cost of the new machinery as well as who the cost inflow related to the finuncing from the local bank. The DNesion must maintain a minimum cash balance of (30) $100,000. If after accounting for cash receipts and disbursements (including dindends) in the cash budget, the budgeted carh walable cash fals below (30) 5100,000 in any quarter, the Dinsion will need to borrow cash. They have arranged a line of credit. alowing it to barrow in $10,000 increments 0.6 . they can borrow 510,000 of $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the avalable cash would othenwise 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 increntents of $10,000 ). The bank charges the Division interest at the rate of (31) 6% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1's interest is not paid in cash until Q2 and Q2's Interest wil be paid in Q3). As a fully owned subsidiary, the Large BoxCar Divson does not pay income taves. Alincome taxes are charged to Tommy's BoxCar's, the parent company. Larpe Box Car Division wa pay dividends of (32) $15,000 each quarter to its corporate parent, Tommy's Box Car's. The dividends must be pasd, even if the Large Box Car Dision has to borrow on its ine of credit to make the payment The budgeted batance sheet for the Large fox Car Divion on (34) June 30, 2023 (which is the camen as the budgeted balance sheet at the beginning of business (25) July 1, 2023) is presented below, Tommys Bor Cars owns toosi of the Copital Stock of the Large Box Car Divion. (no) Aure se nose \begin{tabular}{|l|l|l|} \hline TOTAL ASSETS & $2,620,000 \\ \hline TOTAL LAB. \& SE & $2,620,000 \\ \hline \end{tabular} Dung 2022-2023 fiscal year, the average selling price for large box cars is eqpected to be (1) $70 per car. The Laroe Box Car Ohision forecasts the following units of sales. The collection pattern for Accounts Receivabic is as follows: (6) 70 percent of all sales are collected within the quarter in which they are sold (7) 30 percent of all sales are collected in the following quarter D. There art no bad debts/uncollectible accounts. Due to high denand lart yeas, the Large Box Car Division expects to have ( 8 ) zero finithed boxcars in inventory on (35) July 1, 2022, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is (8) Zero) To avoid harving that peoblem in the corring fiscal yeor, the Large Box Car Division would the to have the ending inventory of Elox Car at the end of each of the first three quarters equal to (9) 20% of the budgeted sales for the next quarter. They would tike to have (10) 4,000 finished BoxCars on hand on (36) June 30, 2023. Each large boxc car requires an average of (11) 4.0 teet of wood. The Large Box Car Diveson tury hood for (13)$2.00 per foot and they expect the price to remain constant throughout the year, They expect to have (12) 10,000 feet of wood (RAW MATERIALS) on hand as of (35) July 1, 202 ((12) 10,000* ((13) $2.00=(14)$20,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 Omision would like to have their direct materials imventory quantity to equal (15) 20% percent of the amount required for the following quarter's planned production, On (36) June 30, 2024, the end of the fiscal year, Large Box Car Division would like to have (16) 12,000 feet of wood on hand (This is ending Direct Material Inventory in units). The Large Hax Car Devision buys is wood on accounc. It pays for (17) 50% of its purchases of direct materists in the quarter in which they were purchased and (1s) 50% in the following quarter. Each large box car requires (19) 2 hours of direct tabor. Emplopees engaged in direct hobr will be paid an estimated (20) $12.00 per hoor hour. Wigers and salaries are paid on the 15 and 30 of each month. Varable manufacturing overhead be elimuled to be (21) $2.00 per clirect tabor hour for the coming fiscal ves. All variable manulacturing onerfeod expences are pad for in the quarter incurred. Foxd manufacturng owecheud is estmuted to total (22)$148,500 each quarter. with (23) $15,000 out of the total amount of (22)$148,500 repiesenting Tommy's Box Cars - Latge Pro Forma Balance Sheet. \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Sales Budget (July 1, 2022 - June 30, 2023) } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{ Units Sold } \\ \hline \multicolumn{6}{|l|}{ Selling Price Per Unit } \\ \hline \multicolumn{6}{|l|}{ Total Budpeted Sales Revenues } \\ \hline & & & & 10 & \\ \hline \multicolumn{6}{|c|}{ Schedule of Cash Collections (Receipts) } \\ \hline Item: & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Aocts Rec Balance Forward & 150,000 & & & & 150,000 \\ \hline \multicolumn{6}{|l|}{ First quarter sales } \\ \hline \multicolumn{6}{|l|}{ Second quarter sales } \\ \hline \multicolumn{6}{|l|}{ Third quarter sales } \\ \hline \multicolumn{6}{|l|}{ Fourth quarter sales } \\ \hline Total Cash Collections & & & & & + \\ \hline \end{tabular} *Fourth quarter sales NOT collected in Q4 is the ending AR on the balance sheet \begin{tabular}{ccc|c|} \hline Fourth & Percent \\ Quarter Sales & Uncollected & Ending AR \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Production Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Budoeted Sales in Units \\ Add: Desired Ending Invtory \end{tabular}} \\ \hline \multirow{2}{*}{\multicolumn{6}{|c|}{\begin{tabular}{l} Add: Desired Ending Invtory \\ = Total Needed \end{tabular}}} \\ \hline \multicolumn{2}{|l|}{\begin{tabular}{l} = Total Needed \\ Less: Beginning Inventory \end{tabular}} & & & & \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Less: Deginning Inventory \\ s Units to be Produced \end{tabular}} \\ \hline \multicolumn{6}{|l|}{ - Units to be Produced } \\ \hline & & & & & \\ \hline \multicolumn{6}{|c|}{ Direct Materials Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Total Production of Box Cars \\ Woed feet per Car \end{tabular}} \\ \hline \multicolumn{6}{|l|}{\begin{tabular}{l} Wood feet per Car \\ Total Wood Reouired (feet) \end{tabular}} \\ \hline \begin{tabular}{l} Total Wood Reouired (feet) \\ Template \end{tabular} & & & & & \\ \hline \end{tabular} Template "Fourth quarter purchase NOr paid in Q4 is the ending AP on the balan