DATE TO: Tommy's Box Cars June 1, 2020 CCSU Consulting Mark Swain, President, Tommy's Box Cars Master Budget for the fiscal year My 1, 2020 - June 30, 2021 FROM: SUBJECT: 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 June 30, 2021 for our corporate strategic planning process, and we cannot wait for Tommy's return from Kentucky. We would like you to prepare the Large Box Car Division's Master Budget for the fiscal year ended June 30, 2021. The deliverables are as follows: 1. Sales budget, including a schedule of expected cash collections. 2. Production budget 3. Direct materials budget, including a schedule of expected cash disbursements for materials 4. Direct labor budget. 5. Manufacturing overhead budget. 6 Ending finished goods Invertory budget calculating the expected value of the finished goods Inventory as of June 30, 2021. 7. Selling and administrative expense budget. 8. Cash budget 9. Budgeted income statement for the year ended June 30, 2021. 10. Budgeted balance sheet for June 30, 2021. All the Master Budget schedules except those marked with an asterisk for the Large Box Car Division should include a column for each quarter and a total column for the fiscal year. We only need annual totals for the budgeted financial statements (schedules 9 and 10) and we only need a year-end total for the value of finished goods Inventory (schedule 6). Tommy's Box Cars During 2019-20 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 Plast Second Third 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 (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 Carson hand on June 30, 2021. Quarter Second Third Eourth Ending FG inventory of Box Cars as a % of the next quarter's budgeted 304 30% 30% 7 sales 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 quarter's 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 Second Third Fourth Ending DM Inventory as a % of the next quarter's production 25% 7 requirement Ending DM Inventory in feet 7 > 60.000 The Large Box Car Division buys is wood on account. It pays for 35% of purchases of Grect materials 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 director will be paid an estimated $10.00 per labor hour. Wages and salaries are paid on the 19 and 30 of each month Variable manufacturing overhead is estimated to be $4.50 per director hour for the coming fiscal year. All variable mandacturing overhead expenses are paid for in the quarter incurred. Pored manufacturing overhead is estimated to total $120,000 each otwth 540,000 out of the total amount of $120,000 representing depreciation on machinery, equipment and the factory. All other fored manufacturing overhead expenses are paid in cash in the quarter they occur. The fed manufacturing 3 Tommy's Box Cars overhead rate will be computed by dividing the year's totale acturing overhead by the year's budgeted direct labor hours. Round the fred overhead rate to the nearest penny Variable selling and administrative expenses are estimated to be $12.00 per box car sold. Feedeling and administrative expenses are expected to total 595,000 each Quarter, with $30,000 out of the total amount of 595,000 representing depreciation on the office space furniture and coment. Other than depreciation, selling and administrative expenses are paid for in the are they 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 focal years will not be used in production and sales during the coming year and t will not be deprecated at 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 trest payable will con the equipment note payable until after (33) June 30, 2020 The Division must maintain a minimum cash balance of $100,000.11 accounting for cash receipts and disbursements including dividends) in the cash budget, the budgeted cash valable cash falls below $100,000 in any quarter, the Division will need to borrow cash. They havenged a line of credit allowing it to borrow in $10,000 increments (e. they can borrow $10,000 of $20.000 etc, but not an odd amount) Assume borrowing will take place at the beginning of any Quarter in which the able cash would otherwise be below $100,000 so that at no time during the quarter will the cash belo fail below $100.000 (alter 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 (eg. Qo's interest is not paid in cash until 2 and 2 Interest will be paid in QJ) As a fully owned subsidiary, the Large Box Car Division does not pay income the income taxes are charged to Tommy's Box Cars, the parent company. Large Box Car Division will pay dividends of $50,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 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. Tommy Box Cars owns 100% of the Capital Stock of the Large Box Car Division LARGE BOX CAR DIVISION-TONNY'S BOX CARS BUDGETED BALANCE SHEET JUNE 30, 2020 ASSETS LIABILITIES & EQUITY Cash 31,450,000 Accounts Payable $450,000 Accounts Receivable 3.900,000 Notes Payable 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 LEAR AS SILAS0,000 Units Sold Selling Price Per Unt Total Budgeted Sales Revenues Sales Budget (July 1, 2020 - June 30, 2021) 02 a 65,000 70,000 55,000 $130 $120 5130 0.0001 1.100.000 7A150.00 60,000 $130 7196.D Your Tots 250,000 $920 ADALGO Them Budgeted sales in Units Add: Desired Ending lavtory Total Needed Less: Beginning Inventory =Units to be produced Production Budget 91 02 65,000 20.000 21.000 16.500 86,000 36,500 21.000 26.000 65.50 55,000 18.000 73,000 15.500 5.500 60,000 35.000 95,000 18.000 77.00 Yan To 250,000 90.5 340,500 55.500 28 LOCO Item Total Production of Hax Cars Wood Feet per Or 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 Qi 92 86,000 65,500 5.00 5.00 430,000 327,500 81,075 20.625 511,875 398,125 50.000 31.875 461,875 316 250 4.00 $ 4.00 $ 1.147.500 5 1.265.000 $ 56,500 5.00 282.500 96.250 378,750 70,625 308,125 4.00 $ 1.712.500 04 77,000 5.00 385.000 60.000 445,000 95.250 348,750 1.00 5 1395.000 de Year Total 285,000 20.00 1.425,000 3D TED 1,733,750 298,750 1.435,000 16.00 5.740.000 Direct Labor Budget 91 92 93 04 Year Tots Item Total Production in Units X Direct Labor Hours per Unit - Total Direct Labor Hous Required X Labor Wage Rate = Total Direct Labor Costs Manufacturing Overhead Budget (MOH) 01 92 94 Year Total Item Variable MFG Overhead: Total direct labor hours x Variable overhead rate per DL Hour = Total Variable MFG Overhead + Total Fored Manufacturing Overhead = Total Manufacturing Overhead Less: Depreciation (non-cash expense) = Cash Disbursements for MFG Overhead Pre-Determined Overhead Rate Calculation Hinti Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH Cost Driver = MOH to be "Applied" Per Driver Unit Pre-Determined Overhead Rate Calculation Hint Recall from Chapter 2 how you calculated your Pre-Determined Overhead tate Total Hudgeting MOH + "Cost Driver - MOH to be "Applied Per Driver Unit Selling and Admin Budget (S&A) 91 92 Year Tot Item Variable Sales & Admin Expenses Sales In Units x Variable Sales & Admin Rate per unit Variable Sales & Admin Expense + Total Fbced Sales & Admin Expense = Total Sales & Admin Expenses Less: Depreciation (non-cash expense) =Cash Disbisbursements for S & A Exp's Schedule of Cash Collections (Receipts). 01 02 Year Total Item Accts Rec Balance Forward First quarter sales Second quarter sales Third quarter sales Fourth quarter sales Total Cash Collections Schedule of Cash Disbursements 91 02 02 04 Year Total Item Acets Payable Balance Forward First quarter Purchases Second quarter Purchases Third quarter Purchases Fourth quarter Purchases Total Cash Payments 01 Cash Budget 92 03 94 Year Total Item Beginning Cash Balance Add: Cash Collections = Total Cash Available Less: Cash Disbursements Direct Materials Direct Labor Manufacturing Overhead Selling & Administrative Equip Purchases Dividends Total Disbursements Excess (Deficiency of cash available over disbursements Year To cos (defidency) of ash available over disbursements -Interest Payments + Harrowing at Beginning of QUARTER) Repayments at the End of QUARTER) Equip loan (at the end of the year) Total handing Ending Cash Balance Loan Balance for Interest Calculation Ending ished Goods Inventory at Cart Hint: Use the formation/previously calculated above (A your PDOH) to determine the product cost of one und. Once completed, ple the unit cost by the number of unit is ender laventary Ending Finished Goods Inventory Budget Quantity Cart rate) Total cost Production per Box Car per becar perut Direct materials 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 Tlem Sales Cost of goods sold Gross margin Selling & administrative expenses Niet operating income Net Income Tommy's Box Cars - Large Pro Forma Balance Sheet As of June 30, 2021 AND June 30, 2020 Assets June 10, 2021 June 30, 2020 1.450.000 1 3.900.000 $ 200, 500 Accounts receive Direct meer levertory Finished Goods Wiertary boxca Pantandement Total assets Liables and Stockholders' Equity Accounts payable Notes payable equipment Letter of Credit outstanding Call stock Retained Earnings Total liabilities and stockholders equity June 30.2021 3 5.900.000 SIASA ODO June 10, 2020 $ 1 5 - 3.500.000 3 1,500,000 18650.000 ACCOUNTING EQUATION OUT OF BALANCE $0 $0 DATE TO: Tommy's Box Cars June 1, 2020 CCSU Consulting Mark Swain, President, Tommy's Box Cars Master Budget for the fiscal year My 1, 2020 - June 30, 2021 FROM: SUBJECT: 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 June 30, 2021 for our corporate strategic planning process, and we cannot wait for Tommy's return from Kentucky. We would like you to prepare the Large Box Car Division's Master Budget for the fiscal year ended June 30, 2021. The deliverables are as follows: 1. Sales budget, including a schedule of expected cash collections. 2. Production budget 3. Direct materials budget, including a schedule of expected cash disbursements for materials 4. Direct labor budget. 5. Manufacturing overhead budget. 6 Ending finished goods Invertory budget calculating the expected value of the finished goods Inventory as of June 30, 2021. 7. Selling and administrative expense budget. 8. Cash budget 9. Budgeted income statement for the year ended June 30, 2021. 10. Budgeted balance sheet for June 30, 2021. All the Master Budget schedules except those marked with an asterisk for the Large Box Car Division should include a column for each quarter and a total column for the fiscal year. We only need annual totals for the budgeted financial statements (schedules 9 and 10) and we only need a year-end total for the value of finished goods Inventory (schedule 6). Tommy's Box Cars During 2019-20 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 Plast Second Third 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 (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 Carson hand on June 30, 2021. Quarter Second Third Eourth Ending FG inventory of Box Cars as a % of the next quarter's budgeted 304 30% 30% 7 sales 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 quarter's 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 Second Third Fourth Ending DM Inventory as a % of the next quarter's production 25% 7 requirement Ending DM Inventory in feet 7 > 60.000 The Large Box Car Division buys is wood on account. It pays for 35% of purchases of Grect materials 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 director will be paid an estimated $10.00 per labor hour. Wages and salaries are paid on the 19 and 30 of each month Variable manufacturing overhead is estimated to be $4.50 per director hour for the coming fiscal year. All variable mandacturing overhead expenses are paid for in the quarter incurred. Pored manufacturing overhead is estimated to total $120,000 each otwth 540,000 out of the total amount of $120,000 representing depreciation on machinery, equipment and the factory. All other fored manufacturing overhead expenses are paid in cash in the quarter they occur. The fed manufacturing 3 Tommy's Box Cars overhead rate will be computed by dividing the year's totale acturing overhead by the year's budgeted direct labor hours. Round the fred overhead rate to the nearest penny Variable selling and administrative expenses are estimated to be $12.00 per box car sold. Feedeling and administrative expenses are expected to total 595,000 each Quarter, with $30,000 out of the total amount of 595,000 representing depreciation on the office space furniture and coment. Other than depreciation, selling and administrative expenses are paid for in the are they 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 focal years will not be used in production and sales during the coming year and t will not be deprecated at 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 trest payable will con the equipment note payable until after (33) June 30, 2020 The Division must maintain a minimum cash balance of $100,000.11 accounting for cash receipts and disbursements including dividends) in the cash budget, the budgeted cash valable cash falls below $100,000 in any quarter, the Division will need to borrow cash. They havenged a line of credit allowing it to borrow in $10,000 increments (e. they can borrow $10,000 of $20.000 etc, but not an odd amount) Assume borrowing will take place at the beginning of any Quarter in which the able cash would otherwise be below $100,000 so that at no time during the quarter will the cash belo fail below $100.000 (alter 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 (eg. Qo's interest is not paid in cash until 2 and 2 Interest will be paid in QJ) As a fully owned subsidiary, the Large Box Car Division does not pay income the income taxes are charged to Tommy's Box Cars, the parent company. Large Box Car Division will pay dividends of $50,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 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. Tommy Box Cars owns 100% of the Capital Stock of the Large Box Car Division LARGE BOX CAR DIVISION-TONNY'S BOX CARS BUDGETED BALANCE SHEET JUNE 30, 2020 ASSETS LIABILITIES & EQUITY Cash 31,450,000 Accounts Payable $450,000 Accounts Receivable 3.900,000 Notes Payable 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 LEAR AS SILAS0,000 Units Sold Selling Price Per Unt Total Budgeted Sales Revenues Sales Budget (July 1, 2020 - June 30, 2021) 02 a 65,000 70,000 55,000 $130 $120 5130 0.0001 1.100.000 7A150.00 60,000 $130 7196.D Your Tots 250,000 $920 ADALGO Them Budgeted sales in Units Add: Desired Ending lavtory Total Needed Less: Beginning Inventory =Units to be produced Production Budget 91 02 65,000 20.000 21.000 16.500 86,000 36,500 21.000 26.000 65.50 55,000 18.000 73,000 15.500 5.500 60,000 35.000 95,000 18.000 77.00 Yan To 250,000 90.5 340,500 55.500 28 LOCO Item Total Production of Hax Cars Wood Feet per Or 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 Qi 92 86,000 65,500 5.00 5.00 430,000 327,500 81,075 20.625 511,875 398,125 50.000 31.875 461,875 316 250 4.00 $ 4.00 $ 1.147.500 5 1.265.000 $ 56,500 5.00 282.500 96.250 378,750 70,625 308,125 4.00 $ 1.712.500 04 77,000 5.00 385.000 60.000 445,000 95.250 348,750 1.00 5 1395.000 de Year Total 285,000 20.00 1.425,000 3D TED 1,733,750 298,750 1.435,000 16.00 5.740.000 Direct Labor Budget 91 92 93 04 Year Tots Item Total Production in Units X Direct Labor Hours per Unit - Total Direct Labor Hous Required X Labor Wage Rate = Total Direct Labor Costs Manufacturing Overhead Budget (MOH) 01 92 94 Year Total Item Variable MFG Overhead: Total direct labor hours x Variable overhead rate per DL Hour = Total Variable MFG Overhead + Total Fored Manufacturing Overhead = Total Manufacturing Overhead Less: Depreciation (non-cash expense) = Cash Disbursements for MFG Overhead Pre-Determined Overhead Rate Calculation Hinti Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOH Cost Driver = MOH to be "Applied" Per Driver Unit Pre-Determined Overhead Rate Calculation Hint Recall from Chapter 2 how you calculated your Pre-Determined Overhead tate Total Hudgeting MOH + "Cost Driver - MOH to be "Applied Per Driver Unit Selling and Admin Budget (S&A) 91 92 Year Tot Item Variable Sales & Admin Expenses Sales In Units x Variable Sales & Admin Rate per unit Variable Sales & Admin Expense + Total Fbced Sales & Admin Expense = Total Sales & Admin Expenses Less: Depreciation (non-cash expense) =Cash Disbisbursements for S & A Exp's Schedule of Cash Collections (Receipts). 01 02 Year Total Item Accts Rec Balance Forward First quarter sales Second quarter sales Third quarter sales Fourth quarter sales Total Cash Collections Schedule of Cash Disbursements 91 02 02 04 Year Total Item Acets Payable Balance Forward First quarter Purchases Second quarter Purchases Third quarter Purchases Fourth quarter Purchases Total Cash Payments 01 Cash Budget 92 03 94 Year Total Item Beginning Cash Balance Add: Cash Collections = Total Cash Available Less: Cash Disbursements Direct Materials Direct Labor Manufacturing Overhead Selling & Administrative Equip Purchases Dividends Total Disbursements Excess (Deficiency of cash available over disbursements Year To cos (defidency) of ash available over disbursements -Interest Payments + Harrowing at Beginning of QUARTER) Repayments at the End of QUARTER) Equip loan (at the end of the year) Total handing Ending Cash Balance Loan Balance for Interest Calculation Ending ished Goods Inventory at Cart Hint: Use the formation/previously calculated above (A your PDOH) to determine the product cost of one und. Once completed, ple the unit cost by the number of unit is ender laventary Ending Finished Goods Inventory Budget Quantity Cart rate) Total cost Production per Box Car per becar perut Direct materials 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 Tlem Sales Cost of goods sold Gross margin Selling & administrative expenses Niet operating income Net Income Tommy's Box Cars - Large Pro Forma Balance Sheet As of June 30, 2021 AND June 30, 2020 Assets June 10, 2021 June 30, 2020 1.450.000 1 3.900.000 $ 200, 500 Accounts receive Direct meer levertory Finished Goods Wiertary boxca Pantandement Total assets Liables and Stockholders' Equity Accounts payable Notes payable equipment Letter of Credit outstanding Call stock Retained Earnings Total liabilities and stockholders equity June 30.2021 3 5.900.000 SIASA ODO June 10, 2020 $ 1 5 - 3.500.000 3 1,500,000 18650.000 ACCOUNTING EQUATION OUT OF BALANCE $0 $0