Excel Project #2

Multiple Worksheets

Due Monday April 16 at 12:00pm for students enrolled in Wednesday lab sessions.
Due Wednesday April 18 at 12:00pm for students enrolled in Friday lab sessions.
Due Friday April 20 at 12:00pm for students enrolled in Monday lab sessions.

Due Friday October 22 at 12:30pm for students enrolled in Monday lab sessions.
Due Monday April 9 at 12:00pm for students enrolled in Wednesday lab sessions.
Due Wednesday April 11 at 12:00pm for students enrolled in Friday lab sessions.
Due Friday April 13 at 12:00pm for students enrolled in Monday lab sessions.

Due Monday October 25 at 12:30pm for students enrolled in Wednesday lab sessions.
Due Wednesday October 27 at 12:30pm for students enrolled in the Friday 8:30am lab session.

IN ORDER FOR YOUR SUBMISSION TO BE CONSIDERED “ON-TIME”, your printouts and file submission must be made by the day/time listed above.

Unlike other projects that I suggest that you start working on early, there is no need to start working on this one early since I plan to use class time to work on selected parts of the project while in class as part of a lab-lecture demonstration (and hopefully you will work on the problem with me as I am doing it on the big screen). I will try to get as much of the project done in class as I can, and then students will finish up the rest of the project on their own. I plan on working on selected parts of both case problems.

As a result, everyone should try to be in class the week of October 18-22(or you will be doing the entire project on your own). Additionally, I suggest that everyone make a printout of this activity description so you can have it in front of you in class so you can cross off steps that we have already completed and you can also note steps that still need to be done.

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.

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 Project 2. For instance, my folder would be called Brian Kovar Excel Project 2.

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 files to download for the second Excel project activity. Download the three Excel files and the word processing file that you see 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.

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

Excel project #2 is composed of the following components:

Case Problem #1:

You are an assistant to Professor David Templeton, who teaches Math 220 at KansasStateUniversity. He has asked you to help him develop a grading workbook His course has 3 sections, and he wants to enter the grades for each section in a separate worksheet. The workbook should track three sets of grades: the first and second exams and the final exam. The workbook should also calculate an overall final score, which is equal to 25% of the first exam score, plus 25% of the second exam score, plus 50% of the final exam. Finally, the workbook should display a final grade based on the overall score. Professor Templeton plans to assign grades according to the following range of scores:

  • 0 to 49 = F
  • 50 to 59 = D
  • 60 to 74 = C
  • 75 to 89 = B
  • 90 to 100 = A

A set of exam scores has already been entered for you (in the file called Grade1). Your job will be to calculate an overall score and grade for each student in each session. You should also format the worksheets and output.

1) Rename the file called Grade1 (your name also goes here) so that the new name for the file contains your own name, similar to my example: Grade1 Brian Kovar

2) In the Documentation sheet, in cell B3, enter the date using the TODAY () function. In cell B4, enter your name.

3) Insert a new worksheet called “Grading Criteria” after the Documentation sheet.

4) On the Grading Criteria worksheet, enter the text “Exam Average” in cell A1 and the text “Grade” in cell B1.

5) In the range A2:B6, create an area/table containing the range of grades specified by Professor Templeton This will be the table used by your VLOOKUP function so don’t forget the rules that go along with the VLOOKUP function when you type in this data. (Hint: Each letter grade should be matched up with the lowest possible score for that grade).

6) On the Grading Criteria worksheet, enter the text “Exam Type” in cell A9 and the text “Weight” in cell B9. In the range A10:B12, enter each exam and the percentage weight of that exam in the final score. Format the Grading Criteria worksheet so that the weights display as percentages. Format the Grading Criteria worksheet using the formatting rules mentioned in class and seen in the first lab session (including column widths and label/number alignment within columns).

7) Now, it is time to look at each of the section worksheets. On each section worksheet, insert a formula into column E (of each worksheet) to calculate the overall score for each student based on the results of the three exams. (Hint: Remember to use Absolute or Mixed Cell References where needed). Remember to include the weight of each exam in the calculation of the overall student average. The formulas entered into column E should interactively reference the weights entered into the “Grading Criteria worksheet. Format the results in column E to display two decimal places.

8) In column F, insert a formula to calculate the final letter grade for each student based on the grade table you entered in the Grading Criteria worksheet. (Hint: Remember to use Absolute or Mixed Cell References where needed).

9) Format the section worksheets to improve their appearance, keeping in mind the formatting rules mentioned in class and seen in the first lab session (including column widths and label/number alignment within columns).

10) On all worksheets, do the following

  • Format all columns of data so that data labels are directly over the numbers they describe. You might also want to consider narrowing down your columns.
  • Using Page Setup option, format each sheet so that it is scaled to fit to 1 page. Each sheet should be centered horizontally on the page, with the name of the section centered in the header, and your name and the date on separate lines in the footer. On the Grading Criteria worksheet, your name should display as a footer.

