Excel Project

CS 14A

60 pts., due Apr. 11

Grading criteria:

1. Perfect spelling.

2. Formatting: The spreadsheets should be easy to read. Titles should be formatted using Center Across Selection. Headings should stand out. There are many ways of accomplishing this: boxes, shading, different fonts, bolding, larger point sizes, etc. Dollar amounts should be formatted using one of the currency formats. Percentages should be formatted as such. Columns should be the appropriate width.

3. Your spreadsheets should be designed so that no formulas need be modified if model assumptions change. This is easiest to accomplish if model parameters (percentages, initial amounts, and such) are stored in their own cells, which are referenced in subsequent formulas.

4. For chart types, choose the most appropriate chart type for the data you are displaying. Use titles, axis labels, legends, etc. as appropriate.

1. A friend of yours is in the process of buying a home and has asked you to compare the payments and total interest on a 15- and 30-year loan. You want to do as professional a job as possible and have decided to analyze the loans in Excel, then incorporate the results into a memo written in Word. As of now, the principal is $150,000, but it is very likely that your friend will change his mind several times, and so you want to use the OLE capability within Windows to dynamically link the worksheet to the word processing document. This project requires that you create two files: the spreadsheet and the memo document.

2. The purchase of a car usually entails extensive bargaining between the dealer and the consumer. The dealer has an asking price, but typically settles for less. The commission paid to a salesperson depends on how close the selling price is to the asking price. Exotic Motors has the following compensation policy for its sales staff:
   • A 3% commission on the actual selling price for cars sold at 95% or more of the asking price.

   • A 2% commission on the actual selling price for cars sold at 90% or more (but less than 95%) of the asking price.

   • A 1% commission on the actual selling price for cars sold at less than 90% of the asking price. The dealer will not go below 85% of her asking price.
   The dealer's asking price is based on the dealer's cost plus a 20% markup; for example, the asking price on a car that cost the dealer $20,000 would be $24,000.

   Develop a spreadsheet to be used by the dealer, which shows her profit (the sales price less dealer's cost and commission) on every sale. The spreadsheet should be completely flexible and allow the dealer to vary the markup or commission percentages without having to edit or recopy any of the formulas. Use the file dealer.xls on the R: drive in the excelprj folder as your starting point.

3. Your work study job next semester has placed you in the Provost's office, where you are to create a presentation for the Board of Trustees. The Provost is expected to make recommendations to the Board regarding the expansion of some programs and the reduction of others. You are expected to help the Provost by developing a series of charts to illustrate enrollment trends. The Provost has provide you with an Excel spreadsheet ( enrolmnt.xls on the R: drive in the excelprj folder) with summary data over the last several years. Put each chart on its own page of the spreadsheet. For each chart, provide some written justification of its inclusion (why you are including it) and design (why you chose the particular chart type). The justifications should appear on the original page of the spreadsheet.