Pre-Lab Excel Activity #3

Working with Excel Lists and Pivot Tables

Must be completed by Tuesday October 27 at 11:00pm by students enrolled in Wednesday lab sessions

Must be completed by Thursday October 29 at 11:00pm by students enrolled in Friday lab sessions

Must be completed by Sunday November 1 at 11:00pm for students enrolled in Monday lab sessions



FOLDER/FILE SUBMISSION TO THE APPROPRIATE KSU ONLINE DROPBOX MUST BE COMPLETED BY THE DATE AND TIME LISTED ABOVE.

 

If you meet the folder/file submission deadline listed above, you can hand in your packet of printouts at your regularly scheduled lab session the next day.

 

IMPORTANT: In order to complete this pre-lab activity, you must have the custom Excel lab manual (the one with the Tiger on the cover).  If you don’t yet have the Excel lab manual, you should be able to purchase it at Varney’s.

 

Before you start, PLEASE print out this entire activity description.  Having a printed copy of this in front of you results in the activity taking less time than completing the activity while constantly switching back and forth between the instructions and the application you are working with.  You will get finished with this activity quite a bit faster if you have printed activity description in front of you.

 

This printout is your assignment description.  Please follow this description, even if it conflicts with the tutorial found in the lab manual.  When working with the tutorial, always read and refer to this printout first, prior to working with the tutorial.

 

Prior to starting this activity, you need to create a folder on your storage media containing your own first and last name, followed by Excel Prelab 3.  For instance, my folder would be called Brian Kovar Excel Prelab 3. 

 

After creating that folder, you will next need to visit the MANGT 366 course on KSU Online. After logging in, visit Files & Content, then Lab Assignments and the folder containing Excel files to download for the third Excel prelab activity.  Download the four files that you see (2 Excel files, a text file and an Access file) into the folder that you just created.  If you create any files from scratch as part of this activity, you will also need to save them into your named folder.

 

IMPORTANT:  PLEASE READ:  If a tutorial talks about getting files from some sort of folder or storage location, you will need to visit the folder that you just created/added to your storage media in the prior paragraph.  All files that you don’t create yourself are already in that folder.  When one of the tutorials tells you to save a file under a certain name, use the name required in the assignment description that you are currently reading (rather than the name found in the tutorial text).

 

IN EXCEL PRE-LAB ACTIVITY#3, DO NOT DISPLAY GRIDLINES IN ANY OF YOUR PRINTOUTS.

 

SPECIAL NOTE:  PLEASE READ

All currency/dollar values found in this project must contain the $ symbol and be formatted using “Currency Style”.  Please use the “true” currency style, rather than the $ button.  Although the button is labeled as “$”, it is really Accounting Style.  You want to use the true currency style, which results in a $ sign right next to the number, such as $45

 

Pre-Lab Excel activity #3 is composed of the following components:

 

Chapter 4: Data to Information

When you open the folder, you will find a text file and an Access file (both containing the text: Your Own Name Goes Here).  Replace those words with your own first and last name, similar to my example:  Brian Kovar.   If you are asked to save your work as an Excel workbook, the file name that you save your work under should also contain your first and last name, just as these two files now do.  You will also see a file currently called Your Last Names Goes Here salesrep.  Rename that file, replacing the words Your Last Name Goes Here with your own last name, similar to my example: Kovar salesrep

1)      Please read pages 175-183.

2)      Using the text file that you just renamed, and eventually the Access file you also renamed, please complete the tutorial steps on pages 184-187.  Print out your results after completing step #4D on page 187.  Label this printout as Conditional Formatting.

3)       Read pages 189-201.

4)      Do not do any of the renaming steps on step #1A on page 202. 

5)      Using your currently open Excel file, click back to the worksheet that contains the data that you imported from the text file (your first import).

6)      On page 202, complete steps #1B and #1C.

7)      Create the formula seen in step #1D on page 202. If you do not see a number that seems to make sense, then make sure you include the parenthesis, as shown.  PLEASE BE AWARE THAT YOUR NUMBERS WILL BE SIMILAR TO THE NUMBERS SEEN IN THE TEXT.  HOWEVER, THEY WILL NOT MATCH.  That is because you are using the TODAY function, and the date that you are doing this activity does not match the date the author made the screenshots that you are looking at.

