Shelly Cashman Excel 2019 | Module 8: SAM Project 1b | Instructions_SC_EX19_8b | Delgado Designs
#Module8 #Module8SAMProject1b #SC_EX19_8b #DelgadoDesigns
Contact Me:
WhatsApp : +923025441145
Email : myitlab458@gmail.com
Direct WhatsApp Link: [ Ссылка ]
#Shelly Cashman Excel 2019 | Module 8: SAM Project 1b
#Shelly Cashman Excel 2019
#Cashman Excel 2019
#Excel 2019
#Module 8: SAM Project 1b
#Module 8
#SAM Project 1b
#Project 1b
#Module 8: SAM Project
#SAM Project
#Project
#Instructions_SC_EX19_8b
#SC_EX19_8b
#EX19_8b
#Delgado Designs
PROJECT STEPS
1. Selena Delgado creates and sells jewelry to stores and gift shops in Denver, Colorado, and nearby cities. She is using an Excel workbook to analyze sales from the previous year and asks for your help in creating advanced types of charts and PivotTables to provide an overview of her jewelry, buyers, and orders.
Go to the Sales worksheet, which contains a column chart in the range F17:K30 showing sales of each jewelry item from July to December. Selena wants to create a similar chart showing the sales data from January to June.
Provide a chart showing the January to June sales data as follows:
a. Copy the July-Dec chart.
b. Paste the chart in cell A17.
c. Select the copied chart and drag the blue outline from the range G3:G15 to the range F3:F15 to change the data shown in the chart.
2. The Promos table in the range I3:K15 compares the expenses for promotions spent in the previous year and the number of jewelry sets sold. Selena asks you to create a chart that shows the relationship between the amount spent and the jewelry sets sold.
a. Insert a Scatter chart that shows the relationship between the expense amount (range J3:J15) and the number of jewelry sets sold (range K3:K15).
b. Resize and position the scatter chart so that it covers the range L2:S15.
c. Use Promo Expense and Sets Sold as the chart title.
3. Selena wants to analyze the relationship between the expense amounts and the jewelry sets sold.
Add a Linear Forecast trendline to the scatter chart.
4. Go to the Buyers worksheet, which contains details about store buyers in a table named Buyers. Selena wants to display the amount billed to buyers in each of five cities where she sells her jewelry.
Insert a recommended PivotTable based on the Buyers table as follows:
a. Insert the Sum of Billed by Location recommended PivotTable. [Mac Hint: Use the Location field in the Rows area and the Billed field in the Values area. Be sure to uncheck the Buyer ID and Item ID check boxes to deselect and remove them from the PivotTable, and drag the Location Field from the Columns area to the Rows area.]
b. Use Billed by Location as the name of the new worksheet.
c. Apply the Coral, Pivot Style Medium 12 style to the PivotTable.
d. Add a second copy of the Billed field to the Values area of the Field List, and then change its summary function to Average so that Selena can compare the average billing amounts to the totals.
e. Change the number format of the two value fields to Currency with 0 decimal places and the $ symbol.
f. Use Total Billed as the column heading in cell B3, and use Average Billed as the column heading in cell C3.
5. Insert a PivotChart based on the new PivotTable as follows to help Selena visualize the data:
a. Insert a Combo PivotChart based on the Billed by Location PivotTable. [Mac Hint: Insert a Clustered Column Chart.]
b. Display the Total Billed as a Clustered Column chart and the Average Billed as a Line chart. [Mac Hint: Select the "Average Billed" data series, select Change Chart Type on the Design tab, point to Combo, and then select Clustered Column - Line.]
c. Include a secondary axis for the Average Billed data. [Mac Hint: Select the "Average Billed" data series/line, control+click the series/line, select Format Data Point, and then select the Secondary Axis radio button.]
d. Hide the Field List so that you can format the value axis of the chart.
e. Change the maximum bounds for the value axis on the left to 800.0.
f. Change the PivotChart colors to Monochromatic Palette 4.
g. Resize and position the chart so that it covers the range A11:G25.
h. Display the Field List again.
