Please answer Cash Budget, Cash budget (financing and repayment), ending finished goods inventory, income statement, and balance sheet

During 2020-21 fiscal year, the average selling price for large box cars is expected to be $130 per car. The Large Box Car Division forecasts the following units of sales.

Quarter	First	Second	Third	Fourth
Box Car UNIT Sales	65,000	70,000	55,000	60,000

The collection pattern for Accounts Receivable is as follows:

• 30 percent of all sales are collected within the quarter in which they are sold
• 70 percent of all sales are collected in the following quarter.
• There are no bad debts/uncollectible accounts.

Due to high demand last year, the Large Box Car Division expects to have zero finished box cars in inventory on July 1, 2020, the beginning of the first quarter of the new fiscal year (i.e. Beginning Finished Goods Inventory is 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 30% of the budgeted sales for the next quarter. They would like to have 35,000 finished Box Cars on hand on June 30, 2021.

Quarter	First	Second	Third	Fourth
Ending FG inventory of Box Cars as a % of the next quarters budgeted sales	30%	30%	30%	?
Ending FG inventory of Box Cars	?	?	?	35,000

Each large box car requires an average of 5.0 feet of wood. The Large Box Car Division buys wood for $4.00 per foot and they expect the price to remain constant throughout the year. They expect to have 50,000 feet of wood (RAW MATERIALS) on hand as of July 1, 2019 (50,000 * $4.00 = $200,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 25 percent of the amount required for the following quarters planned production. On June 30, 2020, the end of the fiscal year, Large Box Car Division would like to have 60,000 feet of wood on hand (This is ending Direct Material Inventory)..

Quarter	First	Second	Third	Fourth
Ending DM inventory as a % of the next quarters production requirement	25%	25%	25%	?
Ending DM inventory in feet	?	?	?	60,000

The Large Box Car Division buys its wood on account. It pays for 35% of its purchases of direct materials in the quarter in which they were purchased and 65% in the quarter after they were purchased.

Each large box car requires 5 hours of direct labor. Employees engaged in direct labor will be paid an estimated $10.00 per labor hour. Wages and salaries are paid on the 15^th and 30^th of each month.

Variable manufacturing overhead is estimated to be $4.50 per direct labor hour for the coming fiscal year. All variable manufacturing overhead expenses are paid for in the quarter incurred.

Fixed manufacturing overhead is estimated to total $120,000 each quarter, with $40,000 out of the total amount of $120,000 representing depreciation on machinery, equipment and the factory. All other fixed manufacturing overhead expenses are paid in cash in the quarter they occur. The fixed manufacturing overhead rate will be computed by dividing the years total fixed manufacturing overhead by the years budgeted direct labor hours. Round the fixed overhead rate to the nearest penny.

Variable selling and administrative expenses are estimated to be $12.00 per box car sold. Fixed selling and administrative expenses are expected to total $95,000 each quarter, with $30,000 out of the total amount of $95,000 representing depreciation on the office space, furniture and equipment. Other than depreciation, all selling and administrative expenses are paid for in the quarter they occur.

On June 30, 2020 the Large Box Car Division plans to buy new machinery and equipment for $1,000,000. The new machinery and equipment will be acquired at the very end of the fiscal year, so it will not be used in production and sales during the coming year and it will not be depreciated until the following year. The Large Box Car Division expects to pay 40% down in cash and finance the remaining 60% of the equipment cost with a note payable from a local bank with whom they do business with. No interest payable will accrue on the equipment note payable until after (33) June 30, 2020.

The Division must maintain a minimum cash balance of $100,000. If after accounting for cash receipts and disbursements (including dividends) in the cash budget, the budgeted cash available cash falls below $100,000 in any quarter, the Division will need to borrow cash. They have arranged a line of credit allowing it to borrow in $10,000 increments (i.e. they can borrow $10,000 or $20,000 etc. but not an odd amount). Assume borrowing will take place at the beginning of any quarter in which the available cash would otherwise be below $100,000 so that at no time during the quarter will the cash balance fall below $100,000 (after payment of interest). If there is extra cash at the end of the quarter and there is borrowing outstanding, the division should pay down principal (also in increments of $10,000). The bank charges the Division interest at the rate of 3% per quarter. Interest accrued in the quarter will be paid the first day of the next quarter (e.g. Q1s interest is not paid in cash until Q2 and Q2s 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 Tommys Box Cars, the parent company. Large Box Car Division will pay dividends of $50,000 each quarter to its corporate parent, Tommys Box Cars. The dividends must be paid, even if the Large Box Car Division has to borrow on its line of credit to make the payment

The budgeted balance sheet for the Large Box Car Division on June 30, 2020 (which is the same as the budgeted balance sheet at the beginning of business July 1, 2020) is presented below. Tommys Box Cars owns 100% of the Capital Stock of the Large Box Car Division.

LARGE Box Car Division Tommys Box Cars
BUDGETED Balance Sheet
JUNE 30, 2020
ASSETS			LIABILITIES & EQUITY
Cash	$1,450,000		Accounts Payable	$450,000
Accounts Receivable	3,900,000		Notes Payable	0
Raw Material Inventory	200,000		Capital Stock	3,500,000
Plant and Equipment	8,900,000		Retained Earnings	10,500,000
TOTAL ASSETS	$14,450,000		TOTAL LIAB. & SE	$14,450,000

Item Units Sold Selling Price Per Unit Total Budgeted Sales Revenues Sales Budget (July 1, 2020 - June 30, 2021) 01 02 03 65,000 70,000 55,000 $130 $130 $130 $ 8,450,000 $ 9,100,000 $ 7,150,000 $ 04 Year Total 60,000 250,000 $130 7,800,000 $ $ 32,500,000 $130 Q3 55,000 Production Budget Q1 Q2 65,000 70,000 21,000 16,500 86,000 86,500 21,000 86,000 65,500 Item Budgeted Sales in Units Add: Desired Ending Instory =Total Needed Less: Beginning Inventory =Units to be produced 18,000 Q4 60,000 35,000 95,000 18,000 77,000 Year Total 250,000 35,000 285,000 73,000 16,500 56,500 285,000 Item Total Production of Box Cars Wood Feet per Car Total Wood Required (feet) Add: Desired Ending Wood (feet) Total Needs (feet) Less: Beginning Inventory Total DM to be purchased (feet) Cost per foot Total cost of direct materials purchases Direct Materials Budget Q1 Q2 Q3 Q4 Year Total 86,000 65,500 56,500 77,000 285,000 5.00 5.00 5.00 5.00 5.00 430,000 327,500 282,500 385,000 1,425,000 81,875 70,625 96,250 60,000 60,000 511,875 398,125 378,750 445,000 1,485,000 81,875 70,625 96,250 60,000 511,875 316,250 308,125 348,750 1,425,000 $ 4.00 $ 4.00$ 4.00 $ 4.00 $ 4.00 $ 2,047,500 $ 1,265,000 $1,232,500 $ 1,395,000 $ 5,700,000 Item Total Production in Units X Direct Labor Hours per Unit = Total Direct Labor Hous Required X Labor Wage Rate = Total Direct Labor Costs Direct Labor Budget Q1 Q2 Q3 Q4 Year Total 86,000 65,500 56,500 77,000 285,000 5.00 5.00 5.00 5.00 5.00 430,000 327,500 282,500 385,000 1,425,000 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 4,300,000 $3,275,000 $2,825,000 $3,850,000 $14,250,000 Manufacturing Overhead Budget (MOH) Q1 Q2 Q3 Q4 Year Total Item Variable MFG Overhead: Total direct labor hours x Variable overhead rate per DL Hour = Total Variable MFG Overhead + Total Fixed Manufacturing Overhead = Total Manufacturing Overhead Less: Depreciation (non-cash expense) = Cash Disbursements for MFG Overhead 430,000 $ 4.50 $ $ 1,935,000 $ $ 120,000 $ $ 2,055,000 $ $ 40,000 $ $ 2,015,000 $ 327,500 4.50 $ 1,473,750 $ 120,000 $ 1,593,750 $ 40,000 $ 1,553,750 $ 282,500 4.50 $ 1,271,250 $ 120,000 $ 1,391,250 $ 40,000 $ 1,351,250 385,000 4.50 1,732,500 $ 120,000 $ 1,852,500 $ 40,000 $ 1,812,500 $ 1,425,000 4.50 6,412,500 480,000 6,892,500 160,000 6,732,500 Pre-Determined Overhead Rate Calculation Hint Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH $ 6,892,500 "Cost Driver" 1,425,000 = MOH to be "Applied" Per Driver Unit $ $ 4.8368 Selling and Admin Budget (S & A) Q1 Q2 Q3 Q4 Year Total 250,000 12.00 Item Variable Sales & Admin Expenses: Sales in Units X Variable Sales & Admin Rate per unit = Variable Sales & Admin Expense + Total Fixed Sales & Admin Expense = Total Sales & Admin Expenses Less: Depreciation (non-cash expense) = Cash Disbisbursements for S & A Exp's $ $ $ 65,000 12.00$ 780,000 $ 95,000 $ 875,000 $ 30,000 $ 845,000 $ 70,000 12.00$ 840,000 $ 95,000 $ 935,000 $ 30,000 $ 905,000 $ 55,000 12.00$ 660,000 $ 95,000 $ 755,000 $ 30,000 $ 725,000 $ 60,000 12.00 $ 720,000 $ 95,000$ 815,000 $ 30,000 $ 785,000 $ 3,000,000 380,000 3,380,000 120,000 3,260,000 $ $ Q3 Item Accts Rec Balance Forward First quarter sales Second quarter sales Third quarter sales Fourth quarter sales Total Cash Collections Schedule of Cash Collections (Receipts) Q1 Q2 $ 3,900,000.00 $ 2,535,000.00 $ 5,915,000.00 $ 2,730,000.00 $ $ 6,370,000.00 2,145,000.00 $ $ 8,515,000 Q4 Year Total $ 3,900,000.00 $ 8,450,000.00 $ 9,100,000.00 5,005,000.00 $ 7,150,000.00 2,340,000.00 $ 2,340,000.00 7,345,000 $ 30,940,000 $ 6,435,000 $ 8,645,000 Item Accts Payable Balance Forward First quarter Purchases Second quarter Purchases Third quarter Purchases Fourth quarter Purchases Total Cash Payments Schedule of Cash Disbursements Q1 Q2 Q3 Q4 Year Total $ 450,000.00 $ 450,000.00 $ 716,625.00 $ 1,330,875.00 $ 2,047,500.00 $ 442,750.00 $ 822,250.00 $ 1,265,000.00 $ 431,375.00 $ 801, 125.00 $ 1,232,500.00 $ 488,250.00 $ 488,250.00 ### ##### $1,773,625.00 $1,253,625.00 $1,289,375.00 $ 5,483, 250.00 Cash Budget Q2 Q1 03 Q4 Year Total 6,435,000 $ 8,645,000$ 8,515,000 $ 7,345,000 $ 30,940,000 Item Beginning Cash Balance Add: Cash Collections = Total Cash Available Less: Cash Disbursements Direct Materials Direct Labor Manufacturing Overhead Selling & Administrative Equipment Purchases Dividends Total Disbursements Excess (Deficiency) of cash available over disbursements $ 2,047,500 $ $ 43,000,000 $ $ 2,015,000 $ $ 845,000 $ 1,265,000 $ 3,275,000 $ 1,553,750 $ 905,000 $ 1,232,500 $ 2,825,000 $ 1,351,250 $ 725,000 $ 1,395,000 $ 3,850,000 $ 1,812,500 $ 785,000 $ 5,700,000 14,250,000 6,732,500 3,260,000 $ 50,000 $ 50,000 $ 50,000 $ 50,000 $ 200,000 Cash Budget (Financing & Repayment) Q1 Q2 Q3 Q4 Year Total Item Excess (deficiency) of cash available over disbursements Interest Payments Borrowing (at Beginning of QUARTER) - Repayments (at the End of QUARTER) + Equip loan (at the end of the year) = Total Financing Ending Cash Balance Loan Balance for Interest Calculation Ending Finished Goods Inventory ( at Cost) Hint Use the information / 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. Ending Finished Goods Inventory Budget Quantity Cost (rate) Total Cost Production per Box Car per boxcar per unit per unit Direct materials X Direct labor Manufacturing overhead Production Cost per Box Car Ending finished goods inventory: Box Cars Ending finished goods inventory in dollars Amount Tommy's Box Cars - Large Pro Forma Income Statement For the Fiscal Year Ended June 30, 2021 Item Sales Cost of goods sold Gross margin Selling & administrative expenses Net operating income Interest expense Net income Tommy's Box Cars - Large Pro Forma Balance Sheet As of June 30, 2021 AND June 30, 2020 Assets June 30, 2021 Cash Accounts receivable Direct materials inventory Finished Goods inventory (box cars) Plant and equipment Total assets Liabilities and Stockholders' Equity June 30, 2021 Accounts payable Notes payable - equipment Letter of Credit outstanding Capital stock Retained Earnings Total liabilities and stockholders equity June 30, 2020 $ 1,450,000 $ 3,900,000 200,000 $ $ 8,900,000 $ 14,450,000 June 30, 2020 $ 450,000 $ $ $ 3,500,000 $ 10,500,000 $ 14,450,000 ACCOUNTING EQUATION OUT OF BALANCE $0 $0 Item Units Sold Selling Price Per Unit Total Budgeted Sales Revenues Sales Budget (July 1, 2020 - June 30, 2021) 01 02 03 65,000 70,000 55,000 $130 $130 $130 $ 8,450,000 $ 9,100,000 $ 7,150,000 $ 04 Year Total 60,000 250,000 $130 7,800,000 $ $ 32,500,000 $130 Q3 55,000 Production Budget Q1 Q2 65,000 70,000 21,000 16,500 86,000 86,500 21,000 86,000 65,500 Item Budgeted Sales in Units Add: Desired Ending Instory =Total Needed Less: Beginning Inventory =Units to be produced 18,000 Q4 60,000 35,000 95,000 18,000 77,000 Year Total 250,000 35,000 285,000 73,000 16,500 56,500 285,000 Item Total Production of Box Cars Wood Feet per Car Total Wood Required (feet) Add: Desired Ending Wood (feet) Total Needs (feet) Less: Beginning Inventory Total DM to be purchased (feet) Cost per foot Total cost of direct materials purchases Direct Materials Budget Q1 Q2 Q3 Q4 Year Total 86,000 65,500 56,500 77,000 285,000 5.00 5.00 5.00 5.00 5.00 430,000 327,500 282,500 385,000 1,425,000 81,875 70,625 96,250 60,000 60,000 511,875 398,125 378,750 445,000 1,485,000 81,875 70,625 96,250 60,000 511,875 316,250 308,125 348,750 1,425,000 $ 4.00 $ 4.00$ 4.00 $ 4.00 $ 4.00 $ 2,047,500 $ 1,265,000 $1,232,500 $ 1,395,000 $ 5,700,000 Item Total Production in Units X Direct Labor Hours per Unit = Total Direct Labor Hous Required X Labor Wage Rate = Total Direct Labor Costs Direct Labor Budget Q1 Q2 Q3 Q4 Year Total 86,000 65,500 56,500 77,000 285,000 5.00 5.00 5.00 5.00 5.00 430,000 327,500 282,500 385,000 1,425,000 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 4,300,000 $3,275,000 $2,825,000 $3,850,000 $14,250,000 Manufacturing Overhead Budget (MOH) Q1 Q2 Q3 Q4 Year Total Item Variable MFG Overhead: Total direct labor hours x Variable overhead rate per DL Hour = Total Variable MFG Overhead + Total Fixed Manufacturing Overhead = Total Manufacturing Overhead Less: Depreciation (non-cash expense) = Cash Disbursements for MFG Overhead 430,000 $ 4.50 $ $ 1,935,000 $ $ 120,000 $ $ 2,055,000 $ $ 40,000 $ $ 2,015,000 $ 327,500 4.50 $ 1,473,750 $ 120,000 $ 1,593,750 $ 40,000 $ 1,553,750 $ 282,500 4.50 $ 1,271,250 $ 120,000 $ 1,391,250 $ 40,000 $ 1,351,250 385,000 4.50 1,732,500 $ 120,000 $ 1,852,500 $ 40,000 $ 1,812,500 $ 1,425,000 4.50 6,412,500 480,000 6,892,500 160,000 6,732,500 Pre-Determined Overhead Rate Calculation Hint Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH $ 6,892,500 "Cost Driver" 1,425,000 = MOH to be "Applied" Per Driver Unit $ $ 4.8368 Selling and Admin Budget (S & A) Q1 Q2 Q3 Q4 Year Total 250,000 12.00 Item Variable Sales & Admin Expenses: Sales in Units X Variable Sales & Admin Rate per unit = Variable Sales & Admin Expense + Total Fixed Sales & Admin Expense = Total Sales & Admin Expenses Less: Depreciation (non-cash expense) = Cash Disbisbursements for S & A Exp's $ $ $ 65,000 12.00$ 780,000 $ 95,000 $ 875,000 $ 30,000 $ 845,000 $ 70,000 12.00$ 840,000 $ 95,000 $ 935,000 $ 30,000 $ 905,000 $ 55,000 12.00$ 660,000 $ 95,000 $ 755,000 $ 30,000 $ 725,000 $ 60,000 12.00 $ 720,000 $ 95,000$ 815,000 $ 30,000 $ 785,000 $ 3,000,000 380,000 3,380,000 120,000 3,260,000 $ $ Q3 Item Accts Rec Balance Forward First quarter sales Second quarter sales Third quarter sales Fourth quarter sales Total Cash Collections Schedule of Cash Collections (Receipts) Q1 Q2 $ 3,900,000.00 $ 2,535,000.00 $ 5,915,000.00 $ 2,730,000.00 $ $ 6,370,000.00 2,145,000.00 $ $ 8,515,000 Q4 Year Total $ 3,900,000.00 $ 8,450,000.00 $ 9,100,000.00 5,005,000.00 $ 7,150,000.00 2,340,000.00 $ 2,340,000.00 7,345,000 $ 30,940,000 $ 6,435,000 $ 8,645,000 Item Accts Payable Balance Forward First quarter Purchases Second quarter Purchases Third quarter Purchases Fourth quarter Purchases Total Cash Payments Schedule of Cash Disbursements Q1 Q2 Q3 Q4 Year Total $ 450,000.00 $ 450,000.00 $ 716,625.00 $ 1,330,875.00 $ 2,047,500.00 $ 442,750.00 $ 822,250.00 $ 1,265,000.00 $ 431,375.00 $ 801, 125.00 $ 1,232,500.00 $ 488,250.00 $ 488,250.00 ### ##### $1,773,625.00 $1,253,625.00 $1,289,375.00 $ 5,483, 250.00 Cash Budget Q2 Q1 03 Q4 Year Total 6,435,000 $ 8,645,000$ 8,515,000 $ 7,345,000 $ 30,940,000 Item Beginning Cash Balance Add: Cash Collections = Total Cash Available Less: Cash Disbursements Direct Materials Direct Labor Manufacturing Overhead Selling & Administrative Equipment Purchases Dividends Total Disbursements Excess (Deficiency) of cash available over disbursements $ 2,047,500 $ $ 43,000,000 $ $ 2,015,000 $ $ 845,000 $ 1,265,000 $ 3,275,000 $ 1,553,750 $ 905,000 $ 1,232,500 $ 2,825,000 $ 1,351,250 $ 725,000 $ 1,395,000 $ 3,850,000 $ 1,812,500 $ 785,000 $ 5,700,000 14,250,000 6,732,500 3,260,000 $ 50,000 $ 50,000 $ 50,000 $ 50,000 $ 200,000 Cash Budget (Financing & Repayment) Q1 Q2 Q3 Q4 Year Total Item Excess (deficiency) of cash available over disbursements Interest Payments Borrowing (at Beginning of QUARTER) - Repayments (at the End of QUARTER) + Equip loan (at the end of the year) = Total Financing Ending Cash Balance Loan Balance for Interest Calculation Ending Finished Goods Inventory ( at Cost) Hint Use the information / 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. Ending Finished Goods Inventory Budget Quantity Cost (rate) Total Cost Production per Box Car per boxcar per unit per unit Direct materials X Direct labor Manufacturing overhead Production Cost per Box Car Ending finished goods inventory: Box Cars Ending finished goods inventory in dollars Amount Tommy's Box Cars - Large Pro Forma Income Statement For the Fiscal Year Ended June 30, 2021 Item Sales Cost of goods sold Gross margin Selling & administrative expenses Net operating income Interest expense Net income Tommy's Box Cars - Large Pro Forma Balance Sheet As of June 30, 2021 AND June 30, 2020 Assets June 30, 2021 Cash Accounts receivable Direct materials inventory Finished Goods inventory (box cars) Plant and equipment Total assets Liabilities and Stockholders' Equity June 30, 2021 Accounts payable Notes payable - equipment Letter of Credit outstanding Capital stock Retained Earnings Total liabilities and stockholders equity June 30, 2020 $ 1,450,000 $ 3,900,000 200,000 $ $ 8,900,000 $ 14,450,000 June 30, 2020 $ 450,000 $ $ $ 3,500,000 $ 10,500,000 $ 14,450,000 ACCOUNTING EQUATION OUT OF BALANCE $0 $0