VIPSolutions

VIPSolutions logo ✨ VIPSolutions

Home Dashboard Ask AI Archive

Using the 2 0 2 3 trial balance and additional information below, prepare the projected ( 2 0 2 4 )  financial statements for Walnut Grove. The prior year data ( provided ) is the starting point for your projections, and then each of the assumptions listed below will also be used. Prepare an Excel workbook which contains the following information: • Tab 1 : 2 0 2 3 Trial Balance ( provided in this document ) • Tab 2 : 2 0 2 3 Projected Income Statement • Tab 3 : 2 0 2 3 Projected Balance Sheet • Tab 4 : 2 0 2 3 Projected Statement of Cash Flows Assumptions: 1 . Sales will change as follows: a . Material & Supplies Sales will increase 4 . 2 %  b . Small Tool Sales will increase 3 . 0 %  c . Tool Rental Revenue will continue throughout the 2 0 2 4 year. An average of 2 5  tools will be rented each week. The weekly rental per tool is $ 4 0 . Assume that the average number of tools given will be rented for all 5 2 weeks. 2 . Cost of sales for materials and supplies and small tools will increase proportionately based on their current percentage of sales, respectively. ( HINT: You will need to use vertical analysis. ) 3 . Small tools, including blades and other items, is expected to total $ 6 , 6 2 5 in 2 0 2 4 .  4 . Office supplies and postage are expected to increase by 4 . 5 % during 2 0 2 4 .  5 . On January 1 st , the company will invest $ 1 3 7 , 0 0 in new equipment for its custom cabinet division. a . This equipment will have a 5 - year life and should be depreciated using the straight - line method. This purchase represents the only expected change to property, plant, and equipment. b . The company will finance the equipment purchased with a 5 - year note at 5 . 3 5 %  interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid monthly. 6 . In relation to # 6 above, the custom cabinet sales division begins operations in 2 0 2 4 . The following assumptions must be used to project the impact on the financial statements. ( Hint: You may need to add accounts to the trial balance. ) a . Walnut Grove anticipates that it will sell 1 8 0 cabinets at an average selling price of $ 5 , 5 0 0 . b . Direct materials per cabinet are $ 1 , 0 0 0 per unit. c . The direct labor per cabinet is 6 hours, and Walnut Grove pays $ 3 0 . 0 0 / hour for this labor. d . Factory overhead is calculated at 3 5 % of direct labor. 7 . The building is being depreciated over a 3 9 - year life, the computers and software are being depreciated over a 3 - year life, and the furniture and fixtures are being depreciated over a 5 - year life, all using straight - line depreciation. 8 . Because of the new cabinet division, annual insurance costs will increase to $ 2 7 , 0 0 0 ,  effective January 1 . The company prepaid 2 years of this insurance and received a 5 . 0 %  discount for the 2 - year prepayment. 9 . On January 1 , a new cabinet division manager will be hired at a cost of $ 6 7 , 0 0 0 . In additional to the new cabinet division manager, 3 new employees will be hired at an average wage of $ 2 2 . 5 0 per hour, employees work an average of 4 0 hours per week. Payroll taxes and benefits should be calculated at 2 0 % of wages. 1 0 . With 2 0 weeks remaining in the year, 3 additional employees will be hired at a rate of $ 2 0 . 5 0 per hour, based on an average of 3 5 hours per week. 1 1 . The income tax rate is 2 5 % . 1 2 . At the end of the year, Walnut Grove will have $ 3 9 , 7 5 0 in ending inventory. 1 3 . In relation # 1 3 , purchases are made evenly throughout the year and are paid in full in the month following purchase. 1 4 . Sales are collected in full the month following the sale. During the month of December, invoiced sales totaled $ 1 5 0 , 0 0 0 . 1 5 . The sales tax rate is 5 . 3 % . 1 6 . At the end of the year, Walnut Grove has received full payment for 2 0 custom cabinet orders that will be completed in January 2 0 2 4 . 0 - - - - - - - - - - - - - show all calculations ( how you get each # for the 2 0 2 4 trial balance ) . make sure to prepare a multistep income statement and use the indirect method for the statement of cash flows ) provide intro, step by step solution using tables along with conclusion

