Sales Budget (July 1, 2022 - June 30, 2023) \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Schedule of Cash Collections (Receipts) } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Accts Rec Balance Forward & 150,000 & & & & 150,000 \\ \hline \multirow{2}{*}{\multicolumn{6}{|c|}{\begin{tabular}{l} First quarter sales \\ Second quarter sales \end{tabular}}} \\ \hline \multirow{2}{*}{\multicolumn{4}{|c|}{ Third quarter sales }} & & \\ \hline \multirow{2}{*}{\multicolumn{5}{|c|}{ Fourth quarter sales }} & \\ \hline & & & & & \\ \hline Total Cash Collections & & & & & \\ \hline \end{tabular} -Fourth quarter sales NOT collected in Q4 is the ending AR on the balance sheet \begin{tabular}{|c|c|c|} \hline \begin{tabular}{c} Fourth \\ Quarter Sales \end{tabular} & \begin{tabular}{c} Percent \\ Uncollected \end{tabular} & Ending AR \\ \hline \end{tabular} \begin{tabular}{|l|c|c|c|c|c|} \hline \multicolumn{1}{|c|}{ Production Budget } & & \\ \hline Budgeted Sales in Units & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Add: Desired Ending Invtory & & & & \\ \hline =Total Needed & & & & \\ \hline Less: Beginning Inventory & & & & \\ \hline = Units to be Produced & & & & \\ \hline \end{tabular} *Fourth quarter purchase NOT paid in Q4 is the ending AP on the balance sheet \begin{tabular}{|c|c|c|} \hline \begin{tabular}{c} Fourth \\ Quarter \\ Purchases \end{tabular} & \begin{tabular}{c} Percent Not \\ Paid \end{tabular} & Ending AP \\ \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 \multicolumn{6}{|l|}{ Total Production in Units } \\ \hline \multicolumn{6}{|l|}{ X Direct Labor Hours per Unit } \\ \hline \multicolumn{6}{|l|}{ = Total Direct Labor Hous Required } \\ \hline \multicolumn{6}{|l|}{ X Labor Wage Rate } \\ \hline \multicolumn{6}{|l|}{= Total Direct Labor Costs } \\ \hline \multicolumn{6}{|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 overhead rate per DL. Hour & & & & & \\ \hline \multicolumn{6}{|l|}{ - Total Variable MFG Overhead } \\ \hline \multicolumn{6}{|l|}{ + Total Foxed Manufacturing Overhead } \\ \hline \multicolumn{6}{|l|}{= 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 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Selling and Admin Budget (S \& A) } \\ \hline Item & Q1 & Q2. & Q3 & Q4 & Year Total \\ \hline Variable Sales \& Admin Expenses: & & & & & \\ \hline Sales in Units & & & & & \\ \hline X Variable Sales \& Admin Rate per unit & & & & & \\ \hline= Variable Sales \& Admin Expense & & & & & \\ \hline + Total Fixed Sales \& Admin Expense & & & & & \\ \hline = Total Sales \& Admin Expenses & & & & & \\ \hline Less: Depreciation (non-cash expense) & & & & & \\ \hline = Cash Disbisbursements for S \& A Exp's & & & & & \\ \hline & & D & & & \\ \hline & & & & & \\ \hline \multicolumn{6}{|c|}{ Cash Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Beginning Cash Balance & 150,000 & $(100,000) & & & \\ \hline Add: Cash Collections & 5 & & & & \\ \hline= Total Cash Available & $150,000 & & & & \\ \hline \multicolumn{6}{|l|}{ Less: Cash Disbursements } \\ \hline Direct Materials & 5250,000 & & & & \\ \hline Direct Labor & =1 & & & & \\ \hline Manufacturing Overhead & = & & & & \\ \hline Selling \& Administrative & -1 & & & & \\ \hline Equipment Purchases & & & & & \\ \hline \multicolumn{6}{|l|}{ Dividends } \\ \hline Total Disbursements & $250,000 & & & & \\ \hline Excess (Deficiency) of cash avalubie over disbursements & $(100,000) & & & & \\ \hline \end{tabular} 7 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Ending Finished Goods Inventory (at Cost) } \\ \hline \multicolumn{6}{|c|}{\begin{tabular}{l} Hint: Use the infornation / previously calculated above (i.e. your PDOH) to determine the product cost of one unit, Once \\ completed, multiple the unit cost by the number of units in ending inventory. \end{tabular}} \\ \hline Ending Finished Goods Inventory Budget & Quantity & & rate) & \multicolumn{2}{|c|}{ Total Cost } \\ \hline Production per Box Car & per boxcar & & anit & \multicolumn{2}{|c|}{ per unit } \\ \hline Direct materials: & 0.00 & $ & & 5 & \\ \hline Direct labor & 0.00 & 5 & & 5 & \\ \hline Manufacturing overhosd & 0.00 & 5 & & 5 & \\ \hline Production Cost per Box Car & & & & $ & * \\ \hline Ending finished goods inventory: Box Cars & & & & & \\ \hline Ending finished goods inventory in dollars & & & & 5 & - \\ \hline \end{tabular} Tommy's Box Cars - Large Pro Forma Income Statement For the Fiscal Year Ended June 30, 2023 \begin{tabular}{|c|c|c|} \hline \multirow[t]{2}{*}{ Item } & \multicolumn{2}{|c|}{ Amount } \\ \hline & 5 & \\ \hline Cost of ooods sold & & \\ \hline Gross margin & 5 & - \\ \hline Seding 8 administrative expenses & & \\ \hline Net operating income & is & - \\ \hline Interest expense & & \\ \hline Net income & s & A= \\ \hline \end{tabular} Tommy's Box Cars - Large Pro Forma Balance Sheet As of June 30, 2023 AND June 30, 2022 \begin{tabular}{|c|c|c|} \hline Assets & 6/30/24 & 6/30/23 \\ \hline Cash & 5 & 150,000 \\ \hline Accounts receivinble. & 5 & 150,000 \\ \hline Dhect materials imentory & & 20,000 \\ \hline Finished Coods inventory (boxcars) & 5 & $ \\ \hline Plant and cquipment & & 2,300,000 \\ \hline Total assets & 5 & $2,620,000 \\ \hline Liabilities and Stockholders' Equity & 6/30/24 & 6/30/23 \\ \hline Accounts pavable & $ & 250,000 \\ \hline Notes porable-equipment & & \\ \hline Letter of Credit outstanding & & 5 \\ \hline Gapital stock & & 1,500,000 \\ \hline Retained Farnings & & 5870,000 \\ \hline Total habilities and stockholders equity & s & $2,620,000 \\ \hline \end{tabular} ACCOUNTING EQUATION OUT OF BALANCE DATE: June 1,2023 TO: cosuconsulting FROM: Mark Swain, President, Tornmy's Box Cars SUBJECT: Master Budget for the fiscal year July 1, 2023 - June 30, 2024 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, 2024 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 Owsion's Master Budget for the fiscal year ended (36) June 30, 2024 . The deliverables are as follows: 1. Sales budget, including a schedule of expected cash collections. 2. Production budget. 2. Production budget. 3. Direct materials budget, including a schedule of expected cash disbursements for materials. 4. Direct labor budget. 5. Manufacturing overthead budget. 6. Ending finished goods inventory budget calculating the expected value of the finished goods inventory as of (36) June 30, 2024 . 7. Seling and adminutrative expense budget. 8. Cash 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 astensk 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. 1 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. Ive attached a bricf 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 Mark Swain During 2022-2023 fiscal year, the average seling price for large box cars is expected to be (1) $70 per car. The Large Box Car Division forecasts the following units of sales. The collection pattern for Accounts Recevable 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. o. 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,2022, 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) 20% of the budgeted sales for the next quarter. They would like to have (10) 4,000 finished Box Cars on hand on (36) June 30, 2023 . Each large box car requires an averape of (11) 4.0 feet of wood. The Large Box Car Division buys wood 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 Division would like to have their direct materials inventory 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 Box Car Division buys its wood on account. It pays for (17) 50% of its purchases of direct materials in the quarter in which they were purchased and (18) 50% in the following quarter. Each large box car requires (19) 2 hours of direct labor. Employees engaged in direct labor will be paid an estimated (20) \$12.00 per labor hour. Wages and salaries are paid on the 15th and 30 of each month. Variable manufacturing overhead is estimated to be (21) $2.00 per direct labor hour for the coming fiscal year. All variable manufacturing overiead expenses are paid for in the quarter incurred. Fued manufacturing overhead is estimated to total (22) $148,500 each quarter, with (23) $15,000 out of the total amount of (22) $148,500 representing depreciation on machinery, equipment and the factory. All other fixed manufacturing overhead expenses are paid in cash in the quarter they occur. The fored manufacturing overhead rate wil be computed by dividing the year's total foxed manufacturing overhead by the year's budgeted direct labor hours. Round the fixed overhead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed selling 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, fumiture and equipment. Other than depreciation, all seling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4m quarter, the Large Box Car Division plans to buy new machinery and equipment for (27) $400,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 vear. The Large Box Car Divison expects to pay (28) 50% down in cash and finance the remaining (29) 50% of the equipment cost wh a note payable from a local bank with whom they do business with. No interest payoble will accrue on the equipment note payable until after (36) June 30,2024 . You must record the initial cost outiay for the entire cost of the new machinery as well as who the cost inflow resated to the financing from the local bank. The Division must maintain a minimum cash balance of (30)$100,000. If after accounting for cash recelpts and disbursements (including dividends) in the cash budget, the budgeted cash avalable cash falis 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 (L.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 avallable 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) 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 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) \$15,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 Larpe Box Car Dwsion on (34) June 30, 2023 (which is the same as the budgeted balance sheet at the beginning of bueiness (35) July 1,2023 ) is presented below, Tommy/s Box Cars owns 100% of the Capital Stock of the Large Box Car Division. (19) Newe se sos? \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline TOTAL ASSETS & $2,620,000 & TOTAL LIAB, \& SE & $2,620,000 \\ \hline \end{tabular} Sales Budget (July 1, 2022 - June 30, 2023) \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Schedule of Cash Collections (Receipts) } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Accts Rec Balance Forward & 150,000 & & & & 150,000 \\ \hline \multirow{2}{*}{\multicolumn{6}{|c|}{\begin{tabular}{l} First quarter sales \\ Second quarter sales \end{tabular}}} \\ \hline \multirow{2}{*}{\multicolumn{4}{|c|}{ Third quarter sales }} & & \\ \hline \multirow{2}{*}{\multicolumn{5}{|c|}{ Fourth quarter sales }} & \\ \hline & & & & & \\ \hline Total Cash Collections & & & & & \\ \hline \end{tabular} -Fourth quarter sales NOT collected in Q4 is the ending AR on the balance sheet \begin{tabular}{|c|c|c|} \hline \begin{tabular}{c} Fourth \\ Quarter Sales \end{tabular} & \begin{tabular}{c} Percent \\ Uncollected \end{tabular} & Ending AR \\ \hline \end{tabular} \begin{tabular}{|l|c|c|c|c|c|} \hline \multicolumn{1}{|c|}{ Production Budget } & & \\ \hline Budgeted Sales in Units & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Add: Desired Ending Invtory & & & & \\ \hline =Total Needed & & & & \\ \hline Less: Beginning Inventory & & & & \\ \hline = Units to be Produced & & & & \\ \hline \end{tabular} *Fourth quarter purchase NOT paid in Q4 is the ending AP on the balance sheet \begin{tabular}{|c|c|c|} \hline \begin{tabular}{c} Fourth \\ Quarter \\ Purchases \end{tabular} & \begin{tabular}{c} Percent Not \\ Paid \end{tabular} & Ending AP \\ \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 \multicolumn{6}{|l|}{ Total Production in Units } \\ \hline \multicolumn{6}{|l|}{ X Direct Labor Hours per Unit } \\ \hline \multicolumn{6}{|l|}{ = Total Direct Labor Hous Required } \\ \hline \multicolumn{6}{|l|}{ X Labor Wage Rate } \\ \hline \multicolumn{6}{|l|}{= Total Direct Labor Costs } \\ \hline \multicolumn{6}{|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 overhead rate per DL. Hour & & & & & \\ \hline \multicolumn{6}{|l|}{ - Total Variable MFG Overhead } \\ \hline \multicolumn{6}{|l|}{ + Total Foxed Manufacturing Overhead } \\ \hline \multicolumn{6}{|l|}{= 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 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Selling and Admin Budget (S \& A) } \\ \hline Item & Q1 & Q2. & Q3 & Q4 & Year Total \\ \hline Variable Sales \& Admin Expenses: & & & & & \\ \hline Sales in Units & & & & & \\ \hline X Variable Sales \& Admin Rate per unit & & & & & \\ \hline= Variable Sales \& Admin Expense & & & & & \\ \hline + Total Fixed Sales \& Admin Expense & & & & & \\ \hline = Total Sales \& Admin Expenses & & & & & \\ \hline Less: Depreciation (non-cash expense) & & & & & \\ \hline = Cash Disbisbursements for S \& A Exp's & & & & & \\ \hline & & D & & & \\ \hline & & & & & \\ \hline \multicolumn{6}{|c|}{ Cash Budget } \\ \hline Item & Q1 & Q2 & Q3 & Q4 & Year Total \\ \hline Beginning Cash Balance & 150,000 & $(100,000) & & & \\ \hline Add: Cash Collections & 5 & & & & \\ \hline= Total Cash Available & $150,000 & & & & \\ \hline \multicolumn{6}{|l|}{ Less: Cash Disbursements } \\ \hline Direct Materials & 5250,000 & & & & \\ \hline Direct Labor & =1 & & & & \\ \hline Manufacturing Overhead & = & & & & \\ \hline Selling \& Administrative & -1 & & & & \\ \hline Equipment Purchases & & & & & \\ \hline \multicolumn{6}{|l|}{ Dividends } \\ \hline Total Disbursements & $250,000 & & & & \\ \hline Excess (Deficiency) of cash avalubie over disbursements & $(100,000) & & & & \\ \hline \end{tabular} 7 \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Ending Finished Goods Inventory (at Cost) } \\ \hline \multicolumn{6}{|c|}{\begin{tabular}{l} Hint: Use the infornation / previously calculated above (i.e. your PDOH) to determine the product cost of one unit, Once \\ completed, multiple the unit cost by the number of units in ending inventory. \end{tabular}} \\ \hline Ending Finished Goods Inventory Budget & Quantity & & rate) & \multicolumn{2}{|c|}{ Total Cost } \\ \hline Production per Box Car & per boxcar & & anit & \multicolumn{2}{|c|}{ per unit } \\ \hline Direct materials: & 0.00 & $ & & 5 & \\ \hline Direct labor & 0.00 & 5 & & 5 & \\ \hline Manufacturing overhosd & 0.00 & 5 & & 5 & \\ \hline Production Cost per Box Car & & & & $ & * \\ \hline Ending finished goods inventory: Box Cars & & & & & \\ \hline Ending finished goods inventory in dollars & & & & 5 & - \\ \hline \end{tabular} Tommy's Box Cars - Large Pro Forma Income Statement For the Fiscal Year Ended June 30, 2023 \begin{tabular}{|c|c|c|} \hline \multirow[t]{2}{*}{ Item } & \multicolumn{2}{|c|}{ Amount } \\ \hline & 5 & \\ \hline Cost of ooods sold & & \\ \hline Gross margin & 5 & - \\ \hline Seding 8 administrative expenses & & \\ \hline Net operating income & is & - \\ \hline Interest expense & & \\ \hline Net income & s & A= \\ \hline \end{tabular} Tommy's Box Cars - Large Pro Forma Balance Sheet As of June 30, 2023 AND June 30, 2022 \begin{tabular}{|c|c|c|} \hline Assets & 6/30/24 & 6/30/23 \\ \hline Cash & 5 & 150,000 \\ \hline Accounts receivinble. & 5 & 150,000 \\ \hline Dhect materials imentory & & 20,000 \\ \hline Finished Coods inventory (boxcars) & 5 & $ \\ \hline Plant and cquipment & & 2,300,000 \\ \hline Total assets & 5 & $2,620,000 \\ \hline Liabilities and Stockholders' Equity & 6/30/24 & 6/30/23 \\ \hline Accounts pavable & $ & 250,000 \\ \hline Notes porable-equipment & & \\ \hline Letter of Credit outstanding & & 5 \\ \hline Gapital stock & & 1,500,000 \\ \hline Retained Farnings & & 5870,000 \\ \hline Total habilities and stockholders equity & s & $2,620,000 \\ \hline \end{tabular} ACCOUNTING EQUATION OUT OF BALANCE DATE: June 1,2023 TO: cosuconsulting FROM: Mark Swain, President, Tornmy's Box Cars SUBJECT: Master Budget for the fiscal year July 1, 2023 - June 30, 2024 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, 2024 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 Owsion's Master Budget for the fiscal year ended (36) June 30, 2024 . The deliverables are as follows: 1. Sales budget, including a schedule of expected cash collections. 2. Production budget. 2. Production budget. 3. Direct materials budget, including a schedule of expected cash disbursements for materials. 4. Direct labor budget. 5. Manufacturing overthead budget. 6. Ending finished goods inventory budget calculating the expected value of the finished goods inventory as of (36) June 30, 2024 . 7. Seling and adminutrative expense budget. 8. Cash 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 astensk 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. 1 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. Ive attached a bricf 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 Mark Swain During 2022-2023 fiscal year, the average seling price for large box cars is expected to be (1) $70 per car. The Large Box Car Division forecasts the following units of sales. The collection pattern for Accounts Recevable 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. o. 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,2022, 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) 20% of the budgeted sales for the next quarter. They would like to have (10) 4,000 finished Box Cars on hand on (36) June 30, 2023 . Each large box car requires an averape of (11) 4.0 feet of wood. The Large Box Car Division buys wood 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 Division would like to have their direct materials inventory 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 Box Car Division buys its wood on account. It pays for (17) 50% of its purchases of direct materials in the quarter in which they were purchased and (18) 50% in the following quarter. Each large box car requires (19) 2 hours of direct labor. Employees engaged in direct labor will be paid an estimated (20) \$12.00 per labor hour. Wages and salaries are paid on the 15th and 30 of each month. Variable manufacturing overhead is estimated to be (21) $2.00 per direct labor hour for the coming fiscal year. All variable manufacturing overiead expenses are paid for in the quarter incurred. Fued manufacturing overhead is estimated to total (22) $148,500 each quarter, with (23) $15,000 out of the total amount of (22) $148,500 representing depreciation on machinery, equipment and the factory. All other fixed manufacturing overhead expenses are paid in cash in the quarter they occur. The fored manufacturing overhead rate wil be computed by dividing the year's total foxed manufacturing overhead by the year's budgeted direct labor hours. Round the fixed overhead rate to the nearest penny. Variable selling and administrative expenses are estimated to be (24) $6.00 per box car sold. Fixed selling 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, fumiture and equipment. Other than depreciation, all seling and administrative expenses are paid for in the quarter they occur. On (36) June, 2024, the last month of the 4m quarter, the Large Box Car Division plans to buy new machinery and equipment for (27) $400,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 vear. The Large Box Car Divison expects to pay (28) 50% down in cash and finance the remaining (29) 50% of the equipment cost wh a note payable from a local bank with whom they do business with. No interest payoble will accrue on the equipment note payable until after (36) June 30,2024 . You must record the initial cost outiay for the entire cost of the new machinery as well as who the cost inflow resated to the financing from the local bank. The Division must maintain a minimum cash balance of (30)$100,000. If after accounting for cash recelpts and disbursements (including dividends) in the cash budget, the budgeted cash avalable cash falis 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 (L.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 avallable 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) 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 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) \$15,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 Larpe Box Car Dwsion on (34) June 30, 2023 (which is the same as the budgeted balance sheet at the beginning of bueiness (35) July 1,2023 ) is presented below, Tommy/s Box Cars owns 100% of the Capital Stock of the Large Box Car Division. (19) Newe se sos? \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline TOTAL ASSETS & $2,620,000 & TOTAL LIAB, \& SE & $2,620,000 \\ \hline \end{tabular}