solution template

AutoSave OFF AA 20 A... Tommy's Box Cars Budgeting Project (1) Review View Tell me Home Insert Draw Design Layout References Mailings e Share Comments X v -- LN 2. ABCDE dobe AaBbCeDdEe daBCN Tahoma 10 . P A Aav A Biuab xx' ADA Paste Book Title Emphasis Heading 1 Intense Emp.. Styles Pane Dictate Sensitivity Our controler, 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, 2022 for our corporate strategic planning process, and we cannot wait for Tammy's neturn from Kentucky. We would like you to prepare the Large Box Car Division's Master Bucget for the fiscal year ended June 30, 2022. The deliverables are as follows: 1. Sales budget, Induding a schedule of expected cash collections. 2. Production budget 3. Drect materials budget, including a schedule of expected cash disbursements for materials. 4. Direct labur budget. 5. Manufacturing overtread budget. 6. Ending finished goods inventory budget calculating the expected value of the finished goods Inventory as of June 30, 2022. 7. Seling and administrative expense bucget. 8. Cash bucket 9. Budgeted income statement for the year ended June 30, 2022. 10. Budgeted balance sheet for June 30, 2022. * All the Master Budget schedules except those marked with an asterisk for the Large Bax Car Don should include a column for each quarter and a total column for the fiscal year. We only need annuel totals for the budgeted financial statements (schedules 9 and 10) and we only need a year-end total for the value of fristed goods inventory ischedule 6). You can print more than one schedule per page, but do not have a pege break in the middle of a budget schedule. I like to be able to view an entire budget schedule without flipping back and forth between pages. Please also use a type font or between 10-12 points for printing I've attached a brief description of the Large Box Car Dision to the budget data Tommy gave me before he left for Kentucky. We cagerly anat your results. Attached on Black Board you'll see a solution template to assist you in completing the project. a . Srcerely Mana Mark Swan 1 Tommy's Box Cars Page 1 of 3 1443 words English (United States) Focus 90% AutoSave OFF AA 20 A... Tommy's Box Cars Budgeting Project (1) Review View Tell me Home Insert Draw Design Layout References Mailings e Share Comments X Tahoma v 10 . P A Aav LN 21 ABC.NJE dobe AaBbCeDdEe daBCN Paste Biuab xx' ADA = = OH Book Title Emphasis Heading 1 Intense Emp.. Styles Pane Dictate Sensitivity During 2021 2022 fiscal year, the average selling price for large box cars is expected to be $120 per car. The Large Box Car Division forecasts the following units of sales. Quarter First Second Third Fourth Box Car UNIT Sales 65,000 67,000 64,000 70,000 The collection pattern for Accounts Receivable is as follows: 40 percent of all sales are colected within the quarter in which they are sold so percent of al sales are collected in the following quarter . There are no bad debts/unicolectible accounts. Due to high demand last year, the Large Box Car Division expects to have zero finished box cars in inventory on July 1, 2021, the beginning of the first cuarter of the new fiscal year (ie. 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 25% of the budgeted sales for the next quarter. They would like to have 15,000 finished Boxe Cars on hand on June 30, 2022. + Quarter First Second Third Fourth Ending FG inventory of Box Cars as a Yo of the next quarter's budgeted 25% % 25% 25% % sales Ending FG Inventory of Box Cars 7 7 7 15,000 Each large box car requires an average of 6.0 feet of wood. The Large Box Car Division buys wood for $3.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, 2021 (50,000 ($3.00 = $150,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 heve ther direct materials inventory quantity to equal 25 percent of the amount required for the following quarter's planned production. On June 30, 2022, 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. Carter First Second Third Fourth Ending DM inventory as a % of the next quarter's production 25% 25% 25% ? requirement Ending DM inventory in feet 7 ? ? ? 60,000 The Large Box Car Division buys its wood on account. It pays for 25% of its purchases of direct materials in the cuarter in which they were purchased and 75% in the quarter after they were purchased. Each large box car requires 4 hours of direct labor. Employees engaged in direct laborwil be paid an estimated $13.00 per labor hour. Waoes and salaries are paid on the 15 and 30+ of each month. Variable manufacturing overhead is estimated to be $4.00 per direct labor hour for the comrg fscal year. Al variable manufacturing overhead experses are paid for in the curter incurred. Axad manufacturing overhead is estimated to total $110,000 each quarter, with $40,000 out of the total amount of $110,000 representing depreciation on machinery, equipment and the factory. Al other fixed manufacturing overhead experses are peid in cesh in the quarter they occur. The fixed manufacturing Page 2 of 3 1443 words LEX English (United States) Focus 90% AutoSave OFF AA 20 A... Tommy's Box Cars Budgeting Project (1) Review View Tell me Home Insert Draw Design Layout References Mailings e Share Comments X Tahoma v 10 . AP Aav LN 2. ARCAJE dobe AaBb CcDd E dobe Paste Biuab xx' ADWAY = = Book Title Emphasis Heading Intense Emp.. Styles Pane Dictate Sensitivity Tommy's Box Cars overhead rate will be computed by dividing the year's total twed manufacturing overtread by the year's budgeted direct labor hours. Round the fined overhead rate to the nearest penny. Variable selling and administrative expenses are estimated to be $15.00 per box car sold. Fixed selling and administrative expenses are expected to total $90,000 each quarter, with $40,000 out of the total amount of $90,000 representing depreciation on the office space, fumiture and equipment. Other than depredation, ali selling and administrative expenses are paid for in the quarter they ocor. On June 30, 2022, the last day of the quarter, the Large Box Car Division plans to buy new machinery and equipment for $500,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 wil not be depredated until the following year. The Large Box Car Division cxpects to pay 30% down In cash and finance the remaining 70% of the equipment cost with a note payable from a local bank with whom they do business with. No nterest payable wil accrue on the equipment rice payable until after June 30, 2022 The Division must maintain a minimum cash balance of $100,000. Ir after accounting for cash receipts and disbursements (including dividends) in the cash budget, the budgeted cash available cash fals below $100,000 in any quarter, the Division will need to borrow cash. They have arranged a line of credt alowing it to boto in $10,000 Increments Le they can borrow $10,000 or $20,000 etc. but not an ccc amount). Aesume 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 wil the cash belarice fell 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 cay of the next quarter (6.9. gi's interest is not paid in cash until 2 and Q2's Interest will be paid in 03). As a ruly owned subsidiary, the Large Box Car Division does not pay income taxes. All income taxes are changed to Tommy's Bore Car's, the parent company, Large Box Car DMsion will pay dividends of $50,000 each quarter to its corporate perent, Tommy's Box Car's. The didends must be paid, even 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, 2021 (which is the same as the budgeted balance sheet at the beginning of business July 1, 2021) is presented below. Tommy's Box Cars Ons 100% of the Captal Stock of the Lage Box Car DMsion LARGE BOX CAR DIVISION - TOMMY'S BOX CARS BUDGETED BALANCE SHEET JUNE 30, 2021 ASSETS Cash Accounts Receivable Raw Material Inventory Plant and Equipment $1,850,000 2,900,000 150,000 9,600,000 LIABILITIES & EQUITY Accounts Payable $800,000 Notes Payable 0 Captal Stock 3,500,000 Retained Eamings 10,200,000 TOTAL ASSETS $14.500.000 TOTAL LIAR, SE $14.500.000 Page 3 of 3 1443 words LEX English (United States) Focus 90% AutoSave OFF AAC2... Q Tommy's Box Cars Budgeting Project - Template (1) View Tell me Home Insert Draw Page Layout Formulas Data Review e Share O Comments Yes No 1 J L M N o P D S 1 U V w X Y Item Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? A3 4 x V fax Units Sold 4 F JK Sales Budget (July 1, 2021 - June 30, 2027) 01 02 03 04 Year Total 3 Us Unile Sud 4 Sell PricePerUnk 5 Total Budghed Sales Revues G 7 Production Budget 8 Dem 01 92 03 04 Year Total 9 Budgeted sales in Units 1 AM Dinding Intary 11 - Total de 12 Less: Dyrer Beantery 13 URLs to be produced Direct Materiais Budget Q1 92 Qa1 Year Total 15 16 Dam Total Production of Box Cars 1s woolret per Car 19 Total Wood Required (fort) 20 Adet Deird Edgwat 31 Total Neels (et) 22 Less: Deyir bartery 23 Total DM to be purchased) 24 Cast perfect 25 Total cost of direct materials purchases Direct Labor Budget 21 Rem 01 03 02 03 94 Year Total 29 Total Production in Units 30 X Direct Laber Hors per Unt 11 Total Direct Labor Hour Required 12 X Labor Wage Rate 3 - Toul Direct Luber Coats 34 35 Manufacturing Overhead Budget (MOH) 16 Ram 01 02 03 04 Year Total 92 1 Variable MIG Overheed: 36 Tou direct labur own 29 Varble weiterate per DL Hur 10 = Total Variatie MF6 Overhead 11 Tetail Fland Manufacturing Overhead 42 - Total Manufacturing Overhead 45 Less: Dureciation mancash - Cash Disbursements for Msowerhood 45 16 Pre-Determined Overhead Rate Calculation 1 Hint: Recall from Chapter 2 ww you calculated your Pre Determined Overhead late 49 Total Buleting MOM SD + "Cost Driver 51 - Monitor Applied Per Driver Unit 52 53 54 Selling and Admin Budget(S & A) SOLUTION + + 47 Ready 70% AutoSave OFF Aar2O ... nro Tommy's Box Cars Budgeting Project - Template (1) Q Home Insert Draw Page Layout Formulas Data Review View Tell me e Share O Comments Yes No L M N o P S 1 U V w X Y Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? A3 x fx Units Sold 3 J 54 Seling and Admin Budgets & A) 55 Item 01 021 94 Year Total 56 Variable Sales Admin Expenses: 57 Sales in Units 5 ut SEX Verb Sr Admin Pate per il 59 - Various Anni Bapuce 50 + Tocal de Admin Euperise 1 = Total Sales Admin penes LARE: Depreciation (non-cathespasta) : - Cash Disbursements for SAAB 54 65 G Schedule of Cach Collections (Recipes) ET Ban Ram 01 q2 ga 91 LY Yar Total E Acta Rec Balance forward to in quarter we 70 Second quarter sales 71 Third quarter sales 73 Fourth quarter 33 Total Cash Colutions Schedule of Cash Disbursements 01 02 m. 03 01 Year Total 75 16 Item 77 Acts Payabi Balance Fanard 7 Pirat qurtur Puch 29 Bocorrer Purchase 80 Third quarter Purchases 1 Fourth quarter purchases 12 Total Cash Payments 03 94 Year Total 84 Cash Budget 25 Item 01 02 56 Reginning Cash Raluce 1 Adet Cash Collections B - Total Cash Available 19 Lesus Cunh Disturen 90 Direct Materials 91 Direct Later 93 Manufacturing Overhead 9 Sing Administrative 54 Equipment Purch 34 95 Dividend 96 Total Disbursements - Enten (Older of cashewilk over disturb 98 99 Cash Budget (Francing & Repayment) 100 Dam QUTQ 01 Exce(dec) of share the ver y 10 sumarts 102 - Internat Payment 103 + Burrowing a Begini CUARTER 104 - Repayments at the End of QUARTER) 105 + Foulaan at the end of the year) 100 - Total Pricing SOLUTION + ga 04 Year Total Ready IB 70% Q e Share O Comments AutoSave OFF Aar2O ... nro Tommy's Box Cars Budgeting Project - Template (1) Home Insert Draw Page Layout Formulas Data Review View Tell me Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? A3 A V fx Units Sold t Yes No 1 J L M N F STU V w Y 3 F 96 90 Cash Budget (Frencing loyment) 100 Bem Q1 Q2 T ga04 91 Year Total Es ( he) of whilable over 10 diebunan 102 - Interat Payment 103 + Burrowing a Begini CUARTER 104 Repayments at the QUARTER) 105 + Foulaan at the end of the year) 100 Total Pricing 107 Ending Cash Balance 103 109 Leon Balanga for recrest Calculation Is 13 13 110 111 Ending Fhished Garretary () Hint: Use the information presiously calculated abouw [..your PDOH) to determine the product cast of one unit. Once completed, multiple the 112 int cost by the number of units Inesdag inwentary , 113 Ending Finished Goods Intory Audget Devet Carote) Total Cont 114 Production per les Cw per a perunt per unit 115 Drudar als 116 Dredlo 112 Naring overhead 119 en tal edition Cod per le 119 Ending finished grada Inwentary: Des Cws 120 Erding finished goods inventary indolers 12:1 122 1231 124 Tommy's CNS-Large 125 Pre Forma Incana temat 126 For the scal Year Ended Jun 29, 2002 127 Den Anicunt 129 Sales 129 Cost of goods "La contro 1:30 BTW 13: Seling 132 B Nelsperating in 129 lrerentegere 13 Net ne 1325 135 Tommy Box Cars Large 137 Pro Forma Balance 138 As of June 30, 2022 AND June 30, 2021 129 Assets une 2012 June 302 140 ch 1,1501109 14: Agora $ 250000 142) Direiranda 150 000 22 14 Fished Goodiretor coor) $ 1:51 Pattandet 2 HELOCOT 145 Total 122 145 Lebilt und Stockholdera Equity Jurn 14, 2012 12 PI June 22, 142 Avant le 5 500 000 14 Nozes par le couper $ 129 Cher of creditordre 150 Capack $ SOS SOLUTION + $ $ Ready E! IB IL 70% AutoSave OFF AAG 2... Q Tommy's Box Cars Budgeting Project - Template (1) View Tell me Home Insert Draw Page Layout Formulas Data Review e Share O Comments Yes No A3 Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? x fx Units Sold 3 E F L M N P S T U V w X Y (. wy Total Cost 105 - Total Prencing 102 Erding Cash Balance 103 109 Leon Balance for Derest Calculation - 15 110 Ending Fished Gets trentary ( Cost) Hint: Use the formations pratourly calculated aboum (.e. your PDOW) to dobermine the product cast of one unit. Once completed, mulle the 112 int cost by the number of units Inendegimentary 113 Erding Finished Goods Twentory Rudget Costrut) 111 Production per Box CH per perunt per unit 115 Directories Drudar 116 Dredla 117 Naturing overhead 118 Prediction cost per en 119 Ending finished god inventary Box Cars 120 Erding finished goods inventory in dolara 12: 122 al 120 Tommy's Box CWS - Large 125 Pro Forma Incana tenant 126 For the scal Year Ended uw 20022 122 Dem Amount 12 Sales 129 Cost of cross we 130 G 13: Salg & ud murder 132 Nelsperalogie 129 reresteren 14 Netheme 13 135 Tommy Cars - Lary 132 Pre Fonna Balance 12 As of June 30, 2022 AND June 30, 2021 12 Assets June 1242 June 2021 140CAth 5 1,950 OG 14: Access $ 2900 142) Orion 5 150 000 14 Fished Goodiretor coor) $ 13 Pattend equipment HOTO 145 Total 12 145 Liabila med Stockholders Equity 222 June 30,2972 June 21.2001 142 Artes 500 000 ja es que 149 Letter of Crestaurang 5 150 Capadock 5 3,500 15: Bari 5 10,200 OG 152 Total abilities and luckholders equiky 14200 000 154 ACCOUNTING EQUATION OUT OF BALANCE so a $0 155 156 152 158 Isa 150 SOLUTION + DE Ready 7070