Question:

Using the 2 0 2 3 trial balance and additional information below, prepare the projected ( 2 0 2 4 )  financial statements for Walnut Grove. The prior year data ( provided ) is the starting point for your projections, and then each of the assumptions listed below will also be used. Prepare an Excel workbook which contains the following information: • Tab 1 : 2 0 2 3 Trial Balance ( provided in this document ) • Tab 2 : 2 0 2 3 Projected Income Statement • Tab 3 : 2 0 2 3 Projected Balance Sheet • Tab 4 : 2 0 2 3 Projected Statement of Cash Flows Assumptions: 1 . Sales will change as follows: a . Material & Supplies Sales will increase 4 . 2 %  b . Small Tool Sales will increase 3 . 0 %  c . Tool Rental Revenue will continue throughout the 2 0 2 4 year. An average of 2 5  tools will be rented each week. The weekly rental per tool is $ 4 0 . Assume that the average number of tools given will be rented for all 5 2 weeks. 2 . Cost of sales for materials and supplies and small tools will increase proportionately based on their current percentage of sales, respectively. ( HINT: You will need to use vertical analysis. ) 3 . Small tools, including blades and other items, is expected to total $ 6 , 6 2 5 in 2 0 2 4 .  4 . Office supplies and postage are expected to increase by 4 . 5 % during 2 0 2 4 .  5 . On January 1 st , the company will invest $ 1 3 7 , 0 0 in new equipment for its custom cabinet division. a . This equipment will have a 5 - year life and should be depreciated using the straight - line method. This purchase represents the only expected change to property, plant, and equipment. b . The company will finance the equipment purchased with a 5 - year note at 5 . 3 5 %  interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid monthly. 6 . In relation to # 6 above, the custom cabinet sales division begins operations in 2 0 2 4 . The following assumptions must be used to project the impact on the financial statements. ( Hint: You may need to add accounts to the trial balance. ) a . Walnut Grove anticipates that it will sell 1 8 0 cabinets at an average selling price of $ 5 , 5 0 0 . b . Direct materials per cabinet are $ 1 , 0 0 0 per unit. c . The direct labor per cabinet is 6 hours, and Walnut Grove pays $ 3 0 . 0 0 / hour for this labor. d . Factory overhead is calculated at 3 5 % of direct labor. 7 . The building is being depreciated over a 3 9 - year life, the computers and software are being depreciated over a 3 - year life, and the furniture and fixtures are being depreciated over a 5 - year life, all using straight - line depreciation. 8 . Because of the new cabinet division, annual insurance costs will increase to $ 2 7 , 0 0 0 ,  effective January 1 . The company prepaid 2 years of this insurance and received a 5 . 0 %  discount for the 2 - year prepayment. 9 . On January 1 , a new cabinet division manager will be hired at a cost of $ 6 7 , 0 0 0 . In additional to the new cabinet division manager, 3 new employees will be hired at an average wage of $ 2 2 . 5 0 per hour, employees work an average of 4 0 hours per week. Payroll taxes and benefits should be calculated at 2 0 % of wages. 1 0 . With 2 0 weeks remaining in the year, 3 additional employees will be hired at a rate of $ 2 0 . 5 0 per hour, based on an average of 3 5 hours per week. 1 1 . The income tax rate is 2 5 % . 1 2 . At the end of the year, Walnut Grove will have $ 3 9 , 7 5 0 in ending inventory. 1 3 . In relation # 1 3 , purchases are made evenly throughout the year and are paid in full in the month following purchase. 1 4 . Sales are collected in full the month following the sale. During the month of December, invoiced sales totaled $ 1 5 0 , 0 0 0 . 1 5 . The sales tax rate is 5 . 3 % . 1 6 . At the end of the year, Walnut Grove has received full payment for 2 0 custom cabinet orders that will be completed in January 2 0 2 4 . 0 - - - - - - - - - - - - - show all calculations ( how you get each # for the 2 0 2 4 trial balance ) . make sure to prepare a multistep income statement and use the indirect method for the statement of cash flows ) provide intro, step by step solution using tables along with conclusion

