i need help doing this assignment, can you please help. Thank you!

DATE: TO: Tommy's Box Cars June 1, 2020 CCSU Consulting Mark Swain, President, Tommy's Box Cars Master Budget for the fiscal year July 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, induding a schedule of expected cash disbursements for materials. 4. Direct labor budget. 5. Manufacturing overhead budget. 6. Ending finished goods inventory budget calculating the expected value of the finished goods inventory as of 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). The hard copies of these budget schedules should be delivered by the company deadline. You can print more than one schedule per page, but do not have a page break in the middle of a budget schedule. I like to be able to view an entire budget schedule without fipping 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 cel spreadsheet that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Upload the Excel spreadsheet on Blackboard. We need that spreadsheet file the night before the meeting. I've attached a brief description of the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your results. Sincerely, kes Mark Swain During 2020-21 fiscal year, the average selling price for large box cars is expected to be Karl Mark Swain 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 o 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 (l.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 quarter's 30% 30% 30% 7 budgeted sales Ending FG Inventory of Box Cars ? 7 ? 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 quartiers, 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 First Second Third Fourth Ending DM Inventory as a % of the next quarter's 25% 25% 25% ? production requirement Ending DM inventory in feet 2 7 760,000 The Large Box Car Division buy 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 hout Wages and salaries are paid on the 15" and 30 of each month Variable manufacturing overhead is estimated to be $4.50 per direct labor hour for the coming fiscal yeat. Al variable manufacturing overhead expenses are paid for in the Quarter incurred. 120 TULCESTITUTIILGIUCg och Testy 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 $12.00 per box car sold. Fixed seling and administrative expenses are expected to total $95,000 each quarter, with $30,000 out of the total amount of $95,000 representing depredation on the office space, furniture and equipment. Other than depreciation, all seling 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 (ie. 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. 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 $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 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. Tommy's Box Cars owns 100% of the Capital Stock of the Large Box Car Division LARGE BOX CAR DIVISION - TOMMY'S BOX CARS BUDGETED BALANCE SHEET JUNE 30, 2020 ASSETS Cash Accounts Receivable Raw Material Inventory Plant and Equipment LIABILITIES & EQUITY $1,450,000 Accounts Payable $450,000 3.900,000 Notes Payable 0 200,000 Capital Stock 3,500,000 8.900.000 Retained Earnings 10.500.000 TOTAL ASSETS $14,450.00 o TOTAL LIAB. & SE $14,450,00 o 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 foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The foxed manufacturing overhead rate will be computed by dividing the year's total fixed manufacturing overhead by the year's budgeted direct labor hours. Round the forced overhead rate to the nearest peniny. Variable selling and administrative expenses are estimated to be $12.00 per box car sold. Fixed seling 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 seling 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 wil 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 (induding 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 (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 fail 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. Qi's Interest is not paid in cash until Q2 and Q2's Interest will be paid in 23). 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 $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 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. Tommy's Box Cars owns 100% of the Capital Stock of the Large Box Car Division LARGE BOX CAR DIVISION - TOMMY'S BOX CARS BUDGETED BALANCE SHEET JUNE 30, 2020 ASSETS Cash Accounts Receivable Raw Material Inventory Plant and Equipment LIABILITIES & EQUITY $1,450,000 Accounts Payable $450,000 3.900,000 Notes Payable 0 200.000 Capital Stock 3.500.000 5.900.000 Retained Earnings 10.500.000 TOTAL ASSETS 514.450.00 o TOTAL LIAB. & SE $14,450,00 Item 2 3 Units Sold 4 Selling Price Per Unit 5 Total Budgeted Sales Revenues B D E Sales Budget (July 1, 2020 - June 30, 2021) 01 Q2 Q3 04 Year Total 65,000 70,000 55,000 60,000 250,000 $130 $130 $130 $130 $130 $3,450,000 9,100,000 $7,150,000 $2,800,000 $32,500,000 2 Production Budget Q1 Q2 65,000 70,000 21,000 16,500 23 55,000 18,000 24 60,000 Year Total 250,000 Direct Materials Budget Q1 Q2 Q3 04 Year Total Item Budgeted Sales in Units 10 Add: Desired Ending Invtory 11 Total Needed 12 Less: Beginning Inventory 13 Units to be produced 24 15 16 Item 27 Total Production of Box Cars 18 Wood Feet per Car 19 Total Wood Required (feet) 20 Add: Desired Ending Wood (feet) 21 Total Needs (feet) 22 Less: Beginning Inventory 23 Total DM to be purchased (feet) 24 Cost per foot 25 Total cost of direct materials purchases 26 27 Item 29 Total Production in Units 30 x Direct Labor Hours per Unit 31 - Total Direct Labor Hous Required 32 X Labor Wage Rate 33 - Total Direct Labor Costs 34 35 36 Item 37 Variable MPG Overhead: 38 Total direct labor hours 39 Variable overhead rate per DL Hour 40 - Tot Variable MFG Overhead 41 Total Reed Manufacturing Overhead 42 - Tot Manufacturing Overhead 4 Last Depreciation (non-cash expense) - Cash Disbursements for MIG Overhead Direct Labor Budget 01 Q2 Q3 04 Year Total Manufacturing Overhead Budget (MOH) Q1 92 Q3 Year Total Pre-Determined Overhead Rate Calculation E A B C D Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOM > + "Cost Driver 1 - MOH to be "Applied" Per Driver Unit 52 53 54 Selling and Admin Budget (S & A) SS Item Q1 Q2 Q3 56 Variable Sales & Admin Expenses: 57 Sales in Units 58 X Variable Sales & Admin Rate per unit 59 Variable Sales & Admin Expense 60 + Total Fixed Sales & Admin Expense 51 = Total Sales & Admin Expenses 62 Less: Depreciation (non-cash expense) Cash Disbisbursements for S & A Exp's Year Total Schedule of Cash Collections (Receipts) Q1 Q2 Q3 Year Total 65 66 67 Item 68 Accts Rec Balance Forward 69 First quarter sales 70 Second quarter sales 72 Third quarter sales 72 Fourth quarter sales 73 Total Cash Collections Schedule of Cash Disbursements Q1 Q2 Year Total 75 75 Item 7 Acets Payable Balance Forward 78 First quarter Purchases 79 Second quarter Purchases 10 Third quarter Purchases 81 Fourth quarter Purchases Total Cash Payments 83 34 35 Itam 86 Beginning Cash Balance Add: Cash Collections - Total Cash Available 89 Lessi Cash Disbursements Direct Manis Director SOLUTION Q1 Cash Budget 92 04 Year Total Ready 31 Fourth quarter Purchase 32 Total Cash Payments Och Budget 92 91 Year Total 34 35 35 Beginning Cash Balance 37 Add: Cuth Collections 33 - Totalth Available 39 Les Oshbursements 30 Direct Materials 31 Direct Labor 92 Manufacturing Overhead 33 Selling a destrative 94 Equipment Purchases 95 Dividende 95 Total Disbursements Bress (Dendency of cash available over disbursements 99 Cash Badget (Finanding & Repayment) 100 Item 91 92 23 Year Total Excess (deficiency) of cash available over 101 disbursements 102 - Interest Payments 103 + Borrowing (at Beginning of QUARTER) 104 - Repayments (at the End of QUARTER) 105 Equip loan (at the end of the year) 106 Total Financing 107 Ending Cash Balance 100 109 Loan Balance for Interest Calculation $ $ $ 110 Ending Finished Goods Inventory (at Cost) Hint: Use the wormation previously calculated above (Le your PDOH) to determine the product cost of one unit. Once completed, multiple the unit 112 cost by the number of unin ending Inwentary 113 Ending Fred Goods Inventory Badget Quan Cost(mate) Total Cast 114 Production per box Car per boxca per 115 Direct materiais 115 Director X 117 Mandaluring Overhead 119 Eng fed goods inventory: Box Cars 120 Enged goods inventory in dollars 121 122 123 Tommy's Box Cars - Large Pro Forma Income Statement 126 For the local Year Ended June 30, 2021 Amount SOLUTION + Ready E 122 123 124 Tommy's Box Cars - Large 125 Pro Forma Income Statement 126 For the Fiscal Year Ended June 30, 2021 127 Item Amount 128 Sales 129 Cost of goods sold 130 Gross margin 131 Selling & administrative expenses 132 Net operating income 133 Interest expense 134 Net Income 135 136 Tommy's Box Cars - Large 137 Pro Forma Balance Sheet 138 As of June 30, 2021 AND June 30, 2020 139 Assets June 30, 2021 June 30, 2020 140 Cash $ 141 Accounts receivable 1,450,000 3,900,000 142 Direct materials inventory $ 200,000 143 Finished Goods Inventory (bax cars) $ 144 plant and equipment $ 8.900.000 145 Total assets $ 14,450,000 146 Liabilities and Stockholders' Equity June 30, 2021 June 30, 2020 147 Accounts payable $ 450,000 148 Notes payable - equipment $ 149 Letter of Credit outstanding $ 150 Capital stock $ 3,500,000 151 Retained Earnings $ 10,500,000 152 Total liabilities and stockholders equity 153 $ 14,450,000 154 ACCOUNTING EQUATION OUT OF BALANCE $0 $0 155 156 157 158 159 160 161 162 DATE: TO: Tommy's Box Cars June 1, 2020 CCSU Consulting Mark Swain, President, Tommy's Box Cars Master Budget for the fiscal year July 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, induding a schedule of expected cash disbursements for materials. 4. Direct labor budget. 5. Manufacturing overhead budget. 6. Ending finished goods inventory budget calculating the expected value of the finished goods inventory as of 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). The hard copies of these budget schedules should be delivered by the company deadline. You can print more than one schedule per page, but do not have a page break in the middle of a budget schedule. I like to be able to view an entire budget schedule without fipping 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 cel spreadsheet that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Upload the Excel spreadsheet on Blackboard. We need that spreadsheet file the night before the meeting. I've attached a brief description of the Large Box Car Division to the budget data Tommy gave me before he left for Kentucky. We eagerly await your results. Sincerely, kes Mark Swain During 2020-21 fiscal year, the average selling price for large box cars is expected to be Karl Mark Swain 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 o 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 (l.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 quarter's 30% 30% 30% 7 budgeted sales Ending FG Inventory of Box Cars ? 7 ? 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 quartiers, 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 First Second Third Fourth Ending DM Inventory as a % of the next quarter's 25% 25% 25% ? production requirement Ending DM inventory in feet 2 7 760,000 The Large Box Car Division buy 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 hout Wages and salaries are paid on the 15" and 30 of each month Variable manufacturing overhead is estimated to be $4.50 per direct labor hour for the coming fiscal yeat. Al variable manufacturing overhead expenses are paid for in the Quarter incurred. 120 TULCESTITUTIILGIUCg och Testy 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 $12.00 per box car sold. Fixed seling and administrative expenses are expected to total $95,000 each quarter, with $30,000 out of the total amount of $95,000 representing depredation on the office space, furniture and equipment. Other than depreciation, all seling 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 (ie. 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. 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 $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 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. Tommy's Box Cars owns 100% of the Capital Stock of the Large Box Car Division LARGE BOX CAR DIVISION - TOMMY'S BOX CARS BUDGETED BALANCE SHEET JUNE 30, 2020 ASSETS Cash Accounts Receivable Raw Material Inventory Plant and Equipment LIABILITIES & EQUITY $1,450,000 Accounts Payable $450,000 3.900,000 Notes Payable 0 200,000 Capital Stock 3,500,000 8.900.000 Retained Earnings 10.500.000 TOTAL ASSETS $14,450.00 o TOTAL LIAB. & SE $14,450,00 o 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 foxed manufacturing overhead expenses are paid in cash in the quarter they occur. The foxed manufacturing overhead rate will be computed by dividing the year's total fixed manufacturing overhead by the year's budgeted direct labor hours. Round the forced overhead rate to the nearest peniny. Variable selling and administrative expenses are estimated to be $12.00 per box car sold. Fixed seling 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 seling 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 wil 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 (induding 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 (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 fail 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. Qi's Interest is not paid in cash until Q2 and Q2's Interest will be paid in 23). 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 $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 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. Tommy's Box Cars owns 100% of the Capital Stock of the Large Box Car Division LARGE BOX CAR DIVISION - TOMMY'S BOX CARS BUDGETED BALANCE SHEET JUNE 30, 2020 ASSETS Cash Accounts Receivable Raw Material Inventory Plant and Equipment LIABILITIES & EQUITY $1,450,000 Accounts Payable $450,000 3.900,000 Notes Payable 0 200.000 Capital Stock 3.500.000 5.900.000 Retained Earnings 10.500.000 TOTAL ASSETS 514.450.00 o TOTAL LIAB. & SE $14,450,00 Item 2 3 Units Sold 4 Selling Price Per Unit 5 Total Budgeted Sales Revenues B D E Sales Budget (July 1, 2020 - June 30, 2021) 01 Q2 Q3 04 Year Total 65,000 70,000 55,000 60,000 250,000 $130 $130 $130 $130 $130 $3,450,000 9,100,000 $7,150,000 $2,800,000 $32,500,000 2 Production Budget Q1 Q2 65,000 70,000 21,000 16,500 23 55,000 18,000 24 60,000 Year Total 250,000 Direct Materials Budget Q1 Q2 Q3 04 Year Total Item Budgeted Sales in Units 10 Add: Desired Ending Invtory 11 Total Needed 12 Less: Beginning Inventory 13 Units to be produced 24 15 16 Item 27 Total Production of Box Cars 18 Wood Feet per Car 19 Total Wood Required (feet) 20 Add: Desired Ending Wood (feet) 21 Total Needs (feet) 22 Less: Beginning Inventory 23 Total DM to be purchased (feet) 24 Cost per foot 25 Total cost of direct materials purchases 26 27 Item 29 Total Production in Units 30 x Direct Labor Hours per Unit 31 - Total Direct Labor Hous Required 32 X Labor Wage Rate 33 - Total Direct Labor Costs 34 35 36 Item 37 Variable MPG Overhead: 38 Total direct labor hours 39 Variable overhead rate per DL Hour 40 - Tot Variable MFG Overhead 41 Total Reed Manufacturing Overhead 42 - Tot Manufacturing Overhead 4 Last Depreciation (non-cash expense) - Cash Disbursements for MIG Overhead Direct Labor Budget 01 Q2 Q3 04 Year Total Manufacturing Overhead Budget (MOH) Q1 92 Q3 Year Total Pre-Determined Overhead Rate Calculation E A B C D Pre-Determined Overhead Rate Calculation Hint: Recall from Chapter 2 how you calculated your Pre-Determined Overhead Rate Total Budgeting MOM > + "Cost Driver 1 - MOH to be "Applied" Per Driver Unit 52 53 54 Selling and Admin Budget (S & A) SS Item Q1 Q2 Q3 56 Variable Sales & Admin Expenses: 57 Sales in Units 58 X Variable Sales & Admin Rate per unit 59 Variable Sales & Admin Expense 60 + Total Fixed Sales & Admin Expense 51 = Total Sales & Admin Expenses 62 Less: Depreciation (non-cash expense) Cash Disbisbursements for S & A Exp's Year Total Schedule of Cash Collections (Receipts) Q1 Q2 Q3 Year Total 65 66 67 Item 68 Accts Rec Balance Forward 69 First quarter sales 70 Second quarter sales 72 Third quarter sales 72 Fourth quarter sales 73 Total Cash Collections Schedule of Cash Disbursements Q1 Q2 Year Total 75 75 Item 7 Acets Payable Balance Forward 78 First quarter Purchases 79 Second quarter Purchases 10 Third quarter Purchases 81 Fourth quarter Purchases Total Cash Payments 83 34 35 Itam 86 Beginning Cash Balance Add: Cash Collections - Total Cash Available 89 Lessi Cash Disbursements Direct Manis Director SOLUTION Q1 Cash Budget 92 04 Year Total Ready 31 Fourth quarter Purchase 32 Total Cash Payments Och Budget 92 91 Year Total 34 35 35 Beginning Cash Balance 37 Add: Cuth Collections 33 - Totalth Available 39 Les Oshbursements 30 Direct Materials 31 Direct Labor 92 Manufacturing Overhead 33 Selling a destrative 94 Equipment Purchases 95 Dividende 95 Total Disbursements Bress (Dendency of cash available over disbursements 99 Cash Badget (Finanding & Repayment) 100 Item 91 92 23 Year Total Excess (deficiency) of cash available over 101 disbursements 102 - Interest Payments 103 + Borrowing (at Beginning of QUARTER) 104 - Repayments (at the End of QUARTER) 105 Equip loan (at the end of the year) 106 Total Financing 107 Ending Cash Balance 100 109 Loan Balance for Interest Calculation $ $ $ 110 Ending Finished Goods Inventory (at Cost) Hint: Use the wormation previously calculated above (Le your PDOH) to determine the product cost of one unit. Once completed, multiple the unit 112 cost by the number of unin ending Inwentary 113 Ending Fred Goods Inventory Badget Quan Cost(mate) Total Cast 114 Production per box Car per boxca per 115 Direct materiais 115 Director X 117 Mandaluring Overhead 119 Eng fed goods inventory: Box Cars 120 Enged goods inventory in dollars 121 122 123 Tommy's Box Cars - Large Pro Forma Income Statement 126 For the local Year Ended June 30, 2021 Amount SOLUTION + Ready E 122 123 124 Tommy's Box Cars - Large 125 Pro Forma Income Statement 126 For the Fiscal Year Ended June 30, 2021 127 Item Amount 128 Sales 129 Cost of goods sold 130 Gross margin 131 Selling & administrative expenses 132 Net operating income 133 Interest expense 134 Net Income 135 136 Tommy's Box Cars - Large 137 Pro Forma Balance Sheet 138 As of June 30, 2021 AND June 30, 2020 139 Assets June 30, 2021 June 30, 2020 140 Cash $ 141 Accounts receivable 1,450,000 3,900,000 142 Direct materials inventory $ 200,000 143 Finished Goods Inventory (bax cars) $ 144 plant and equipment $ 8.900.000 145 Total assets $ 14,450,000 146 Liabilities and Stockholders' Equity June 30, 2021 June 30, 2020 147 Accounts payable $ 450,000 148 Notes payable - equipment $ 149 Letter of Credit outstanding $ 150 Capital stock $ 3,500,000 151 Retained Earnings $ 10,500,000 152 Total liabilities and stockholders equity 153 $ 14,450,000 154 ACCOUNTING EQUATION OUT OF BALANCE $0 $0 155 156 157 158 159 160 161 162