Question

In: Finance

ONLY NEED PART E PLEASE SHOW ALL WORK Company X projects numbers of unit sales for...

ONLY NEED PART E

PLEASE SHOW ALL WORK

Company X projects numbers of unit sales for a new project as follows:

81,000 (year 1), 89,000 (year 2), 97,000 (year 3), 92,000 (year 4), and 77,000 (year 5).

The project will require $1,500,000 in net working capital to start (year 0) and require net working capital investments each year equal to 15% of the projected sales for subsequent years (year 1 - 5). NWC is recovered at the end of the fifth year.

Fixed costs (operating expenses) are $1,850,000 per year.

Variable costs are $190/unit, and the units are priced at $345 each.

The equipment needed to begin production has a cost of $19,500,000.

The equipment is qualified for accounting as MACRS depreciation:

Year 1: 14.29%, Year 2: 24.49%, Year 3: 17.49%, Year 4: 12.49%, Year 5: 8.93%

In 5 years, this equipment can be sold for 35% of its acquisition cost (original cost)

The company is in the 35% marginal tax bracket and has a required rate of return of 18%.

(a) Estimate FCF for each year

(b) Calculate NPV, IRR based on the estimated FCF from part a

(d) Do a sensitivity analysis using 2-input data table analyzing NPV given Selling price ($300, $320,...$400) , and VC ($160, $170, ...$210)

(e) Do a scenario analysis (optimistic and pessimistic) and report the summary results for NPV and IRR:

Optimistic case: Variable cost= $160/unit, Fixed cost= $1,600,000, selling price= $400/unit

Pessimistic case: Variable cost= $210/unit, Fixed cost= $2,050,000, selling price= $300/unit

Solutions

Expert Solution

Step 1 Summarize Given Information

Cost Of project
Equipment	$ 1,95,00,000
Net working Capital	$ 15,00,000
Add 15% of sales in next year
Salvage Cost in year 5 (35% of cost)	$ 68,25,000
Selling Price	$345
Variable Cost	$190
Fixed Cost	$ 18,50,000
Cost of Capital	18%
Tax Rate	35%

Part E:

Optimistic situation and pessimistic situation

	Original Situation	optimistic value	pessimistic value
NPV	$ 53,82,514.29	$ 2,02,51,493.50	$ (59,68,041.69)
IRR	25%	44.80%	9.76%

Part I : Optimistic Situation:

	Year	Year	Year	Year	Year
	1	2	3	4	5
Units Manufactured	81000	89000	97000	92000	77000
Contribution units * (selling price - marginal cost)	$ 1,94,40,000	$ 2,13,60,000	$ 2,32,80,000	$ 2,20,80,000	$ 1,84,80,000
Fixed Cost	$ (16,00,000)	$ (16,00,000)	$ (16,00,000)	$ (16,00,000)	$ (16,00,000)
Depreciation	$ (27,86,550)	$ (47,75,550)	$ (34,10,550)	$ (24,35,550)	$ (17,41,350)
Profit Before Tax	$ 1,50,53,450	$ 1,49,84,450	$ 1,82,69,450	$ 1,80,44,450	$ 1,51,38,650
Tax @ 35%	$ (52,68,708)	$ (52,44,558)	$ (63,94,308)	$ (63,15,558)	$ (52,98,528)
Profit After Tax	$ 97,84,743	$ 97,39,893	$ 1,18,75,143	$ 1,17,28,893	$ 98,40,123

Addback Depreciation	$ 27,86,550	$ 47,75,550	$ 34,10,550	$ 24,35,550	$ 17,41,350
Less: Working Capital Requirement (15% of next year sales)	$ (53,40,000)	$ (58,20,000)	$ (55,20,000)	$ (46,20,000)
Add: Recovery of Net working Capital					$ 2,28,00,000
Add: Salvage Value					$ 68,25,000

Free Cash Flow	$ 72,31,293	$ 86,95,443	$ 97,65,693	$ 95,44,443	$ 4,12,06,473
Discount Factor @ 18%	0.847457627	0.71818443	0.608630873	0.515788875	0.437109216
D.C.F (FCF * Disc Factor)	$ 61,28,213.98	$ 62,44,931.41	$ 59,43,701.95	$ 49,22,917.26	$ 1,80,11,728.90

Calculation of NPV and IRR

DCF year 1-5		$ 4,12,51,493.50
Cash outflow year 0		$ (2,10,00,000)
Cost of Machinery	$ (1,95,00,000)
Working Capital	$ (15,00,000)
NPV		$ 2,02,51,493.50

Using IRR function in excel. {formula = IRR(values for year 0: year6)}

	0	1	2	3	4	5
DCF	$ (2,10,00,000)	$ 72,31,292.50	$ 86,95,442.50	$ 97,65,692.50	$ 95,44,442.50	$ 4,12,06,472.50
IRR	44.80%