This is AI generated Response.
Asked by: Test User 100098
Created at: 2025-04-28 09:35:22

Final Answer:

Rendered Raw Text
Did you like this answer?
Absolutely! Below is a **comprehensive, step-by-step guide** (with sample tables) on how to **project Walnut Grove’s 2024 financial statements** using the provided 2023 trial balance and the assumptions. You’ll find: - **Introduction** - **Step-by-step solution** for each assumption, with sample calculations and tables - **Structure for the Excel Workbook** - **Conclusion** Let’s begin! --- # **Introduction** Walnut Grove seeks to project its 2024 financial statements using its 2023 trial balance and several business assumptions. This involves updating revenues, costs, and expenses, accounting for new equipment, staff, a new product line, and various other changes. The goal: **Prepare the following (for 2024):** - Projected Income Statement (multistep) - Projected Balance Sheet - Projected Statement of Cash Flows (Indirect Method) - Show all calculations (reconciling to a projected trial balance) --- # **Step-by-Step Solution** ## **Step 1: Enter the 2023 Trial Balance** *Copy the provided 2023 trial balance into Excel, Tab 1.* | Account | Debit | Credit | |------------------------------|---------|---------| | Cash | $XX,XXX | | | Accounts Receivable | $XX,XXX | | | Inventory | $XX,XXX | | | Prepaid Insurance | $X,XXX | | | Property, Plant, Equipment | $XX,XXX | | | Accumulated Depreciation | | $X,XXX | | Accounts Payable | | $XX,XXX | | Notes Payable | | $XX,XXX | | Capital | | $XX,XXX | | Retained Earnings | | $XX,XXX | | Sales: Materials/Supplies | | $XX,XXX | | Sales: Small Tools | | $XX,XXX | | Tool Rental Revenue | | $X,XXX | | Cost of Sales: Mat/Supplies | $XX,XXX | | | Cost of Sales: Small Tools | $XX,XXX | | | Small Tools Expense | $X,XXX | | | Office Supplies/Postage | $X,XXX | | | Insurance Expense | $X,XXX | | | Salaries/Wages Expense | $XX,XXX | | | Payroll Taxes/Benefits | $X,XXX | | | Depreciation Expense | $X,XXX | | | Utilities Expense | $X,XXX | | | Interest Expense | $X,XXX | | | Income Tax Expense | $X,XXX | | *(Fill in with actual 2023 numbers.)* --- ## **Step 2: Project Revenues for 2024** ### **a) Materials & Supplies Sales:** Increase by 4.2%. | 2023 Sales | Increase % | 2024 Projected Sales | |------------|------------|---------------------| | $X | 4.2% | = $X × 1.042 | ### **b) Small Tool Sales:** Increase by 3.0%. | 2023 Sales | Increase % | 2024 Projected Sales | |------------|------------|---------------------| | $Y | 3.0% | = $Y × 1.03 | ### **c) Tool Rental Revenue:** 25 tools × $40/tool/week × 52 weeks | # Tools | Rate/Tool/Week | Weeks | 2024 Projected Revenue | |---------|----------------|-------|-----------------------| | 25 | $40 | 52 | =25 × $40 × 52 = $52,000 | ### **d) Custom Cabinet Sales:** 180 cabinets × $5,500 | Units | Price/Unit | 2024 Projected Sales | |-------|------------|---------------------| | 180 | $5,500 | 180 × $5,500 = $990,000 | --- ## **Step 3: Project Cost of Sales** ### **a) Materials & Supplies – Cost:** First, find 2023 cost as % of sales: | 2023 Cost | 2023 Sales | % of Sales | |-----------|------------|------------| | $A | $X | = $A/$X | Then, apply this % to 2024 projected sales: | 2024 Projected Sales | Cost % | 2024 Projected Cost | |----------------------|--------|--------------------| | $X × 1.042 | Z% | = (2024 Sales) × (Z%) | ### **b) Small Tools – Cost:** Same steps as above. | 2023 Cost | 2023 Sales | % of Sales | |-----------|------------|------------| | $B | $Y | = $B/$Y | Apply to 2024 projected sales. ### **c) Custom Cabinets – Cost:** - **Direct Materials:** 180 cabinets × $1,000 = $180,000 - **Direct Labor:** 180 × 6 hrs × $30/hr = $32,400 - **Factory Overhead:** 35% × Direct Labor = 35% × $32,400 = $11,340 | | Direct Materials | Direct Labor | Factory Overhead | Total Cost | |---|------------------|--------------|------------------|------------| | | $180,000 | $32,400 | $11,340 | $223,740 | --- ## **Step 4: Project Operating Expenses** ### **Small Tools Expense:** Given as $6,625 for 2024. ### **Office Supplies and Postage:** Increase by 4.5%. | 2023 Expense | Increase % | 2024 Expense | |--------------|------------|--------------| | $C | 4.5% | = $C × 1.045 | ### **Insurance Expense:** - Annual insurance increases to $27,000, prepaid for 2 years with 5% discount. - Amount paid = $27,000 × 2 × 95% = $51,300 - 2024 expense = $51,300 / 2 = $25,650 ### **Depreciation Expense:** **a) New Equipment:** $137,000 / 5 = $27,400/year **b) Existing assets:** Use straight-line depreciation based on original cost, useful life, and accumulated depreciation. (Use 2023 data to calculate 2024 depreciation; see Assumption 7.) ### **Salaries, Wages, Payroll Taxes & Benefits:** #### **a) New Cabinet Division Manager:** - Salary = $67,000 #### **b) 3 New Full-Time Employees (all year):** - Wage = $22.50/hr × 40 hrs/week × 52 weeks = $46,800/employee - For 3 employees = $46,800 × 3 = $140,400 #### **c) Payroll Taxes & Benefits:** - 20% of wages for new employees: ($67,000 + $140,400) × 20% = $41,480 #### **d) 3 Additional Employees (20 weeks only):** - $20.50/hr × 35 hrs/week × 20 weeks = $14,350/employee - 3 employees = $43,050 - Payroll taxes/benefits: $43,050 × 20% = $8,610 #### **e) Add to existing salaries/wages and payroll taxes/benefits from 2023.** --- ## **Step 5: Interest Expense for New Loan** - Amount borrowed: $137,000 - Term: 5 years, 5.35%, monthly payments **Monthly payment calculation:** Monthly interest rate = 5.35% / 12 = 0.4458% n = 60 months Monthly payment = $137,000 × [r(1+r)^n] / [(1+r)^n – 1] Use Excel’s PMT function: `=PMT(5.35%/12, 60, -137,000)` - Calculate total interest for the year (sum of interest in 12 payments). --- ## **Step 6: Income Tax Expense** - 25% of pre-tax income --- ## **Step 7: Ending Inventory** - Ending inventory for 2024 is $39,750 --- ## **Step 8: Accounts Receivable & Payable** - Sales collected in full the month after sale; December sales = $150,000 → A/R at year-end = $150,000 - Purchases paid in the month after purchase; calculate purchases for December (see COGS and inventory). --- ## **Step 9: Deferred Revenue** - Full payment received for 20 cabinets not yet delivered = 20 × $5,500 = $110,000 (record as deferred revenue at year end). --- ## **Step 10: Sales Tax Payable** - Sales tax on all applicable sales: 5.3% - Exclude rental income if non-taxable; calculate on materials, small tools, cabinets. --- ## **Step 11

📢 Instructions:

  • Ask anything relatd to above question only.
  • For new Question goto :
    Ask AI

Related Answers

Study Help, Fast Answers

VIPSolutions focuses on clear, step‑by‑step explanations so you can learn quickly. Whether you need an AI‑generated walkthrough or a short hint to get unstuck, each solution is organized for fast reading and easy review later.

Search similar questions, compare approaches, and bookmark the best answers for revision. Our goal is simple: quick, reliable study help that feels natural—not noisy.