Topic: excel

Detailed Question: Informatics with Excel #1 - determine a cost / benefit analysis

Your company wishes to invest $675,000 into a new web accelerator product. Your company believes this product will increase online sales as web response time for its users will be significantly better.

You will create a spreadsheet to determine whether the benefit received will outweigh the cost of the product.

For each month your spreadsheet will compute costs and benefits of the system. The analysis should include 3 years of figures.

After the initial purchase price of $675,000 there is a decreasing scale maintenance contract beginning in the second month. The maintenance contact begins at $15,000 and decreases 5.25% per month. (Although this value is subject to change at any time.)

The benefit is realized in increased sales from the company's website. There is a $26,000 sales increase (over the base) in the first month followed by a measurable but declining sales increase each month. The initial projection is that the $26,000 sales increase will decline by 2.75% per month. (Although this value is subject to change at any time.)

Do not hardcode data values in the body of the spreadsheet; use formulas with cell references only.

Create a spreadsheet that will allow your manager to play a "what if" scenario by changing the values via the click of a button, especially for the percentage monthly decrease in the maintenance contract and the monthly decrease in additional sales. (Of course, all values should be changeable by the user.) The user should be able to easily determine the total cost/benefit for the entire time and at the end of any month.

You will determine the layout and formatting of the spreadsheet, along with the correct formulas to solve the problem. Be sure to apply the heuristics of good spreadsheet design. Be sure the user can quickly and easily identify the input and output area. Be sure that the spreadsheet is formatted well.

Use conditional formatting along with the IF function to display a message in the output error indicating "GO" (in green) when benefits exceed cost and "STOP" in red when costs exceed benefits.

I am purposefully not providing you with a design layout for the spreadsheet. Your job is to determine a meaningful presentation of this problem using an Excel workbook.

[This spreadsheet is not realistic as it does not include a discount factor putting the figures into today's values.]

There are many different layouts and designs that can solve this problem well. However, no two spreadsheets submitted by students should be identical as this would indicate plagiarism.