fm007

  Hi,
My professor says all my calculations are wrong. I have included two of my classmates calculations as guiuldelines. The answers seem to be correct but YOU NEED TO ENTER IT IN EXCEL USING THE EXCEL CALCULATOR. my professor wants to see that you know how to use excel to do calculations. If you downloand these documents, you ll see that the calculation has been done with excel.
Please do your own calculations (answer should be the same as these ones listed), make sure your work does not look the same as these (use a different color or format or remove the highlighted parts, change the title— anything). Also the answers you use should help with the paper. I could not get all of the answers  ( am not sure if the answer of part 1 or 3 of the assignment is in this excel but I have included a document that my professor said might help wiht the calculations. 

http://www.ache.org/pubs/hap_c ompanion/gapenski_finance/onli ne%20appendix%20a

Another persons work
My professors response is in orange. Show original message 

Sheet1

$ – 0 $ – 0 $ – 0 $ – 0

$ 3,510 $ 3,510 $ 3,510 $ 3,510

:

Year 0

Year 2

Year 3

Year 4

Year 5

$ (420,800)

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5

Cash Flow

$ (420,800.00)

$ (420,800.00) $ 82,882.88 $ 87,545.65 $ 92,710.68 $ 96,500.14 $ 99,629.20

$ (420,800.00)

$ 38,468.55

																	Year 0		Year 1			Year 2			Year 3			Year 4			Year 5
Nurse Triage Salaries	$ 523,800	$ 549,990	$ 577,490	$ 606,364	$ 636,682	$ 668,516
Forecasted ER Cost Reductions	$ 400,000	$ 800,000	$ 848,000	$ 900,577	$ 955,512	$ 1,013,798
New IT Specialist’s Salary	$ 150,000	$ 154,500	$ 159,135	$ 163,909	$ 168,826	$ 173,891
Costs of Facility Renovations	$ 30,000					$ – 0
Necessary Capital Equipment Purchases	$ 117,000					$ 3,510
Net	Cash Flow		$ (420,800)	$ 92,000	$ 107,865	$ 126,794	$ 146,494	$ 167,881
Present Value Factor=	value previous year/(110%)
Cost of Capital = 11%	0.11
1.00
Year 1 (1-11%)	0.90
0.81
0.73
0.66
0.59
Present Values of Net Cash Flows:		$ 82,882.88		$ 87,545.65		$ 92,710.68		$ 96,500.14		$ 99,629.20	Calculated =Net cash flow * Present value factor
Net Present Value:		$ 38,468.55	Calculated = Sum of all Present values year 0-5
Internal Rate of Return (IRR):	14.21%	Measures the discount rate that makes the Net Present Value =0. IRR is used to estimate the profitability of potential investments as it assumes the cash flows from a project are reinvested at the IRR.
Modified Internal Rate of Return (MIRR):	12.96%	MIRR assumes that positive cash flows are reinvested at the firm’s cost of capital. It is considered that the MIRR more accurately reflects the cost and profitability of a project.
The IRR and MIRR measure profitability of the project. We use the 11% cost of capital that is provided. As long as the IRR and MIRR are higher, Jiranna would consider moving forward with the project as it would be profitable.
			$ (420,800.00)	$ 92,000.00	$ 107,865.00	$ 126,794.00	$ 146,494.00	$ 167,881.00
Cumulative Cash Flow(previous year cumilative+current year cash flow)	$ (328,800.00)	$ (220,935.00)	$ (94,141.00)	$ 52,353.00	$ 220,234.00	Year 3 is the last year when cumulative cash flow is negative.
Present values of net cash flow (same as column 8)
Discounted cash flow(previous year present value of net cash flow+previous year cumulative)	$ (337,917.12)	$ (250,371.46)	$ (157,660.78)	$ (61,160.65)	Year 4 is the last year when discounted cumulative cash flow is negative
Payback Period (# years):	3.64	Calculated = full years recovery (3) + (Absolute value of cumulative cash at the end of year 3 =94141)/(cash flow in following year= 146494)	The payback period calculates the time required for the initial capital invested in a project to be repaid by the net cash flow that has been generated. It is a metric used to evaluate the risk associated with a project. A shorter payback period is considered to be better as it shows the project is less risky.
Discounted Payback Period (# years):	4.61	Formula using Discounted cash flow below = full years recovery (3) + (Absolute value of cumulative cash at the end of year 3 =94141)/(cash flow in following year 146494)	Also used to evaluate the rish associated with a project but it uses the net present value of future cash flows. Due to the time value of money, the discounted payback period can be a more accurate predictor of rish than the regular payback period as it takes into account discounted cash flows.
The payback period for the centralized call center is 3.64 years and the discounted payback period is 4.21 years, which is even longer. Jiranna Healthcare has a corporate policy that it is only willing to take on a project that takes no more than 3.5 years to pay for itself. Therefore, this project would be too risky for Jiranna to move forward with.

Sheet1

$ – 0 $ – 0 $ – 0 $ – 0

$ 3,510 $ 3,510 $ 3,510 $ 3,510

:

Year 0

Year 2

Year 3

Year 4

Year 5

$ (420,800)

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5

Cash Flow

$ (420,800.00)

$ (420,800.00) $ 82,882.88 $ 87,545.65 $ 92,710.68 $ 96,500.14 $ 99,629.20

$ (420,800.00)

$ 38,468.55

																	Year 0		Year 1			Year 2			Year 3			Year 4			Year 5
Nurse Triage Salaries	$ 523,800	$ 549,990	$ 577,490	$ 606,364	$ 636,682	$ 668,516
Forecasted ER Cost Reductions	$ 400,000	$ 800,000	$ 848,000	$ 900,577	$ 955,512	$ 1,013,798
New IT Specialist’s Salary	$ 150,000	$ 154,500	$ 159,135	$ 163,909	$ 168,826	$ 173,891
Costs of Facility Renovations	$ 30,000					$ – 0
Necessary Capital Equipment Purchases	$ 117,000					$ 3,510
Net	Cash Flow		$ (420,800)	$ 92,000	$ 107,865	$ 126,794	$ 146,494	$ 167,881
Present Value Factor=	value previous year/(110%)
Cost of Capital = 11%	0.11
1.00
Year 1 (1-11%)	0.90
0.81
0.73
0.66
0.59
Present Values of Net Cash Flows:		$ 82,882.88		$ 87,545.65		$ 92,710.68		$ 96,500.14		$ 99,629.20	Calculated =Net cash flow * Present value factor
Net Present Value:		$ 38,468.55	Calculated = Sum of all Present values year 0-5
Internal Rate of Return (IRR):	14.21%	Measures the discount rate that makes the Net Present Value =0. IRR is used to estimate the profitability of potential investments as it assumes the cash flows from a project are reinvested at the IRR.
Modified Internal Rate of Return (MIRR):	12.96%	MIRR assumes that positive cash flows are reinvested at the firm’s cost of capital. It is considered that the MIRR more accurately reflects the cost and profitability of a project.
The IRR and MIRR measure profitability of the project. We use the 11% cost of capital that is provided. As long as the IRR and MIRR are higher, Jiranna would consider moving forward with the project as it would be profitable.
			$ (420,800.00)	$ 92,000.00	$ 107,865.00	$ 126,794.00	$ 146,494.00	$ 167,881.00
Cumulative Cash Flow(previous year cumilative+current year cash flow)	$ (328,800.00)	$ (220,935.00)	$ (94,141.00)	$ 52,353.00	$ 220,234.00	Year 3 is the last year when cumulative cash flow is negative.
Present values of net cash flow (same as column 8)
Discounted cash flow(previous year present value of net cash flow+previous year cumulative)	$ (337,917.12)	$ (250,371.46)	$ (157,660.78)	$ (61,160.65)	Year 4 is the last year when discounted cumulative cash flow is negative
Payback Period (# years):	3.64	Calculated = full years recovery (3) + (Absolute value of cumulative cash at the end of year 3 =94141)/(cash flow in following year= 146494)	The payback period calculates the time required for the initial capital invested in a project to be repaid by the net cash flow that has been generated. It is a metric used to evaluate the risk associated with a project. A shorter payback period is considered to be better as it shows the project is less risky.
Discounted Payback Period (# years):	4.61	Formula using Discounted cash flow below = full years recovery (3) + (Absolute value of cumulative cash at the end of year 3 =94141)/(cash flow in following year 146494)	Also used to evaluate the rish associated with a project but it uses the net present value of future cash flows. Due to the time value of money, the discounted payback period can be a more accurate predictor of rish than the regular payback period as it takes into account discounted cash flows.
The payback period for the centralized call center is 3.64 years and the discounted payback period is 4.21 years, which is even longer. Jiranna Healthcare has a corporate policy that it is only willing to take on a project that takes no more than 3.5 years to pay for itself. Therefore, this project would be too risky for Jiranna to move forward with.