11) Save your work. Print all spreadsheets (Grading Criteria, Section 1, Section 2, and Section 3).

12) Display and print the formulas for Section 1, Section 2 and Section 3.

13) On the Grading Criteria sheet, change the weight for exam #1 from 25% to 15%. Change the weight for exam #2 from 25% to 20%. Change the weight of the final from 50% to 65%. Print all three section spreadsheets once again so the new grades are visible. On each of these new printouts, write REVISED GRADES USING NEW WEIGHTS.

Written Questions: The folder that you downloaded earlier contains a written question that you need to answer using your word processor. Please open the file called Excel2.doc to answer that question.

Case Problem #2:

Joy Ling has approached you with another workbook project. She wants you to create a workbook that displays monthly contributions for the year. The workbook should contain a separate worksheet for each month of the year, showing the total contributions for that month and for the year up through that month.

Each contributor has been assigned an ID number. Joy has a separate workbook that contains the contributor’s name, address, and phone number, as well as whether the contribution came from a personal, business, or governmental source. Rather than repeating this information in the new workbook, Joy has only included the contributor’s ID number. She wants you to insert the remaining information using Excel’s lookup feature.

1) Open the file called CList. On the documentation sheet, type the date in cell B3 and your name in cell B4. Save your work. Close the CList file.

2) Rename the file called Contributions (your name also goes here) so that the new name for the file contains your own name, similar to my example: Contributions Brian Kovar

3) Open the Contributions file that you just renamed. Enter your name and the current date in the documentation sheet. Review the sheets in the Contributions workbook.

4) Create a worksheet group that contains the January through December worksheets.

5) In the worksheet group, insert a formula in cell B5 to display the sum of the contributions in column E.

6) Ungroup the worksheets. Verify on all of your sheets that cell B5 contains the sum of all of the contributions for that particular month.

7) In cell B6 of each worksheet, insert a formula that displays the total contributions for the current month plus the total of all previous months for the year (Hint: Enter the formula using a cell reference in cell B6 to the previous month’s contributions, cell B5, plus the contributions of the current month).

8) Open the CList file once again. It contains the list of choir contributors in the range A2: E44.

9) Switch to the Contributions workbook, and then regroup the monthly worksheets. In cell F3, insert the VLOOKUP function, where the lookup value is the contributor ID found in cell D3, the lookup table is located in the range A2:E44 in the contributors list in the CList workbook, and the column index number is 2.

10) Insert the VLOOKUP function for the rest of the values in row 3 (Type, Address, and Phone). For the Type, the column index number is 3, for Address, the column index number is 4, and for the Phone Number, the column index number is 5. (Hint: Use a mixed reference to refer to the lookup value (always in column D) so that you can copy the VLookup formula).

11) Copy the VLOOKUP formulas from row 3 into the range F3:I25. Don’t worry if #N/A appears on some of your sheets.

12) On all worksheets, do the following

  • Format all dollar values to display with the currency symbol and no decimal places.
  • Widen out all columns so none of the entries in that column are cut off.
  • In the Page Setup option, format each sheet so that it displays in landscape orientation with the worksheet scaled to fit to 1 page.
  • Create a footer on all sheets so that your name displays centered in the footer.
  • Create a header on all sheets so that the name of the sheet/month displays centered in the header.

13) Ungroup the worksheets. Visit all of your worksheets, and on any sheet where you see #N/A, delete the #N/A wherever it might appear.

14) Save your work

15) Print the January, May, August, October and December worksheets. Label each printout to indicate the month.

16) Display and print the formulas for January, May, August, October and December.

ALL PRINTOUTS THAT YOU MAKE FOR THIS ASSIGNMENT NEED TO FIT ON ONE PAGE.

HANDING IN THE ASSIGNMENT

1) Submit your named folder (containing all of the Excel project 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:

  • A cover page containing the usual required information.
  • Your response to the written question.
  • The Grading Criteria printout
  • The Section 1 printout (as indicated in Case Problem #1 step #11)
  • The Section 2 printout (as indicated in Case Problem #1 step #11)
  • The Section 3 printout (as indicated in Case Problem #1 step #11)
  • The Section 1 formulas printout
  • The Section 2 formulas printout
  • The Section 3 formulas printout
  • The Section 1 printout using the revised weights (as indicated in Case Problem #1 step #13)
  • The Section 2 printout using the revised weights (as indicated in Case Problem #1 step #13)
  • The Section 3 printout using the revised weights (as indicated in Case Problem #1 step #13)
  • The January printout from Case Problem #2 step 15.
  • The May printout from Case Problem #2 step 15.
  • The August printout from Case Problem #2 step 15.
  • The October printout from Case Problem #2 step 15.
  • The December printout from printout from Case Problem #2 step 15.
  • Case Problem #2 step #16 formulas printouts, in this order (January, May, August, October, December)

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.