8)      Continue on with step #1E and follow the instructions in the tutorial as written, stopping after you complete step #2E.  Make a printout of your results after completing step #2E.  Label this printout as Filtered Red Salaries.

9)      Continue with step #2F on page 203 and do all of the activity steps that you encounter, stopping after you complete step #8E on page 209.

10)  In cell B19, type in Atlanta.  In cell C19, type in Account Rep.  Make a printout of your work and label it as Database Statistics.

11)  Save your work and close the file.

Pivot Table Tutorial

Understanding Pivot Tables is important because employers of KSU College of Business students expect that our student know, understand and can construct Pivot Tables.  Most of your Excel project #3 steps will be related to Pivot Tables.  Due to the complexity of pivot tables, the emphasis that employers place upon having the people that they hire having pivot table skills, the importance of having pivot table skills in the business world, and the fact that I don’t think that textbooks give pivot table coverage enough emphasis, I have written my own tutorial to teach students pivot table skills.  Completing this step-by-step tutorial is the next part of your pre-lab activity.  This tutorial has also been published by the Information Systems section of the American Accounting Association in the Compendium of Classroom Cases and Tools for AIS Applications as part of “The Pivot Table Toolkit” (B. Kovar, S. Kovar, R. Vogt 2009).  You can download the tutorial pdf file by visiting http://info.cba.ksu.edu/bkovar/PivotTableTutorial.pdf.  The file that you will use to complete the tutorial is called Pro Golf USA Pivot Table Data.xlsx and it should be in the folder that you downloaded earlier.  Just read through that, completing each activity step as it is encountered.  Save your work regularly as you progress through the tutorial. When you have finished the tutorial, you should have the following printouts:

 

Extra Credit (optional: not required)

Employers of KSU CBA graduates and I both think that having pivot table experience is important.  Most students find pivot tables challenging and having more pivot table experience is definitely beneficial.  As a result, I am willing to offer some extra credit in order for you to gain additional pivot table exposure.  If you wish to have this additional exposure and extra credit, then please continue on:

12)  Please carefully read pages 210-219.  Understanding Pivot Tables is important because employers of KSU College of Business students expect that our student know, understand and can construct Pivot Tables.  Most of your Excel project #3 steps will be related to Pivot Tables.

13)  Open the file that you previously renamed that contains your last name and salesrep (for example: Kovar salesrep)

14)  On page 220, read step #1A and then start doing the steps, beginning with step #1B and continuing on through step #3F on page 222.

15)  After completing step #3F on page 222, format your work conforms to the formatting rules that we have seen in class (number formats, alignment, etc.)  Additionally, format the spreadsheet so that it fits on 1 page.  Then, make a printout of your work.  Label this printout as First Extra Pivot Table.

16)  Continue on with the next step on page 222 and do all of the steps that follow, stopping after you complete step #5C on page 223.

17)  After completing step #5C, click the regular spreadsheet (outside of the Pivot table and Pivot chart).  If the Pivot chart is covering any of the Pivot table, click the chart and drag it below the table, so that your chart and table match what is seen in figure 52 on page 224. 

18)  Once your work matches figure 52, click one of the cells in the regular spreadsheet.  Do a print preview and make sure that you can see both the pivot table and pivot chart in the same view/printout.  Once you see both at the same time, make a printout of your work.  Label this printout as Extra Table with Chart.

19)  After making the printout, continue on with the next step and finish the rest of the tutorial, following the instructions as written.

20)  After you have completed the last step, make a printout that shows me you completed the last few steps.  Label this final printout with the name I’m All Done With the Extra Credit.

 

 

HANDING IN THE ACTIVITY

1) Submit your named folder (containing all of the Excel files) to the proper location in the KSU Online dropboxes.

 

2) Please assemble your packet of printouts.  The printouts should be arranged in the following order:


If you completed the extra credit, then you will have these additional printouts

 

 

IN ORDER TO BE CONSIDERED FOR FULL CREDIT, YOUR PRINTOUTS MUST BE IN THE REQUIRED SUBMISSION ORDER.  Deductions will be taken for printouts that are not in the proper order.

 

FOLDER/FILE SUBMISSION MUST BE COMPLETED BY THE DUE DATE/TIME LISTED ABOVE.  If you meet the folder/file submission deadline listed above, you can hand in your packet of printouts at my office or in your regularly scheduled lab session the next day.