Purchase Solution

Develop an NPV profile graph

Not what you're looking for?

Ask Custom Question

Can you create an NPV Profile graph based on the Excel spreadsheet analysis information provided?

** Please see the attached file for the Excel formatted problem description **

Inputs
ATSV old @ t=0 305,000 ATSV formula = Market salvage value-(Market salvage value-Book value)*tax rate
Equipment 2,000,000
Tax Credit 100,000
Depreciaton per year 300,000
Sales period 1 1,000,000 growth: g yrs 2-3 = 15% g yrs 4-6 = 5%
CoGS %of sales 45%
SG&A exp. %of sales 10%
ATSV new @ t=6 260,000

Operating Life CFs
Time 0 1 2 3 4 5 6
Sales $1,000,000 $1,150,000 $1,322,500 $1,388,625 $1,458,056 $1,530,959
- COGS 450,000 67,500 77,625 29,756 31,244 32,806
- SG&A expenses 100,000 15,000 17,250 6,613 6,943 7,290
- Depreciation 300,000 300,000 300,000 300,000 300,000 300,000
= EBIT 150,000 767,500 927,625 1,052,256 1,119,869 1,190,863
-Taxes (40%) 60,000 307,000 371,050 420,903 447,948 476,345
= Net Income 90,000 460,500 556,575 631,354 671,921 714,518
+ Depreciation 300,000 300,000 300,000 300,000 300,000 300,000
= Operating CF 390,000 760,500 856,575 931,354 971,921 1,014,518

Time 0 Investments
Equipment -2,000,000
ATSV old 305,000
Tax credit 100,000
NWC -200,000

Terminal Non-OCF:
ATSV new @ t=6 260,000
NWC 200,000
= Net Cash Flow -$1,795,000 $390,000 $760,500 $856,575 $931,354 $971,921 $1,474,518
= Cummulative CF -$1,795,000 -$1,405,000 -$644,500 $212,075 $1,143,429 $2,115,350 $3,589,868

Cost of Capital 8%
NPV $2,173,336.49
IRR = 35.0%
PBP = 2.75
PI = 2.21

Purchase this Solution

Solution Summary

This solution provides a detailed sample of the given finance problem in Excel.

Purchase this Solution


Free BrainMass Quizzes
Elementary Microeconomics

This quiz reviews the basic concept of supply and demand analysis.

Pricing Strategies

Discussion about various pricing techniques of profit-seeking firms.

Economics, Basic Concepts, Demand-Supply-Equilibrium

The quiz tests the basic concepts of demand, supply, and equilibrium in a free market.

Basics of Economics

Quiz will help you to review some basics of microeconomics and macroeconomics which are often not understood.

Economic Issues and Concepts

This quiz provides a review of the basic microeconomic concepts. Students can test their understanding of major economic issues.