Part 2 – Spreadsheet Editing (10 marks)
The exercises in Lab 2 – Spreadsheet Editing will provide good practice for this part of the assignment.
This part of the assignment places you in the role of a sales manager for a fictitious entertainment company. In this role, you are responsible for tracking and projecting sales and sales targets, and tracking and managing the performance of sales staff.
For this part of the assignment, you will need the following files available in the P2Data folder in the AssignmentData.zip file that is available for download from Canvas:
1. P2-Data1.xlsx
You will produce the following files which will contain your work for this part of the assignment and be part of the zip file which you will submit on Canvas (with YOURSTUDENTID replaced with your student ID number):
1. P2-YOURSTUDENTID-SalesReports.xslx
Instructions
1. Open P2-Data1.xlsx using Microsoft Excel. Use ‘Save As’ to save a copy of this file as P2- YOURSTUDENTID-SalesReports.xlsx (replacing YOURSTUDENTID with your student ID number). Make sure you save your work regularly to avoid losing any progress! The text and data that is included in P2-Data1.xlsx is the data you will be adding to and using for this part of the assignment.
2. For all of the following steps, use a function wherever appropriate.
3. For the Quarterly Sales Summary sheet, make the following additions:
a. In the ‘Total 2018’ column (F), add formulas to calculate the total for sales across all four quarters for that type of network.
b. In the ‘Projected 2019 Sales Increase’ column (G), add formulas to calculate the projected 2019 sales increase which is the value in Total 2018 multiplied by the
Projected Sales Growth value in row 4. Complete the formula for ‘Classic Movie and TV Networks’ and use the fill option to copy the formula for the other types of network.
c. In the ‘Totals’ row (12), add formulas to calculate the total for each quarter, total for 2018, and projected 2019 sales increase.
d. In the ‘Trend’ column (H), add a Sparkline to show the change in sales for each network over the quarters in 2018.
e. Add a chart that shows how each of the networks contributed to the total 2018 sales. Make sure the chart has an appropriate title and labels. Place the chart below the table.
f. In a cell directly below the chart, briefly ( 200 words) justify your choice of chart type.
g. Format the sheet appropriately, ensuring all cells and values are appropriately formatted and the contents are entirely visible at 100% zoom. All sales figures are in dollars.

4. For the Bonuses sheet, make the following additions:
a. For the ‘Total Sales’ column (G), add formulas to calculate the total annual sales (across all four quarters) of each sales representative’s sales. Apply appropriate conditional formatting to the total sales column so that the relative performance of each individual sales representatives compared to each other can be quickly seen.
b. In the ‘Bonus’ column (H), add formulas so that sales representatives whose total annual sales are above the average annual sales for all sales representatives receive a bonus (‘Yes’), and the others do not (‘No’). Also add conditional formatting in this column to highlight any ‘Yes’ values.
c. In the ‘Total’ row (18), add formulas to calculate the total sales by sales representatives for each quarter and across all four quarters.
d. Under the Summary Statistics heading, for each entry enter formulas (in column D) to calculate and display the statistics as listed. Note: You should not have to change the data table in order to accomplish this.
e. Add a chart that shows how total sales by all sales representatives combined has grown and shrunk across the four quarters. Make sure the chart has an appropriate title and labels. Place the chart below the table.
f. In a cell directly below the chart, briefly ( 200 words) justify your choice of chart type.
g. Format the sheet appropriately, ensuring all cells and values are appropriately formatted and the contents are entirely visible at 100% zoom. All sales figures are in dollars.
5. For the Sales Projections sheet, make the following additions:
a. In the ‘Total 2018’ column (B), change the static values to appropriate formulas that refer to the appropriate values in the Quarterly Sales Summary sheet. Also, add a formula in the Totals row for this column (cell B12) to calculate the correct total.
b. In the ‘% of Total Sales’ column (C), add the appropriate formulas to show the percentage contribution of each network’s sales to the total 2018 sales.
c. In the ‘Target % of Target Total Sales’ column (F), add the appropriate formulas to show the percentage contribution of each network’s target sales amount to the total target sales.
d. Delete the target amount for International Networks (cell E11). Then use Goal Seek to determine the target amount of International Networks that is required for it to make up 13% of total sales coming from International Networks.
e. Format the sheet appropriately, ensuring all cells and values are appropriately formatted and the contents are entirely visible at 100% zoom. All sales figures are in dollars.
6. Create a pivot table in a new sheet using all of the data (A9:G223) in the Trips sheet. Using this pivot table:
a. What is the total of sales from trips for Film Entertainment Networks in Australia (AUS)? Enter this value in the cell A4 of the Trips sheet.
b. How much did Lisa Vanderbeck spend on trips (Trip expenditures) in Quarter 3? Enter this value in the cell A5 of the Trips sheet.

c. Which sales representative had the highest average profit per trip? Enter their name in cell A6 of the Trips sheet.
d. Which sales representative made no trips in a given quarter? Enter their name and the quarter in which they made no trips (e.g. John Smith, Q1) in cell A7 of the Trips sheet.
e. Set up the pivot table to show profit for the Film Entertainment Network only, broken down by Region and Quarter. Format the values displayed by the pivot table appropriately, ensuring that the contents are entirely visible at 100% zoom.
7. Make sure you save your completed worksheet as P2-YOURSTUDENTID-SalesReports.xlsx
(replacing YOURSTUDENTID with your student ID number) for submission.