Question

Hart Venture Capital (HVC) specializes in providing venture capital for software development and Internet applications. Currently HVC has two investment opportunities: (1) Security Systems, a firm that needs additional capital to develop an Internet security software package, and (2) Market Analysis, a market research company that needs additional capital to develop a software package for conducting customer satisfaction surveys. In exchange for Security Systems stock, the firm asked HVC to provide $600,000 in year 1, $600,000 in year 2, and $250,000 in year 3 over the coming three-year period. In exchange for Market Analysis stock, the firm asked HVC to provide $500,000 in year 1, $350,000 in year 2, and $400,000 in year 3 over the same three-year period. HVC believes that both investment opportunities are worth pursuing. However, because of other investments, HVC is willing to commit at most $800,000 for both projects in the first year, at most $700,000 in the second year, and $500,000 in the third year.

HVC’s financial analysis team reviewed both projects and recommended that the company’s objective should be to maximize the net present value of the total investment in Security Systems and Market Analysis. The net present value takes into account the estimated value of the stock at the end of the three-year period as well as the capital outflows that are necessary during each of the three years. Using an 8% rate of return, HVC’s financial analysis team estimates that 100% funding of the Security Systems project has a net present value of $1,800,000, and 100% funding of the Market Analysis project has a net present value of $1,600,000.

HVC has the option to fund any percentage of the Security Systems and Market Analysis projects. For example, if HVC decides to fund 40% of the Security Systems project, investments of 0.40 ($600,000) = $240,000 would be required in year 1, 0.40 ($600,000) = $240,000 would be required in year 2, and 0.40 ($250,000) = $100,000 would be required in year 3. In this case, the net present value of the Security Systems project would be 0.40 ($1,800,000) = $720,000. The investment amounts and the net present value for partial funding of the Market Analysis project would be computed in the same manner.

Perform an analysis of HVC’s investment problem and prepare a report that presents your findings and recommendations. Be sure to include information on the following:

1. The recommended percentage of each project that HVC should fund and the net present value of the total investment
2. A capital allocation plan for Security Systems and Market Analysis for the coming three-year period and the total HVC investment each year
3. The effect, if any, on the recommended percentage of each project that HVC should fund if HVC is willing to commit an additional $100,000 during the first year
4. A capital allocation plan if an additional $100,000 is made available
5. Your recommendation as to whether HVC should commit the additional $100,000 in the first year  

Please use excel solver showing excel steps and state each step being done per number thanks

Answer 1

Step 1

Determination of PV of investment and inflows for three years using excel

formula used =NPV(rate,nper,pmt and yearly compounding)

PV of investment and PV of cash flows for 3 years
Security systems (40% investment)		Amount($)
PV of 1st year payment		240000
PV of 2nd year payment
Interest Rate	8%
Yearly payment	-240000
Periods	1
Compounding period	1
	-$2,05,759.60	205760
PV of 3rd year payment
Interest Rate	8%
Yearly payment	-100000
Periods	2
Compounding period	1
	-$85,731.24	85731
	PV of total payment	531491
	Less : 40% NPV	720000
	PV of cash Inflow	1251491
Security systems (40% investment)		Amount($)
PV of 1st year payment		200000
PV of 2nd year payment
Interest Rate	8%
Yearly payment	-140000
Periods	1
Compounding period	1
	-$1,20,025.72	120026
PV of 3rd year payment
Interest Rate	8%
Yearly payment	-160000
Periods	2
Compounding period	1
	-$1,37,171.57	137172
	PV of total payment	457198
	Less : 40% NPV	640000
	PV of cash Inflow	1097198
PV of total investment of HVC		988689
TOTAL NPV		1360000

It is observed that total NPV comes $1360000

Step 2 total investment per year

Investment of HVC per year	PV invested sum($)	Amount($)
1st year
Investment of Security system	240000
Investment of market analysis	200000	440000
2nd year
Investment of Security system	205760
Investment of market analysis	120026	325786
2nd year
Investment of Security system	85731
Investment of market analysis	137172	222903
	Total	988689

Step 3 when additional capital is introduced on 50: 50 ratio

and total NPV for such allocation

If the additional capital is effective for both projects 50:50
Investment of Security system			Amount($)
1st year			240000
Add:Additional 50% of 1 lac			50000
2nd year			205760
3rd year			85731
Total PV of investment			581491
PV of cash inflow			1251491
NPV of the project is			670000
Investment of Market analysis			Amount($)
1st year			200000
Add:Additional 50% of 1 lac			50000
2nd year			120026
3rd year			137172
Total PV of investment			507198
PV of cash inflow			1097198
NPV of the project is			590000
Total NPV			1260000

step 4 when additional capital is invested individually

If additional capital is effective individually
Investment of Security system			Amount($)
1st year			240000
Add:Additional			100000
2nd year			205760
3rd year			85731
Total PV of investment			631491
PV of cash inflow			1251491
NPV of the project is			620000
Total NPV of the project			1260000
Investment of Market analysis			Amount($)
1st year			200000
Add:Additional			100000
2nd year			120026
3rd year			137172
Total PV of investment			557198
PV of cash inflow			1097198
NPV of the project is			540000
Total NPV of the project			1260000

When additional capital is invested according to step 3 NPV comes $1260000

which is same when the additional capital is applied on individual basis

Therefore, in both cases additional capital can be invested as in both cases the result of NPV is same.

However, if additional capital is introduced it affects the total NPV. and for such introduction total NPV amount reduced