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.

Hi,Please read the assessment guide attached and weekly content.Most of the information for this assessment can be found in week 10 material.CheersAssessment 3 InformationSubject Code: MKT203Subject Name:...Hi guys,Please read the assessment guide attached.This assessment must be based on the assessment Oth200918228 (Also attached).Please, pay attention on all the assessment instructions.CheersAssessment...Written AssignmentFinancial Planning Fundamentals(DFP1_AS_v4A1)Student identification (student to complete)Please complete the fields shaded grey.Student numberAssignment result (assessor to complete)Result...Please follow the assessment guideline and the instruction for the structure with indication. I need this assessment by 25th september aus time 12 AM. Except everything the assessment should be in 1000...HOLMES INSTITUTEFACULTY OFHIGHER EDUCATION Assessment Details and Submission GuidelinesTrimester T2 2020Unit Code HI5020Unit Title Corporate AccountingAssessment Type Individual AssignmentAssessment Title...Assessment 3 InformationSubject Code: CLWM4100Subject Name: Taxation LawAssessment Title: Assessment 3Assessment Type: Individual Assignment and Video presentationWeighting: 20 %Total Marks: 20Submission:...This assessment task involves selecting a mathematics task and deconstructing it in terms of its relevance to the curriculum and big ideas of number or algebra and for fostering mathematical thinking and...**Show All Questions**