Comp25: Excel - Five Year Profitability Study

Learning Objectives

1. To create a five-year analysis of income and expenses for the purpose of attracting future investors.
2. Learn to work with grouped worksheets and linked formulas in Excel

Steps to take

1. Open your first Excel lab Income and Expense worksheet that you have already created.  Save it as "Five-Year Study." 
   (Note: If you cannot find your original file, download it from your Blackboard lab assignment)
2. Name your first sheet tab 2009
3. Duplicate your worksheet four times and name your new sheet tabs 2008, 2007, 2006 and 2005 respectively.
   
   
   
4. Notice that all five worksheet have exactly the same information since you have just duplicated the information in 2009.  Go in and randomly change the income/expense raw data in 2005-2008 so that each worksheet has unique "bottom line" totals. Be careful not to overwrite formula cells -- Only change raw data!
5. Be sure to change your title information in each page to reflect the current year or the summary page:
   
   
   
   
6. Duplicate your last worksheet one more time to create a summary worksheet.  Name the worksheet "Five Year Summary" and move it so that it is the first worksheet in the workbook.  Change the title within the worksheet so that it is titled "Five Year Summary" as well.
   
   
   
   
   
7. Delete all the data in the worksheet EXCEPT the column and row headings.
8. Add linked formulas to your summary worksheet to link the data from all five yearly worksheets to a single worksheet.
   1. Use the Point and Click method combined with the Shift key to easily link formulas from the five years of data to a single summary sheet.
   2. Example:  =SUM('2009:2005'!B6)
   3. Create linked formulas for your income first and then complete your expense linked formulas and lastly do your Profit/Loss formula.
   4. All cells are relative for both income and expense, so you should be able to use the Fill tool to copy the linked formulas within each respective category.
   5. Adjust Border formatting if necessary
9. Group all six worksheets together and change the original formatting so that it is unique to this file.  It must be different than the original file. 
10. Make each sheet tab a different color
11. Protect your formulas in the Summary worksheet so that they cannot accidentally be overwritten
12. Scale your Summary Worksheet in the Page Setup menu so that it fits to one page
13. Save and upload to Blackboard. 

Grading Guidelines

This assignment will be graded using the following guidelines: