Excel

Shelly Cashman Excel 2016 | Module 8: SAM Project 1b

Shelly Cashman Excel 2016 | Module 8: SAM Project 1b

Galanos & Company

WORKING WITH TRENDLINES, PIVOTTABLES, PIVOTCHARTS, AND SLICERS

GETTING STARTED

Open the file SC_EX16_8b_ FirstLastName_1.xlsx , available for download from the SAM website.

Save the file as SC_EX16_8b_ FirstLastName_2.xlsx by changing the “1” to a “2”.

0. If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the file SC_EX16_8b_ FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

You are an assistant to Dmitri Galanos, the owner of Galanos & Company in Tampa, Florida. The firm specializes in residential and commercial construction, renovation, and remodeling. He wants to review the sales data from the last four quarters to see how best to expand the company’s construction projects. He asks for your help in creating line charts, PivotTables, and PivotCharts.

Go to the 2020 Project Totals worksheet. Create a Line with Markers chart based on the range A3:E4. Move and resize the chart so that the upper-left corner is within cell A7 and the lower-right corner is within cell E20.

Format the line with markers chart as described below:

a. Use 2020 Project Totals as the title of the chart.

b. Add a Primary Vertical Axis title to the chart. Use Sales as the Primary Vertical Axis title.

c. Select the Quarter 3 data point on the chart, then change the size of the marker to 8 and the fill color of the marker to Gold, Accent 3 (7th column, 1st row in the Theme Colors palette).

Dmitri wants to review how the sales of the commercial projects might grow over the next two quarters by adding a trendline to his sales data chart.

Go to the Commercial Chart - Trend worksheet. Add a linear trendline to the chart with the following options:

d. Set the trendline to forecast forward 2 periods.

e. Display the R-squared value on the chart.

Dmitri categorizes the company’s projects in three different sizes: small, large, and medium, depending on the number of crew members needed. Dmitri wants to display how the sales of different project sizes have changed over time.

Go to the 2020 Project Size Chart worksheet. Remove the Average Base Cost series from the PivotChart.

Format the PivotChart as described below:

f. Change the format of all four data series to Full Pyramid.

g. Change the fill color of the Back Wall of the chart to Brown, Accent 6, Lighter 60% (10th column, 3rd row in the Theme Colors palette) and the fill color of the Side Wall of the chart to Brown, Accent 6, Lighter 80% (10th column, 2nd row in the Theme Colors palette).

Go to the 2020 Project Sales worksheet. Select cell A4 and create a PivotChart & PivotTable on a new worksheet as described below:

h. Use Project Size Pivot as the name of the worksheet.

i. If necessary, move the worksheet directly after the 2020 Project Size Chart worksheet.

j. Add the Crew Size field to the Axis (Category) area of the PivotChart field task pane.

k. Add the Project Type field to the Values area of the PivotChart field task pane. ( Hint: The field name will be updated to Count of Project Type.)

l. Move the PivotChart so that the upper-left corner is located within cell A9 and the lower-right corner is located within cell G23.

m. Use Projects by Size as the title of the PivotChart.

n. Add a Primary Vertical Axis title to the PivotChart. Use Number of Crew Members as the Primary Vertical Axis title.

o. Add a Primary Horizontal Axis title to the PivotChart. Use Project Size as the Primary Horizontal Axis title.

Dmitri wants to analyze the location of the company’s projects in Tampa. Go to the 2020 Customers worksheet and select cell A4. Create a blank PivotTable report in a new worksheet. ( Hint: The PivotTable report should be based on the table named Customers.)

1. Use Customer Location Report as the name of the worksheet.

q. Add the Location and Project Type fields (in that order) to the Rows area of the PivotTable Fields task pane.

r. Add the Base Cost field to the Values area of the PivotTable Fields task pane. ( Hint: The name of the field will be updated to Sum of Base Cost.)

s. Apply the Currency number format, with 0 decimal places and $ as the symbol, to the Sum of Base Cost field.

