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:37:23

Final Answer:

Rendered Raw Text
Did you like this answer?
Certainly! Here’s a **step-by-step guide**—with **tables, explanations, and formulas**—to project the **2024 financial statements** for Walnut Grove, based on your scenario. --- ## **Introduction** This exercise projects Walnut Grove’s 2024 financial statements (Income Statement, Balance Sheet, and Statement of Cash Flows) based on the 2023 trial balance and a set of detailed business assumptions. The goal is to integrate sales growth, new business lines, equipment purchases, staffing changes, and other operational adjustments into a comprehensive set of pro forma financials. **All calculations will be shown**. --- ## **Step 1: Gather and Format the 2023 Trial Balance** *(This would be Tab 1 in Excel—since the data is "provided", I’ll use placeholder values and account names for illustration. Replace these with your actual trial balance numbers.)* | Account | Debit ($) | Credit ($) | |-------------------------------|-----------|------------| | Cash | 120,000 | | | Accounts Receivable | 45,000 | | | Inventory | 34,000 | | | Prepaid Insurance | 13,000 | | | Property, Plant, Equipment | 184,000 | | | Accumulated Depreciation | | 42,000 | | Accounts Payable | | 33,000 | | Notes Payable | | 21,000 | | Common Stock | | 75,000 | | Retained Earnings | | 225,000 | | Material & Supplies Sales | | 330,000 | | Small Tool Sales | | 65,000 | | Tool Rental Revenue | | 40,000 | | Cost of Material Sales | 180,000 | | | Cost of Small Tool Sales | 32,500 | | | Small Tools Expense | 6,200 | | | Office Supplies & Postage | 3,800 | | | Insurance Expense | 12,500 | | | Wages Expense | 88,000 | | | Payroll Taxes & Benefits | 17,600 | | | Utilities Expense | 6,400 | | | Depreciation Expense | 15,000 | | | Interest Expense | 1,200 | | | Income Tax Expense | 25,000 | | | **Totals** | 759,700 | 759,700 | --- ## **Step 2: Project 2024 Sales** ### **a. Material & Supplies Sales** - 2023: **$330,000** - 2024 Increase: **4.2%** - **Calc:** $330,000 × 1.042 = **$343,860** ### **b. Small Tool Sales** - 2023: **$65,000** - 2024 Increase: **3.0%** - **Calc:** $65,000 × 1.03 = **$66,950** ### **c. Tool Rental Revenue** - 25 tools × $40 × 52 weeks = **$52,000** ### **d. Custom Cabinet Sales** - 180 cabinets × $5,500 = **$990,000** #### **Total Sales for 2024** | Category | 2023 Actual | 2024 Projection | |------------------------|-------------|--------------------------| | Material & Supplies | $330,000 | $343,860 | | Small Tool Sales | $65,000 | $66,950 | | Tool Rental Revenue | $40,000 | $52,000 | | Custom Cabinet Sales | - | $990,000 | | **Total** | $435,000 | **$1,452,810** | --- ## **Step 3: Project Cost of Goods Sold (COGS)** ### **a. Material & Supplies COGS** - 2023: $180,000 / $330,000 = **54.5% of sales** - 2024: $343,860 × 54.5% = **$187,804** ### **b. Small Tool COGS** - 2023: $32,500 / $65,000 = **50% of sales** - 2024: $66,950 × 50% = **$33,475** ### **c. Small Tools Expense** - Provided for 2024: **$6,625** ### **d. Custom Cabinet COGS** - **Direct Materials:** $1,000 × 180 = **$180,000** - **Direct Labor:** 6 hrs × $30 × 180 = **$32,400** - **Factory Overhead:** 35% × $32,400 = **$11,340** - **Total COGS:** $180,000 + $32,400 + $11,340 = **$223,740** | Category | 2023 Actual | 2024 Projection | |------------------------|-------------|--------------------------| | Material & Supplies | $180,000 | $187,804 | | Small Tool Sales | $32,500 | $33,475 | | Small Tools Expense | $6,200 | $6,625 | | Custom Cabinet COGS | - | $223,740 | | **Total COGS** | $218,700 | **$451,644** | --- ## **Step 4: Project Other Expenses** ### **a. Office Supplies & Postage** - 2023: $3,800 × 1.045 = **$3,971** ### **b. Insurance** - 2024 annual cost: **$27,000** - Paid 2 years in advance, 5% discount: $27,000 × 2 × 0.95 = $51,300 (total prepayment) - **Expense for 2024:** $51,300 / 2 = **$25,650** ### **c. Wages Expense** - **Existing Staff:** 2023 value: **$88,000** - **Cabinet Division Manager:** $67,000 - **3 New Employees:** $22.50/hr × 40 hrs × 52 weeks × 3 = $140,400 - **3 More Employees (20 weeks):** $20.50/hr × 35 hrs × 20 weeks × 3 = $43,050 - **Total Wages:** $88,000 + $67,000 + $140,400 + $43,050 = **$338,450** ### **d. Payroll Taxes & Benefits** - 20% of new wages (excluding 2023 base): $67,000 + $140,400 + $43,050 = $250,450 × 20% = $50,090 - 2023 base taxes: $17,600 - **Total:** $17,600 + $50,090 = **$67,690** ### **e. Utilities** - Assume unchanged unless otherwise noted: **$6,400** ### **f. Depreciation Expense** - **Building:** (use 2023 value or recalc) - **Computers/Software:** (use 2023 value or recalc) - **Furniture/Fixtures:** (use 2023 value or recalc) - **New Equipment (2024):** $137,000 / 5 = **$27,400** - **Total:** 2023 Depreciation ($15,000) - remove any old equipment depreciation + $27,400 *(Assume other assets unchanged for now, so total = $15,000 (old) + $27,400 (new) = **$42,400**)* ### **g. Interest Expense** - **New Equipment Loan:** $137,000, 5 years, 5.35% annual, monthly payment. #### **Amortization Calculation (Monthly):** - **Loan amount:** $137,000, - **Rate:** 5.35% / 12 = 0.4458% per month - **nper:** 60 months - **PMT:** - =PMT(5.35%/12, 60, -137000) ≈ $2,601.51 per month - **Total payments in 2024:** $2,601.51 × 12 = $31,218.12 - **Interest (first year):** - First month: 137,000 × 0.004458 = $610.65 - Second month: (137,000 - (2,601.51 - 610.65)) × 0.004458 - Sum all monthly interest for 12 months. - **For simplicity, let's estimate average interest:** - First year average principal: (137,000 + 137,000 - (2,601.51 × 12) + sum of principal paid) / 2 - Or use amortization schedule. For now, estimate first year interest ≈ $7,000 (exact value will be slightly less as principal is

📢 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.