This project will introduce you to some basic features of Microsoft Excel.  Excel is the dominant market player in the spreadsheet market at this time and its use is a prerequisite to success in the current accounting (and business) job market.  You will also be exposed to some advanced features of Excel to whet your appetite and encourage you to learn more about this software package that you will soon use on a daily basis.  For those of you that are already "power" users of Excel, show me your creativity and expand on the requirements of this assignment.  If you are new to Excel, I would begin with MS training materials 2007. 

As you complete the project, make sure you utilize the extensive Help features of Excel.  The most common questions I'm asked during the completion of this project can be found using the Help function.  Please contact myself or my student assistant with any questions, after you have attempted to answer the question yourself using the Help function.  This ability to "learn" the features of Excel and other Microsoft products will be invaluable as you develop as a business professional.

Start by reading Ensuring Spreadsheet Accuracy in the Sarbanes-Oxley Era. This article discusses the importance of ensuring that Excel files are adequately documented and tested.  We won't use the validation testing, etc., in this project.  However, I would recommend rereading the article after you complete the project and using some of the techniques mentioned.  Also see Sloppy Spreadsheets for some ideas of what not to do.

Due dates:
                                                                     Points

    Excel Project I                    October 6            50   
    Excel Project II                   October 13          20
    Excel Project III                  October 20          30         
   

PROJECT I:

Start by reading How to Link to Web Data,[EXCEL 2007]  Follow the instructions in the article to proceed. Simply select stocks as indicated by the instructions. The only update will be the new stock prices that are downloaded from the web.  This one is pretty straight forward.  Note that you might need to change the cell references in your worksheet based on the location of the stock quotes from your web query.  I think you will be impressed with how easy it is to download web based information directly into Excel.  Just think of the possibilities for end-user computing by accessing data directly from databases and having calculations automatically updated.  Sounds like increased productivity to me!  Add some graphs as well to spice things up.

All homework submitted electronically should have the following subject line: 

Lastname_Firstname_Homework, or  Payne_Jeff_Excel_Project_1. 

PROJECT II:

See cool Microsoft online tutorial for Excel 2007 as well.  Once you have familiarized yourself with the purpose and functionality of PivotTables, proceed with the following instructions. 

Open this Excel file (2007). The Excel file will have 186 rows and go to column H.

Use this data to create a PivotTable.  For Excel 2007 you will see:

Use Document Type for column labels, Vendor Name for Row labels, and Document Amount for Values.  You will need to change this to Sum of Documents using the options in the Values section. Your first row should indicate Board of Water and Light with an Invoice of 16411.25 and a Payment of 16411.25 with a Grand Total of 32822.50.

Next copy the PivotTable output (values only using paste special) to a separate worksheet.  If you have a column named "blank" delete it.  Change the Grand Total column to Balance, then change the formula to determine the balance due for each account (Invoice-Payment-Returns).  The Total Balance should be $19,933.60.

Reformat the columns to allow all information to show.  Also change the format to "," for all the numeric fields, with $ for the first and last row with dollar figures as follows:

Rename the worksheets, Data, PivotTable, and AP Balance.  Delete worksheets that are not used. Add a new worksheet, labeled Description, and detail what this Workbook is used for, where did the data come from (a data extraction for the AP master file), what are you trying to learn.  Someone should be able to read this description and understand how to replicate the workbook on their own.

Save your file as Lastname_Firstname_EXCEL_2 and e-mail before class begins on the due date (remember to use the correct subject line with your e-mail: Lastname_Firstname_Homework, or Payne_Jeff_Excel_Project_2. 

PROJECT III: 

Create Workbook shown in VLOOKUPexample.  Save file as Lastname_Firstname_Excel_3 and e-mail before class begins on the due date. Subject line: Lastname_Firstname_Homework, or Payne_Jeff_Excel_Project_3.