Question

1. Sway's Back Store is considering a 7-year project which will require the purchase of $2 million in new equipment. The equipment will be depreciated using MACRS method. Sway's Back Store will sell the equipment at the end of the project for a salvage value of $300,000. Annual sales from this project are estimated at $1,050,000 in year 1, and it is expected to grow by 3% each year. The variable cost is 40% of the annual sales and there is an annual fixed cost of $100,000. The store should build an inventory with a value of 10% of next year’s sales. All of the new net working capital will be recouped at the end of the project. The firm desires a minimal 12% rate of return on this project. The tax rate is 40%.  
2. 1. Construct a capital budgeting table and calculate the Free Cash Flow of each year. Hint: you need to find out capital expenditure (1 points) , Salvage Cash Flow (3 points), Change in Net working Capital (3 points), Operating Cash Flows (8 points), and then Free Cash Flow (1 point)
   2. Calculate the project NPV (3 points), IRR (2 points), Discounted Payback Period (4 points), Modified IRR (3 points)
   3. Conduct a sensitivity analysis of NPV to the % change of 1^st year sales. Hint: You will simulate at least 3 values for 1^styear sales and record the corresponding NPV for each of the simulated sales value (6 points). Analysis the sensitivity and interpret the result (4 points)
   4. Conduct a Scenario analysis of project NPV based on different level of first year sales.  

	Worst	Base	Best
Probability	20%	60%	20%
1st year sale	$850,000	$1,050,000	$1,250,000

Please calculate the NPVs of the worst and best cases (4 points) and then the mean and standard deviation of the 3 NPVs (

Answer 1

Following is the depreciation schedule:

Year	MACRS Depreciation rate	Depreciation	BV at the end of the year
1.00	14.29%	$ 2,85,800.00	$ 17,14,200.00
2.00	24.49%	$ 4,89,800.00	$ 12,24,400.00
3.00	17.49%	$ 3,49,800.00	$    8,74,600.00
4.00	12.49%	$ 2,49,800.00	$    6,24,800.00
5.00	8.93%	$ 1,78,600.00	$    4,46,200.00
6.00	8.92%	$ 1,78,400.00	$    2,67,800.00
7.00	8.93%	$ 1,78,600.00	$       89,200.00
8.00	4.46%	$     89,200.00	$                       -

Base case NPV is calculated below:

Particulars	Remark	0	1	2	3	4	5	6	7
Sales	Given		$ 10,50,000.00	$ 10,81,500.00	$ 11,13,945.00	$ 11,47,363.35	$ 11,81,784.25	$ 12,17,237.78	$ 12,53,754.91
Total VC	40% of Sales		$    4,20,000.00	$    4,32,600.00	$    4,45,578.00	$    4,58,945.34	$    4,72,713.70	$    4,86,895.11	$    5,01,501.96
Fixed cost	Given		$    1,00,000.00	$    1,00,000.00	$    1,00,000.00	$    1,00,000.00	$    1,00,000.00	$    1,00,000.00	$    1,00,000.00
EBITDA	Sales-Total VC-Fixed Cost		$    5,30,000.00	$    5,48,900.00	$    5,68,367.00	$    5,88,418.01	$    6,09,070.55	$    6,30,342.67	$    6,52,252.95
Depreciation	MACRS		$    2,85,800.00	$    4,89,800.00	$    3,49,800.00	$    2,49,800.00	$    1,78,600.00	$    1,78,400.00	$    1,78,600.00
EBT	EBITDA-Depreciation		$    2,44,200.00	$       59,100.00	$    2,18,567.00	$    3,38,618.01	$    4,30,470.55	$    4,51,942.67	$    4,73,652.95
Tax	40% x EBT		$       97,680.00	$       23,640.00	$       87,426.80	$    1,35,447.20	$    1,72,188.22	$    1,80,777.07	$    1,89,461.18
EAT	EBT-Tax		$    1,46,520.00	$       35,460.00	$    1,31,140.20	$    2,03,170.81	$    2,58,282.33	$    2,71,165.60	$    2,84,191.77
Depreciation	Added back as non cash		$    2,85,800.00	$    4,89,800.00	$    3,49,800.00	$    2,49,800.00	$    1,78,600.00	$    1,78,400.00	$    1,78,600.00
OCF	EAT+Depreciation		$    4,32,320.00	$    5,25,260.00	$    4,80,940.20	$    4,52,970.81	$    4,36,882.33	$    4,49,565.60	$    4,62,791.77
FCINV	Given	$ -20,00,000.00
Salvage Value	Given								300000
Tax on Profit from Sale	-0.40 x (Salvage value - book value)								$      -12,880.00
WCINV	Given	$    -1,05,000.00	$ -1,08,150.00	$ -1,11,394.50	$ -1,14,736.34	$ -1,18,178.43	$ -1,21,723.78	$ -1,25,375.49	$    8,04,558.53
FCF	OCF+FCINV+Salvage Value-Tax on profit from sale+WCINV	$ -21,05,000.00	$    3,24,170.00	$    4,13,865.50	$    3,66,203.87	$    3,34,792.38	$    3,15,158.55	$    3,24,190.11	$ 15,54,470.30
Discount factor Formula	at 12%	1/(1+0.12)^0	1/(1+0.12)^1	1/(1+0.12)^2	1/(1+0.12)^3	1/(1+0.12)^4	1/(1+0.12)^5	1/(1+0.12)^6	1/(1+0.12)^7
Discount factor	Calculated using above formula	1	0.892857143	0.797193878	0.711780248	0.635518078	0.567426856	0.506631121	0.452349215
DCF	FCF x Discount Factor	$ -21,05,000.00	$    2,89,437.50	$    3,29,931.04	$    2,60,656.68	$    2,12,766.61	$    1,78,829.43	$    1,64,244.80	$    7,03,163.42
NPV = sum of all DCF	$                              34,029.48

NPV is positive so we should select the project

IRR is the rate at which the NPV = 0 and can be found using the goal seek function in excel or a financial calculator:

IRR comes to 12.41% rounded to 2 decimal places:

Discounted payback period at 12% discount rate is calculated below:

• Opening balance= previous year's closing balance
• Closing balance = Opening balance + investment - DCF
• We see that the the closing balance in year 6 was $669,133.94 while the DCF in year 7 was  $703,163.42 so some time during the 7th year the entire invest was recovered and the portion of year was 669,133.94/703,163.42 = 0.95 so the payback period is 6.95 years

PV and FV are calculated below:

Sensitivity analysis:

Increasing sales of year 1 by 1%, increases NPV by 0.48%:

decreasing sales of year 1 by 1%, decreases NPV by 0.48%

Increasing sales of 1st year by 2% increases NPV by 0.97%

So for every 1% change in year 1 sales, the NPV changes in the same direction by 0.48%

Scenario analysis

Worst case NPV:

Best case NPV:

So following is the case wise NPV:

Case	Probability	NPV	Probability weighted NPV	Probability x (NPV - Mean)2
Base	60%	$       34,029.48	0.60 x34,029.48 = $   20,417.69	0.00
Worst	20%	$ -2,79,802.80	0.20 x-2,79,802.80 = $ -55,960.56	$ 196981,39,878.90
Nest	20%	$   3,47,861.75	0.20 x3,47,861.75 = $   69,572.35	$ 196981,39,135.14
		Mean = sum of Probability weighted NPV	$   34,029.48
			Variance = sum of Probability x (NPV - Mean)2	$ 393962,79,014.04
			Standard deviation = square root of variance	$             1,98,484.96