Part II pessimistic situation

Calculation of FCF

	Year	Year	Year	Year	Year
	1	2	3	4	5
Units Manufactured	81000	89000	97000	92000	77000
Contribution units * (selling price - marginal cost)	$ 72,90,000	$ 80,10,000	$ 87,30,000	$ 82,80,000	$ 69,30,000
Fixed Cost	$ (20,50,000)	$ (20,50,000)	$ (20,50,000)	$ (20,50,000)	$ (20,50,000)
Depreciation	$ (27,86,550)	$ (47,75,550)	$ (34,10,550)	$ (24,35,550)	$ (17,41,350)
Profit Before Tax	$ 24,53,450	$ 11,84,450	$ 32,69,450	$ 37,94,450	$ 31,38,650
Tax @ 35%	$ (8,58,708)	$ (4,14,558)	$ (11,44,308)	$ (13,28,058)	$ (10,98,528)
Profit After Tax	$ 15,94,743	$ 7,69,893	$ 21,25,143	$ 24,66,393	$ 20,40,123

Addback Depreciation	$ 27,86,550	$ 47,75,550	$ 34,10,550	$ 24,35,550	$ 17,41,350
Less: Working Capital Requirement (15% of next year sales)	$ (40,05,000)	$ (43,65,000)	$ (41,40,000)	$ (34,65,000)
Add: Recovery of Net working Capital					$ 1,74,75,000
Add: Salvage Value					$ 68,25,000

Free Cash Flow	$ 3,76,293	$ 11,80,443	$ 13,95,693	$ 14,36,943	$ 2,80,81,473
Discount Factor @ 18%	0.847457627	0.71818443	0.608630873	0.515788875	0.437109216
D.C.F (FCF * Disc Factor)	$ 3,18,891.95	$ 8,47,775.42	$ 8,49,461.54	$ 7,41,158.96	$ 1,22,74,670.44

Calculation of NPV and IRR

DCF year 1-5		$ 1,50,31,958.31
Cash outflow year 0		$ (2,10,00,000)
Cost of Machinery	$ (1,95,00,000)
Working Capital	$ (15,00,000)
NPV		$ (59,68,041.69)

Calculation of IRR {formula = IRR(values for year 0: year6)}

Years	0	1	2	3	4	5
DCF	$ (2,10,00,000)	$ 3,76,292.50	$ 11,80,442.50	$ 13,95,692.50	$ 14,36,942.50	$ 2,80,81,472.50
IRR	9.76%

Part III : Original situation

Calculation of FCF

Particulars	Year	Year	Year	Year	Year
	1	2	3	4	5
Units Manufactured	81000	89000	97000	92000	77000
Contribution units * (selling price - marginal cost)	$12,555,000	$13,795,000	$15,035,000	$14,260,000	$11,935,000
Fixed Cost	$ (18,50,000)	$ (18,50,000)	$ (18,50,000)	$ (18,50,000)	$ (18,50,000)
Depreciation	$ (27,86,550)	$ (47,75,550)	$ (34,10,550)	$ (24,35,550)	$ (17,41,350)
Profit Before Tax	$ 79,18,450	$ 71,69,450	$ 97,74,450	$ 99,74,450	$ 83,43,650
Tax @ 35%	$ (27,71,458)	$ (25,09,308)	$ (34,21,058)	$ (34,91,058)	$ (29,20,278)
Profit After Tax	$ 51,46,993	$ 46,60,143	$ 63,53,393	$ 64,83,393	$ 54,23,373

Addback Depreciation	$ 27,86,550	$ 47,75,550	$ 34,10,550	$ 24,35,550	$ 17,41,350
Less: Working Capital Requirement (15% of next year sales)	$ (46,05,750)	$ (50,19,750)	$ (47,61,000)	$ (39,84,750)
Add: Recovery of Net working Capital					$ 1,98,71,250
Add: Salvage Value					$ 68,25,000

Free Cash Flow	$ 33,27,793	$ 44,15,943	$ 50,02,943	$ 49,34,193	$ 3,38,60,973
Discount Factor @ 18%	0.847457627	0.71818443	0.608630873	0.515788875	0.437109216
D.C.F (FCF * Disc Factor)	$ 28,20,163.14	$ 31,71,461.15	$ 30,44,945.26	$ 25,45,001.60	$ 1,48,00,943.15

Calculation of NPV and IRR

DCF year 1-5		$ 2,63,82,514.29
Cash outflow year 0		$ (2,10,00,000)
Cost of Machinery	$ (1,95,00,000)
Working Capital	$ (15,00,000)
NPV		$ 53,82,514.29

Calculation of IRR {formula = IRR(values for year 0: year6)}

	0	1	2	3	4	5
DCF	$ (2,10,00,000)	$ 33,27,792.50	$ 44,15,942.50	$ 50,02,942.50	$ 49,34,192.50	$ 3,38,60,972.50
IRR	25.26%

jeff jeffy answered 1 year ago

Next > < Previous

Related Solutions

PLEASE SHOW WORK FOR ALL, IF YOU'RE ONLY GOING TO DO ONE OF THESE AND NOT...

PLEASE SHOW WORK FOR ALL, IF YOU'RE ONLY GOING TO DO ONE OF THESE AND NOT ALL, DONT ANSWER 12. P. Rose, Inc. is considering a new four-year expansion project that requires an initial fixed asset investment of $1,950,000. The asset will be depreciated straight-line to zero over its six-year tax life, after which time it will be worthless. The project is estimated to generate $2,145,000 in annual sales, with costs of $1,205,000. The fixed asset will have a market...

PLEASE SHOW WORK FOR ALL, IF YOU'RE ONLY GOING TO DO ONE OF THESE AND NOT...

PLEASE SHOW WORK FOR ALL, IF YOU'RE ONLY GOING TO DO ONE OF THESE AND NOT ALL, DONT ANSWER 1. Assume that the risk-free rate is 5 percent and that the market risk premium is 7 percent. If a stock has a required rate of return of 13.75 percent, what must its beta be? a. 1.25 b. 1.35 c. 1.37 d. 1.60 e. 1.96 2. Your family recently obtained a 30-year $100,000 fixed-rate mortgage. Which of the following statements is...

( PLEASE SHOW ALL YOUR WORK) You will need your ticker code (company abbreviation) for stock...

( PLEASE SHOW ALL YOUR WORK) You will need your ticker code (company abbreviation) for stock prices for this question. Use your ticker code to obtain the closing prices for the following two time periods to obtain two data sets: March 2, 2019 to March 16, 2019 Data set A February 16, 2019 to February 28, 2019 Data set B Take the closing prices from data set B and add 0.5 to each one of them. Treat data sets A...

PART 4 is the only part i need PLEASE PB6-3 Recording Sales and Purchases with Discounts...

PART 4 is the only part i need PLEASE PB6-3 Recording Sales and Purchases with Discounts and Returns and Analyzing Gross Profit Percentage [LO 6-4, LO 6-5] [The following information applies to the questions displayed below.] Larry’s Building Supplies (LBS) is a local hardware store. LBS uses a perpetual inventory system. The following transactions (summarized) have been selected for analysis: a. Sold merchandise for cash (cost of merchandise $321,350). $ 680,000 b. Received merchandise returned by customers as unsatisfactory...

Show all your work. Let f(x) = x 5 e x 3 (i) Use the Taylor...

Show all your work. Let f(x) = x 5 e x 3 (i) Use the Taylor series for e x around 0 to find the Taylor series for f(x) (ii) Use (i) to find f (20)(0), f (21)(0), f (22)(0), f (23)(0)

Show that a point x is an accumulation point of a set E if and only...

Show that a point x is an accumulation point of a set E if and only if for every ε > 0 there are at least two points belonging to the set E ∩ (x − ε, x + ε).

Please answer all parts of the question. Please show all work and all steps. 1a.) Show...

Please answer all parts of the question. Please show all work and all steps. 1a.) Show that the solutions of x' = arc tan (x) + t cannot have maxima 1b.) Find the value of a such that the existence and uniqueness theorem applies to the ivp x' = (3/2)((|x|)^(1/3)), x(0) = a. 1c.) Find the limits, as t approaches both positive infinity and negative infinity, of the solution Φ(t) of the ivp x' = (x+2)(1-x^4), x(0) = 0

Show that ?̅ is an unbiased estimator for ?. ( please show all work completely)

Show all your work Show all your work 3. Sales discount- allocate the following transactions into...

Show all your work Show all your work 3. Sales discount- allocate the following transactions into the correct part of the accounting equation and their correct T account a. Brown Company sold merchandise on account to Black Company $32,456 b. Black Company returned $10,121 worth of merchandise (account was credited) C. Brown Company received a check from Black Company less the returns with an 8% discount of 15/30 4. Purchase discount- allocate the following transactions into the correct part of...

-PLEASE SHOW ALL WORK- Recall that Benford's Law claims that numbers chosen from very large data...

-PLEASE SHOW ALL WORK- Recall that Benford's Law claims that numbers chosen from very large data files tend to have "1" as the first nonzero digit disproportionately often. In fact, research has shown that if you randomly draw a number from a very large data file, the probability of getting a number with "1" as the leading digit is about 0.301. Now suppose you are an auditor for a very large corporation. The revenue report involves millions of numbers in...

Subjects