t. Add the Crew Size field to the Filters area of the PivotTable Fields task pane. Use the filter to display only records for L in the PivotTable report.

Return to the 2020 Customers worksheet. Select cell A4 and create a PivotChart directly from the data in that table in a new worksheet as follows:

u. Use Project Size PivotChart as the name of the worksheet.

v. If necessary, move the worksheet directly after the 2020 Customers worksheet.

w. Add the Crew Size field to the Legend (Series) area of the PivotChart Fields task pane.

x. Add the Season field to the Axis (Category) area of the PivotChart Fields task pane.

y. Add the Project Type field to the Values area of the PivotChart Fields task pane. Use Projects by Season as the title of this field.

z. Move and resize the PivotChart so that the upper-left corner is located within cell G2 and the lower-right corner is located within cell M15.

Dmitri already created a PivotTable to determine how many sales were associated with each location in Tampa. He asks you to format the chart to make it easier to read and determine the average sales per customer at each location.

Go to the Location Report worksheet. Refresh the PivotTable report data. ( Hint: After refreshing the PivotTable data, the Summer-Fall value for Northwest Bath should be 700.)

Apply the Pivot Style Medium 7 to the PivotTable report. ( Hint: Depending on your version of Office, the PivotTable style may appear as Tan, Pivot Style Medium 7.)

Modify the Sum of Base Cost field (now labeled “Projects by Location”) as follows:

aa. Summarize the values in the column using the AVERAGE function.

ab. Use Average Project Sales per Season as the name of the field.

Modify the number format of the Summer-Fall, Winter-Spring, and Grand Total fields so that the values appear in the Accounting number format, with 0 decimal places and $ as the symbol.

Change the Report Layout to display the report in Outline Form.

Switch to the Sales Total Report worksheet. To find how much each project earns per day, add a new calculated field to the end of the PivotTable report as follows:

ac. For the field, create a formula without using a function that divides the Base Cost field by the Days field. ( Hint: The calculated field will automatically be added to your PivotTable.)

ad. Use Project Revenue per Day as the name of the calculated field. ( Hint: The name will appear as “Sum of Project Revenue per Day” in the PivotTable.)

ae. Apply the Accounting number format, with 0 decimal places and the $ symbol, to the field.

Go to the Project Type Report worksheet. Format the Season Slicer as follows:

af. Format the Slicer using the Slicer Style Light 2 style. ( Hint: Depending on your version of Office, the Slicer style may appear as Light Green, Slicer Style Light 2.)

ag. Use the Slicer to filter the PivotTable report to display only data for the Summer-Fall field.

Add another Slicer to the PivotTable report based on the Project Type field, then complete the following actions:

ah. Resize and reposition the Project Type Slicer so that the upper-left corner is located within cell D9 and the lower-right corner is located within cell F22.

ai. Format the Slicer using the Slicer Style Light 2 style. ( Hint: Depending on your version of Office, the Slicer style may appear as Light Green, Slicer Style Light 2.)

aj. Use the Slicer to filter the PivotTable report to display only data for Bath and Kitchen fields. ( Hint: The PivotTable should already be filtered using the Season Slicer.)

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: 2020 Project Totals Worksheet

Final Figure 2: Commercial Chart - Trend Worksheet

Final Figure 3: 2020 Project Size Chart Worksheet

Final Figure 4: Project Size Pivot Worksheet

Final Figure 5: 2020 Project Sales Worksheet

Final Figure 6: Customer Location Report Worksheet

Final Figure 7: 2020 Customers Worksheet

Final Figure 8: Project Size PivotChart Worksheet

Final Figure 9: Location Report Worksheet

Final Figure 10: Sales Total Report Worksheet

Final Figure 11: Project Type Report Worksheet

2

image3.png

image4.png

image5.png

image6.png

image7.png

image8.png

image9.png

image10.png

image11.png

image12.png

image1.png

image2.png