Reference no: EM131931080

Detailed instructions:

1. Create an Excel workbook with the following tabs: development time, costs for in-house development, benefits of in-house development, summary (in-house), payback period (inhouse), comparison and recommendation.

2. Create a development time spreadsheet

a) The expected value of the work time required for a project is calculated as a weighted average of the optimistic (A), most likely (M) and pessimistic (B). For example, expected value Ei is defined as:

E_i = (A+4M+B)/6

b) The development time spreadsheet should contain the following data (note you need to format the table and results to make it more professional looking):

Now that you had the estimated work time (Ei) and also the 90% probability range, you can now compute for the Expected time needed in the development. Here is an explanation on how you compute for the expected time:

- Person days/year = assuming 200 workdays available per year

Computation	Expected time
Expected time = Ei/200 =  1800/200	9 person years
90% Prob. Range =           (1484/200 ... (2116/200)	7.4  person year     ..  10.6 person years

3. Next workbook contains all the costs for in-house development. Your spreadsheet should look like this (note that students are required to input their own cost data except for the project team salary; the project team salary will be based on the total cost from the project team rate spreadsheet. The value for the project team salary starts on the third year and depreciates by 15% per year.

Fiscal Year

Cost Items		2016			2017	2018	2019	2020
Hardware		$300,000			$250,000	$250,000
Software						$200,000	$150,000	$150,000
Project Team Salary						$300,000	$250,000	$250,000
Telecommunications						$400,000	$450,000	$500,000
Training						$150,000	$150,000	$150,000
Operations and Contingencies		$400,000			$400,000	$400,000
Project Total Costs By Year		$700,000			$650,000	$1,700,000	$1,000,000	$1,050,000
PROJECT TOTAL COST	$5,100,000

- You need to enter data for the cost of each item. The values shown above are just examples.
- The first fiscal year is entered - rest of the fiscal year is computed by adding a year (for example, if year 2016 is entered in the first fiscal year, the rest of the four years will automatically be computer by adding a year from the previous year).
- Project total costs by year is the sum of all cost items per year (you need to use the formula to compute for this).
- Project total cost is the total money you need to spend on your project.
- You need to format your tables (you can design it the way you want, just make it look more professional). Make sure that appropriate formats are used (e.g. date format for dates, percentage formats or money formats)

4. Next workbook contains the benefits of in-house development:

Fiscal Year

Benefit Sources	2016			2017	2018	2019	2020
Cost Reduction (courier and returned goods)					$500,000	$525,000	$550,000
Enhanced Revenues						$250,000	$350,000
Decreased Employee Overtime						$100,000	$100,000
Decreased Overhead					$50,000	$50,000	$50,000
Total Benefits Per Year	$0			$0	$550,000	$925,000	$1,050,000
Confidence Factor	100%			100%	100%	100%	100%
Benefits Claimed for Analysis	$0			$0	$550,000	$925,000	$1,050,000
Project Grand Total Benefit		$2,525,000

Note:
- You need to enter data for the benefit value. The values shown above are just examples. Make sure you use formulas when appropriate.
- Fiscal year for Benefit sources is referenced to the first fiscal year in cost (if the year in the cost changes, the fiscal years in benefit sources automatically change too). Fiscal year for the 2nd to 5th year are automatically computed based on the year in the first fiscal year (one year is added on the previous year).
- Cost reduction will start having the benefit on the third year and increases 5% every year
- Enhanced revenue can start on the third year (2018) and it increases 40% per year
- Total Beneifts by year is the sum of all benefit sources per year (you need to use the formula to compute for this).
- Benefits Claimed for Analysis is computed using the following formula : total benefits per year * confidence factor.
- Project Grand Total Benefit is the total benefits for 5 years.
- You need to format your tables (you can design it the way you want). Make sure that appropriate formats are used (e.g. date format for dates, percentage formats and money formats)

6. Create a workbook that contains the summary of the cost-benefit analysis for in-house

Fiscal Year

	2016	2017	2018	2019	2020
Undiscounted Flows
Costs	$700,000	$650,000	$1,700,000	$1,000,000	$1,050,000
Benefits	$0	$0	$550,000	$925,000	$1,050,000
Net Cash Flow	-$700,000	-$650,000	-$1,150,000	-$75,000	$0

Discount Factors

Discount Rate			7.0%
Base Year			2016
		Year Index			0				1		2		3			4
		Discount Factor			1.0000				0.9346		0.8734		0.8163			0.7629
	Discounted Flows
	Costs			-$700,000				-$607,477		-$1,484,846		-$816,298			-$801,040
	Benefits			$0				$0		$480,391		$755,076			$801,040
	Net			-$700,000				-$607,477		-$1,004,455		-$61,222			$0
	Cumulative			-$700,000				-$1,307,477		-$2,311,931		-$2,373,154			-$2,373,154
Net Present Value			($2,373,154)
Internal Rate of Return			21%

Note:
Fiscal year for Benefit sources is referenced to the first fiscal year in cost (if the year in the cost changes, the fiscal years in benefit sources automatically change too). Fiscal year for the 2nd to 5th year are automatically computed based on the year in the first fiscal year (one year is added on the previous year).

Values for yearly cost and benefits are referenced from the yearly cost and benefits (from cost table and benefits table)
Net cash flow is computed by subtracting the cost from benefits.
Enter the value Discount rate - this is the cost of money that determines the time value of you costs and benefits (example if you are working with an interest-free loan, this would be zero; a typical value is around 8%).
Base year is the current year; the year you want the future perspectives is computed
Year index is computed using the following formula: fiscal year - base year
Discount factor for each year is how much less the cash flows are worth because they are in the future. It is computed using the following formula: 1/((1+Discount Rate)^year index)
Costs for discounted flows cash per year is computed using the following formula: (-1) * undiscounted cost * discount factor
Benefits for discounted flows per year is computed using the following formula: undiscounted benefits * discount factor
Net is the sum of cost for discount flows and benefits for discounted flows.
Cumulative net value is the cumulative net value so far (example 2016 value is the same as the net value; 2017 value is cumulative value of 2016 +2017 net value and so on).
When the cumulative value becomes positive, you have completed your payback period.
Net Present Value is computed by using the NPV formula of following are the items for cost: =
NPV(discount rate, net cash flow from 2017-2020,) + net cash flow for 2016
Internal Rate of Return is computed using the IRR formula of excel: = IRR(net cash flow from 20162020, 0.1)

7. Create a worksheet that contains the graphs and recommendation:

- Create different graphs from the worksheets you created. Select several graphs that you think will be needed to make a valid analysis of the project.

Attachment:- Assessment.zip