Hi need help for assignment and i attached copy of my work till now.

Pawanbabbu

New Member
Joined
Sep 2, 2014
Messages
4
spread-sheet model of your business costs over a 6 year period. For the purpose of modelling, assume a discount rate of 10 per cent. Your business model should include the following analysis to ascertain the financial viability of the project:
1. Payback period
2. Net present value (NPV)
3. Internal rate of return (IRR), and
4. Profitability index
To obtain the payback period you would first need to calculate the cumulative [COLOR=#009900 !important]net cash[/COLOR] flow. Use the NPV and IRR functions provided in[COLOR=#009900 !important]Microsoft[/COLOR] Excel. You may need to search the Internet for information if you are not familiar with how to use these functions. Interpret your results.
Investment should only be in millions. The result of profit percentage should be remain between 1 to 2%.
Really need help.

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]year0[/TD]
[TD="width: 64"]Year1[/TD]
[TD="width: 64"]Year2[/TD]
[TD="width: 64"]Year3[/TD]
[TD="width: 64"]Year4[/TD]
[TD="width: 64"]Year5[/TD]
[TD="width: 64"]Year6[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]-$(10.00)[/TD]
[TD="class: xl67, align: right"]-$(5.00)[/TD]
[TD="class: xl67, align: right"]-$(2.00)[/TD]
[TD="class: xl65, align: right"]$1[/TD]
[TD="class: xl65, align: right"]$6[/TD]
[TD="class: xl65, align: right"]$11[/TD]
[TD="class: xl66, align: right"]$13.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Net[/TD]
[TD]Cashflow[/TD]
[TD="colspan: 2"]Cum cashflow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Year0[/TD]
[TD="class: xl67, align: right"]-$(10.00)[/TD]
[TD="class: xl67, align: right"]-$(10.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Year1`[/TD]
[TD="class: xl66, align: right"]-$5.00[/TD]
[TD="class: xl66, align: right"]-$5.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Year2[/TD]
[TD="class: xl66, align: right"]-$3.00[/TD]
[TD="class: xl66, align: right"]-$3.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Year3[/TD]
[TD="class: xl66, align: right"]$1.00[/TD]
[TD="class: xl66, align: right"]$1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Year4[/TD]
[TD="class: xl66, align: right"]$6.00[/TD]
[TD="class: xl66, align: right"]$6.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Year5[/TD]
[TD="class: xl66, align: right"]$11.00[/TD]
[TD="class: xl66, align: right"]$11.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Year6[/TD]
[TD="class: xl66, align: right"]$13.00[/TD]
[TD="class: xl67, align: right"]$13.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 512"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD="colspan: 3"]Discount rate(R)=10.0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]year0[/TD]
[TD]Year1[/TD]
[TD]Year2[/TD]
[TD]Year3[/TD]
[TD]Year4[/TD]
[TD]Year5[/TD]
[TD]Year6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-$(10.00)[/TD]
[TD="align: right"]-$(5.00)[/TD]
[TD="align: right"]-$(8.00)[/TD]
[TD="align: right"]$1[/TD]
[TD="align: right"]$6[/TD]
[TD="align: right"]$11[/TD]
[TD="align: right"]$13.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0.909091[/TD]
[TD="align: right"]0.826446[/TD]
[TD="align: right"]0.751315[/TD]
[TD="align: right"]0.683013[/TD]
[TD="align: right"]0.620921[/TD]
[TD="align: right"]0.564474[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-$10.00[/TD]
[TD="align: right"]-$4.55[/TD]
[TD="align: right"]-$6.61[/TD]
[TD="align: right"]$0.75[/TD]
[TD="align: right"]$4.10[/TD]
[TD="align: right"]$6.83[/TD]
[TD="align: right"]$7.34[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Your assignment is asking you to use the NPV and IRR functions, and says you may need to do some research if you are not familiar with these. Have you done this yet, and if so, what results do you have?

People will be happy to help you if you are having trouble understanding these functions, probably less inclined to do your homework for you.
 
Upvote 0
Your assignment is asking you to use the NPV and IRR functions, and says you may need to do some research if you are not familiar with these. Have you done this yet, and if so, what results do you have?

People will be happy to help you if you are having trouble understanding these functions, probably less inclined to do your homework for you.
I completed this one.
[TABLE="width: 223"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]NPV[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Discount rate [/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Initial Investment[/TD]
[TD="align: right"]-$1,000,000.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]0 Year Return[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]1nd Year[/TD]
[TD][/TD]
[TD="align: right"]$5,000.00[/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD][/TD]
[TD="align: right"]$10,000.00[/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD][/TD]
[TD="align: right"]$20,000.00[/TD]
[/TR]
[TR]
[TD]4th[/TD]
[TD][/TD]
[TD="align: right"]$25,000.00[/TD]
[/TR]
[TR]
[TD]5th[/TD]
[TD][/TD]
[TD="align: right"]$25,000.00[/TD]
[/TR]
[TR]
[TD]6th[/TD]
[TD][/TD]
[TD="align: right"]$15,000.00[/TD]
[/TR]
[TR]
[TD]NPV[/TD]
[TD][/TD]
[TD="align: right"]-$852,147.36[/TD]
[/TR]
[TR]
[TD]IRR[/TD]
[TD][/TD]
[TD]-$ 0.35[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Profablilty Index[/TD]
[TD="align: right"]$56,943.55[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
OK, you've applied NPV and IRR correctly (nearly). Your IRR of -$0.35 should be formatted as a %, i.e. -35% p.a. (which might give you a clue about how financially viable this project is looking)?

With the NPV and IRR functions, the periods between cashflow amounts need to be equal, so your table should look like this, i.e. without the extra row for time = 0:

Excel 2010
AB

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Time[/TD]
[TD="align: right"]Amount[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]-$1,000,000[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]$5,000.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]$10,000.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]$20,000.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]$25,000.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]$25,000.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]$15,000.00[/TD]

</tbody>

The NPV of your cashflows at times 1, 2 .. 6 should be calculated on B3:B8, giving $68,902 at 10% pa discount rate. Given the initial investment of $1m, the NPV for the project is $68,902 - $1m = -$931,098, i.e. your investor is looking to lose $931,098 (discounted present value) on the deal. And IRR should be calculated on B2:B8 giving -40% p.a.

Do these numbers make sense to you? Are they consistent with an investment of $1m which has returned only $100,000, before allowance for discounting?

How have you calculated your profitability index? It should be the discounted value of your cash flows in years 1 to 6, divided by the initial investment of $1m. Given you have recouped only 10% of your initial investment, before allowing for discounting, wouldn't you expect this measure to be less than 10%?
 
Upvote 0

Forum statistics

Threads
1,223,284
Messages
6,171,179
Members
452,388
Latest member
Lorenzo_Barry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top