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.

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.

Mat 101Task 1Find the limit values:Task 2Find all vertical and horizontal asymptotes toTask 3Find the sums: ( b is to infinity)Task 4The concentration of an algae in the fjord varies periodically with...28 September 2022 at 12:39 amUnit name: Behavioural science = APA 7th edition Refereeing stylePsychology Research Report1,700 wordsTopic is to focus on emotions in general Different findings between anger...the seven segment display is not coming up in the circuit please correct the circuit and resend the proteus file with the working circuitI need it done as soon as possible pleasei just want to know how to apply the model using PML package in RIFN561: Enterprise Systems Lifecycle ManagementAssessment Task 2Assessment Task 2Task overviewAssessment name: Request for Proposal (RFP)Task description: The objective for this assignment is to build...ET 515 Exam #1Create a Power Point presentation explaining the process to change a tire on a standard automobile. Pretend your tire has gone flat and you must change the tire along the side of a deserted...**Show All Questions**