Create a spreadsheet showing the annual net income, cash flows, and net present
ID: 2794371 • Letter: C
Question
Create a spreadsheet showing the annual net income, cash flows, and net present
value of the project. Use the absorption costing method for your annual income
statements. Once you have net income computed, convert your net income to cash flow
by adding back to net income any non-cash expenses. When annual cash flows are
computed you are ready to find the present value of each of those annual cash flows.
Add your present values together and net with the initial costs/startup costs to find the
net present value.
The Ebitts Field Corporation manufactures baseball gloves. Charlie Botz, the company’s
top salesperson, has recommended expanding into the baseball bat business. He put
together a project proposal that includes the following information:
New production equipment will cost $75,000 and will be depreciated using the
straight-line method over 5 years. This equipment can be sold for $10,000 at the
end of the project (year 6).
Setting up production and establishing distribution channels will have a $300,000
startup cost. These expenses are deductible for tax purposes.
The unit sales forecast for baseball bats is as follows:
Aluminum Bats
Wooden Bats
Year 1
6000
8000
Year 2
9000
12000
Year 3
15000
14000
Year 4
18000
20000
Year 5
20000
22000
Year 6
22000
24000
The company expects manufacturing costs to be $11 per unit for aluminum bats
and $9 per unit for wood bats.
The expected gross margin is $7 per unit for aluminum bats and $3 per unit for
wood bats.
Other expenses associated with the project are estimated at $20,000 per year
during the first 2 years and $40,000 per year for the last 4 years.
The company has enough available space in their existing manufacturing facility
that has no alternative use at this time.
The company has a marginal tax rate of 35%.
Use a 12% rate of return for your present value calculations
Please show Formulas on how to solve!!!
Aluminum Bats
Wooden Bats
Year 1
6000
8000
Year 2
9000
12000
Year 3
15000
14000
Year 4
18000
20000
Year 5
20000
22000
Year 6
22000
24000
Explanation / Answer
Details New production equipment cost (NC) 75000 life(years) 6 Salvage value 10000 depreciation based on straight line method(75000-10000)/6 10833.33 Startup cost (expenses deductible) 300000 (SC)Startup cost after adjusting for Taxes @ 35% (0.35*300000) 195000 Calculation of Sales and Manufacturing Costs Year Aluminum Bats (AB) Manufacturing cost oer unit (MCA) Selling price per unit (SPA)($11+gross margin($7)) Wooden Bats (WB) Manufacturing cost oer unit(MCW) Selling price per unit (SPW) ($9+gross margin($3)) Total manfac cost per year (TMC=(AB*MCA+WB*MCW)) Total sales per year(TS= (AB*SPA+WB*SPW)) Gross profit per year(TS-TMC) Year 1 6000 11 18 8000 9 12 138000 204000 66000 Year 2 9000 11 18 12000 9 12 207000 306000 99000 Year 3 15000 11 18 14000 9 12 291000 438000 147000 Year 4 18000 11 18 20000 9 12 378000 564000 186000 Year 5 20000 11 18 22000 9 12 418000 624000 206000 Year 6 22000 11 18 24000 9 12 458000 684000 226000 Estimation of Net income Year 1 2 3 4 5 6 Revenue (total Sales) (TS) 204000 306000 438000 564000 624000 684000 Manufactring cost(MC) 138000 207000 291000 378000 418000 458000 other expenses (OE) 20000 20000 40000 40000 40000 40000 Depreciation (D) 10833.333 10833.333 10833.33 10833.33 10833.33 10833.33 Net income(PBIT) (TS-MC-OE-D) 35166.667 68166.667 96166.67 135166.7 155166.7 175166.7 Taxes (T) = (0.35*NI) 12308.333 23858.333 33658.33 47308.33 54308.33 61308.33 Net Income after taxes (NI) 22858.333 44308.333 62508.33 87858.33 100858.3 113858.3 Estimation of Cash flows Net Income (NI) 22858.333 44308.333 62508.33 87858.33 100858.3 113858.3 Depreciation (D) 10833.333 10833.333 10833.33 10833.33 10833.33 10833.33 Cash In flow (CIF) = (NI+D) 33691.667 55141.667 73341.67 98691.67 111691.7 124691.7 Present value of cash flows @ 12% PV (PVIF)factor 1 ÷ (1 + i)n 0.8928571 0.7971939 0.71178 0.635518 0.567427 0.506631 Present value of cashflows (PVCF) 30081.845 43958.599 52203.15 62720.34 63376.85 63172.68 NPV= Present value of future cash flows - cost of the Project Cost of the Project (NC+SC) 270000 Present value of future cash flows (Sum of PVCF) 315513.46 Net Present Value 45513.462
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.