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.

ASSESSMENT 2 BRIEFSubject Code and Title HDW204 Healthcare in the Digital WorldAssessment Report – Patient-facing technology platformIndividual/Group IndividualLength 1800 words (+/- 10%)Learning Outcomes...ACC102 Financial Accounting Individual AssignmentSemester 2, 2021Assessment type: Individual Assignment composed of three questions.Submission: online via Moodle.Weighting: 30% of overall markDue date/submission:...Identify the three most likely threats to an office computing system with less than ten employees by identifying the three most likely vulnerabilities to be exploited. Estimate the number of times each...INT102: Interpersonal Communication SkillsAssessment 2: Individual Written ReportDue Date for 2B Written Report: Week 6 (20%)2 B: Written report (20%)Students are required to submit a written report between...Hi there can you please quote for this assessment.Task 1 Company is AdairsAssessment 4, Finance for Managers SP5 2021Total 50 marksDue Date: 13 October 2021, 11PMIn this assignment, you will undertake...Course eventDescriptionWord length: 1000The aim of this assessment is for students to be able to demonstrate the ability to acquire knowledge and the capacity to critically reflect on key concepts and...Assessment 3 BUS304Due Oct 29, 2021 This is an individual assessment.Hofstede's Cultural Dimensions1,500 to 2,000 words plus or minus 10%Geert Hofstede outlined a theory that would describe a way that...**Show All Questions**