Question

Conch Republic can manufacture the new smart phones for $300 each in variable costs. Fixed costs for the operation are estimated to run $4.3 million per year. The estimated sales volume is 75,000, 95,000, 125,000, 130,000, and 140,000 per year for the next five years, respectively. The unit price of the new smart phone will be $650. The necessary equipment can be purchased for $61 million and will be depreciated on a seven-year MACRS schedule. It is believed the value of the equipment in five years will be $3.4 million.
Shelley has asked you to prepare a report that answers the following questions.

Please use template below to show answer in excel.

Input:

	Year 1	Year 2	Year 3	Year 4	Year 5
Units Sales	75,000	95,000	125,000	130,000	140,000
Equipment Cost	61,000,000
Salvage value	3,400,000
Units Price	650
Variable cost (per unit)	300
Fixed costs (per year)	4,300,000
Tax rate	35%
NWC (% of sales)	15%
Required return	12%
Required Payback Period (years)	3
MACRS Schedule	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8
3-year	33.33%	44.45%	14.81%	7.41%
5- year	20.00%	32.00%	19.20%	11.52%	11.52%	5.76%
7-year	14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46% Pro Forma Income Statements Year Year 1 Year 2 Year 3 Year 4 Year 5 Revenues          48,750,000          61,750,000         81,250,000        84,500,000        91,000,000 Variable costs          22,500,000          28,500,000         37,500,000        39,000,000        42,000,000 Fixed costs             4,300,000             4,300,000            4,300,000           4,300,000           4,300,000 Depreciation             8,716,900          14,938,900         10,668,900           7,618,900           5,447,300 EBIT          13,233,100          14,011,100         28,781,100        33,581,100        39,252,700 Taxes (35%)             4,631,585             4,903,885         10,073,385        11,753,385        13,738,445 Net income             8,601,515             9,107,215         18,707,715        21,827,715        25,514,255 OCF       17,318,415       24,046,115      29,376,615     29,446,615     30,961,555 Net Working Capital Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Initial NWC                              -   Ending NWC NWC cash flow Salvage Value Market value of salvage Book value of salvage Taxes on sale: Aftertax salvage value: Project Cash Flows Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 OCF Change in NWC Capital spending Total cash flow Cumulative cash flow Question 1 Value Decision Payback Period Question 2 NPV Question 3 IRR Question 4 Profitability Index Question 5 Target Sales Price

Answer 1

Statement showing depreciation

Year	Opening balance	Depreciation rates	Depreciation	Closing balance
1	61000000	14.29%	8716900	52283100
2	52283100	24.49%	14938900	37344200
3	37344200	17.49%	10668900	26675300
4	26675300	12.49%	7618900	19056400
5	19056400	8.93%	5447300	13609100
6	13609100	8.92%	5441200	8167900
7	8167900	8.93%	5447300	2720600
8	2720600	4.46%	2720600	0

Statement showing WC requirement

Particulars	0	1	2	3	4	5
SPPU		650	650	650	650	650
No of units		75000	95000	125000	130000	140000
Sales		48750000	61750000	81250000	84500000	91000000
WC 15% of sales		7312500	9262500	12187500	12675000	13650000
Increase in WC		7312500	1950000	2925000	487500	975000
Decrease in cash		-7312500	-1950000	-2925000	-487500	-975000
Recovered at end of year 5						13650000
Cash flow		-7312500	-1950000	-2925000	-487500	12675000

Statement showing cash inflow from sale of asset

Particulars	Amount
Salvage value	3400000
BV	13609100
Loss	10209100
Tax Savings @ 35%	3573185
Total cash inflow	6973185

Statement showing NPV

Particulars	0	1	2	3	4	5	Total
Cost of machine	-61000000
SPPU		650	650	650	650	650
VCPU		300	300	300	300	300
CPU		350	350	350	350	350
No of units		75000	95000	125000	130000	140000
Contribution		26250000	33250000	43750000	45500000	49000000
Fixed cost		4300000	4300000	4300000	4300000	4300000
Depreciation		8716900	14938900	10668900	7618900	5447300
PBT		13233100	14011100	28781100	33581100	39252700
Tax @ 35%		4631585	4903885	10073385	11753385	13738445
PAT		8601515	9107215	18707715	21827715	25514255
Add: depreciation		8716900	14938900	10668900	7618900	5447300
Annual cash flow		17318415	24046115	29376615	29446615	30961555
WC requirement		-7312500	-1950000	-2925000	-487500	12675000
Salvage value						6973185
Total cash flow	-61000000	10005915	22096115	26451615	28959115	50609740
PVIF @ 12%	1	0.893	0.797	0.712	0.636	0.567
Present value	-61000000	8933852.679	17614887.6	18827737.1	18404041.12	28717325.64	31497844

PI = PV of cash inflow/PV of cash outflow

=138122500/61000000

=2.264

IRR is the rate at which NPV is 0

At 26.96% NPV comes to 0, hence IRR =26.96%

Particulars	0	1	2	3	4	5	Total
Cost of machine	-61000000
SPPU		650	650	650	650	650
VCPU		300	300	300	300	300
CPU		350	350	350	350	350
No of units		75000	95000	125000	130000	140000
Contribution		26250000	33250000	43750000	45500000	49000000
Fixed cost		4300000	4300000	4300000	4300000	4300000
Depreciation		8716900	14938900	10668900	7618900	5447300
PBT		13233100	14011100	28781100	33581100	39252700
Tax @ 35%		4631585	4903885	10073385	11753385	13738445
PAT		8601515	9107215	18707715	21827715	25514255
Add: depreciation		8716900	14938900	10668900	7618900	5447300
Annual cash flow		17318415	24046115	29376615	29446615	30961555
WC requirement		-7312500	-1950000	-2925000	-487500	12675000
Salvage value						6973185
Total cash flow	-61000000	10005915	22096115	26451615	28959115	50609740
PVIF @ 26.962349%	1	0.787635081	0.62036902	0.4886244	0.384857721	0.303127442
Present value	-61000000	7881009.668	13707745.22	12924904.6	11145139.01	15341201.05	0

Payback period

Year	Cash flow	Cummulative cash flow
1	17318415	17318415
2	24046115	41364530
3	29376615	70741145
4	29446615	100187760
5	30961555	131149315

Year	Cummulative cash flow
2	41364530
3	70741145
1	29376615
?	19635470

Thus 19635470/29376615

=0.6684

Thus payback period = 2+0.6684

=2.6684 years