CS170 – Computer Applications for Business
Spring 2024 • Assignment 10
|
Due Date:
|
Before 11:59 p.m. on Friday, April 19th, 2024
|
|
Accept Until:
|
Before 11:59 p.m. on Friday, April 26th, 2024
|
|
Evaluation:
|
20 points
|
|
Submit to Canvas:
|
Assignment10.xlsx file
|
Learning Objective:
This assignment is designed to gain additional proficiency in the use of MS Excel Tables, Pivot Tables and lookups. For this assignment, you will upload your Excel file to Canvas so that it can be accessed by just clicking on the file's link.
To get credit for this assignment:
1 Deliver the assignment10.xlsx file to Canvas on time.
Directions:
• Follow the requirements listed on the next page.
• Create the Excel Table and the Pivot Tables as indicated.
• Enter your name on cell J5 and honor code of the Drinks worksheet.
Requirements:
Excel Tables, VLOOKUP
The Drinks worksheet shows monthly sales byproduct and state.
A. In Column E, create a heading “ Month Number” and create a formula that takes in the Month from column A and outputs the month number. Hint use =Month()
B. Convert the Drinks dataset into an Excel Table (remember you only need to select one cell to create excel table)
C. Format the Sales to be currency ($) with comma separator for (000)
D. Add column header “ Discount” for Column F, format the discount to be percentage (%)
E. Add column header “ Discount Amount” for Column G, format to currency ($) and comma separator for (000)
F. Apply the Blue, Table Style Medium 9 to the table.
G. Sort column E from smallest to largest.
H. Add a Total row using the totals feature of excel tables and use the properties of the Total Row to calculate:
1. the sum of sales (Column D).
2. the average of discount (column F)
3. the sum of discount amount (column G)
I. Starting at cell K9, setup a table that sales 0-$999 has 0% discount, $1,000 to $9,999 has 1% discount, and $10,000 and higher has 3% discount.
J. Use the table created instep I to create a name range called “salesDiscount”
K. In column F, create a VLOOKUP that references column D to lookup the discount in the named range you just created.
L. In column G, calculate the value of column D and column F.
M. In cell N5, use the table to filter on March sales. Take the sum of sales for March and paste the value into N5
Pivot Tables and Pivot Charts
Pivot Table & Pivot Chart:
A. Use the Drinks worksheet and create another sheet with Pivot Table that displays the sum of Sales $ organized by: Product and Month as row labels (in that order) and State as column labels. Format all sales numbers as currency ($) with no decimal places. Click on the + next to Breakfast Power so you can see each of the months.
B. Collapse the Product rows and create a Clustered Column Pivot Chart keeping on same worksheet.
C. Set the title of the chart to be “ Drink Sales”
D. Apply Chart Style 14.
E. Rename the PivotTable's worksheet: Pivot Table Drinks.
F. In cell K1, please put in the state with the highest drinks sales, the bestselling drink in that state, and the best-selling month for that drink and state.
References:
1. Chapters 13 and 14 of the Fluency7 textbook
2. Support.office.com Table and Pivot Table Tutorials
3. Lectures and Recitations