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 2010. 

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 me only after you have attempted to answer the question yourself using the Help function, or by using a tutorial at Microsoft or even YouTube.  This ability to "learn" the features of Excel and other Microsoft products will be invaluable as you develop into 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 apply some of the techniques mentioned to your completed projects.  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 11          20
    Excel Project III                  October 18          30         
   

Very Important:  Each project should be e-mailed separately with the following in the subject line:  Lastname_Firstname_Excel_project 1 (2, or 3).  Points will be awarded for each submission for doing this correctly.

All email for this project should be sent to uk.acc.324@gmail.com.

PROJECT I:

Start by reading How to Link to Web Data,[EXCEL]  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: 

Use your Lastname_Firstname_Homework, or for example Doe_John_Excel_Project_1. 

PROJECT II:

See online tutorial for Excel 2010 on YouTube to gain an understanding of Pivot Tables.  YouTube is a great resource for videos on about anything you would like to learn using Microsoft Office.  Once you have familiarized yourself with the purpose and functionality of PivotTables, proceed with the following instructions. 

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

Use this data to create a PivotTable (as shown in the above tutorial).  Place cursor in cell A1 and select Insert PivotTable. For Excel you will see:

Make sure you have the same data range and have selected New worksheet.  Click OK.  You will now see:

Use Document Type for column labels (just drag and drop Document Type into the Column Labels box), Vendor Name for Row labels, and Document Amount for Values.  This will change to Sum of Documents. 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 entire PivotTable output (using the paste special values command) to a separate worksheet.  If you have a column named "blank" delete it.  In the Grand Total column 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:

The Grand Total column should be a calculated field of the Invoice - Payment - Return.  The Grand Total of $19.933.60 should also be a calculated fields. Rename the worksheets, Data, PivotTable, and AP Balance.  Delete worksheets that are not used (if any). 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, for example Doe_John_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, for example Doe_John_Excel_Project_3.