Solution for QID #49536: Shelly Cashman Excel 2016 | Module 8: SAM Mac Project 1a | StudyHelpMe

Shelly Cashman Excel 2016 | Module 8: SAM Mac Project 1a Peterbu Farm Share WORKING WITH TRENDLINES, PIVOTTABLES, PIVOTCHARTS, AND SLICERS GETTING STARTED ? Open the file SC_EX16_Mac_8a_ FirstLastName _1.xlsx , available for download from the SAM website. ? Save the file as SC_EX16_Mac_8a_ FirstLastName _2.xlsx by changing the “1” to a “2”. o 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_Mac_8a_ FirstLastName _2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. ? If you see a Message Bar with a security warning at the top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file. PROJECT STEPS 1. Marcello Peterbu coordinates the farm share program for his family’s farm. Besides the traditional vegetable and fruit shares, the Peterbu farm share has also been expanded to include meat and dairy options for its members. Marcello is reviewing the farm share sales data from the last few years to see how best to expand the program. He first wants to create a line chart of the total sales from the farm shares over a four year period. Go to the 2018-2021 Share 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 in cell H24. 2. Format the line with markers chart as described below: a. Use 2018-2021 Farm Share Sales as the title of the chart. b. Add a Primary Vertical Axis title to the chart. Use Dollars (U.S.) as the Primary Vertical Axis title. c. Add a Primary Horizontal Axis title to the chart. Use Sales Year as the Primary Horizontal Axis title. d. Select the 2020 Sales data point on the chart, then change the size of the marker to 10 and the fill color of the marker to Green, Accent 6 (10 th column, 1 st row in the Theme Colors palette). 3. Marcello now wishes to review how the sales of the non-produce shares might grow over the next two years by adding a trendline to his sales data chart. Go to the Non-Produce Chart - Trends worksheet. Add a linear trendline to the chart with the following options: a. Set the trendline to forecast forward 2 periods. b. Display the R-squared value on the chart. ( Hint : Depending on your version of Office, the R-squared value may contain a different number of decimal places.) 4. Peterbu farm shares come in three different sizes: small, large, and regular (for those shares without different size options). Marcello wants to visually display how the sales of different share sizes have changed over time. Go to the 2018-2021 Share Size Chart worksheet. Remove the Average Share Cost series from the chart. 5. Format the chart as described below: a. Change the format of all four data series to Cylinder . b. Change the fill color of the Back Wall of the chart to Blue, Accent 1, Lighter 80% (5 th column, 2 nd row in the Theme Colors palette) and fill color of the Side Wall of the chart to Orange, Accent 2, Lighter 80% (6 th column, 2 nd row in the Theme Colors palette). 6. Go to the 2018-2021 Share Sales worksheet. Select cell A4 and create a PivotTable and a chart on a new worksheet as described below: a. Use Share Size Pivot as the name of the worksheet. b. If necessary, move the worksheet directly after the 2018-2021 Share Size Chart worksheet. c. Add the Share Size field to the Rows area of the PivotTable field task pane. d. Add the Share Type field to the Values area of the PivotTable field task pane. ( Hint : The field name will be updated to Count of Share Type.) e. Insert a Clustered Column chart based on the range A4:B6. ( Hint : Depending on your version of Office, your chart may appear with field buttons. If so, hide the field buttons, remove the legend, and continue.) f. Move the chart so that the upper left corner is located within cell A9 and the lower right corner is located within cell G23. g. Use Share Options by Size as the title of the chart. h. Add a Primary Vertical Axis title to the chart. Use Number of Share Options as the Primary Vertical Axis title. i. Add a Primary Horizontal Axis title to the chart. Use Share Size as the Primary Horizontal Axis title. 7. Customers can pick up their weekly farm shares at designated locations in town. If Marcello decides to expand his program, he needs to know how each location will be impacted by farm share pick up. Go to the 2021 North Region Participants worksheet and select cell A4. Create a blank PivotTable report in a new worksheet. ( Hint : The PivotTable report should be based on the table NorthRegion2021.) a. Use North Pickup Location Report as the name of the worksheet. b. Add the Pickup Location and Share Type (in that order) to the Rows area of the PivotTable Fields task pane. c. Add the Share Cost field to the Values area of the PivotTable Fields task pane. ( Hint : The name of the field will be updated to Sum of Share Cost.) d. Apply the Currency number format, with 0 decimal places and $ as the symbol, to the Sum of Share Cost field. e. Add the Share 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. 8. Go to the Size Chart worksheet. Update the worksheet as described below: a. Rename the worksheet using Season Size Chart as the new name of the worksheet. b. Select the range A2:D4 and insert a Clustered Column chart . Switch the rows and columns so that the horizontal axis is grouped by season. ( Hint : Depending on your version of Office, your chart may appear with field buttons. If so, hide the field buttons, do not switch the rows and columns, and continue from substep e.) c. Remove the chart title. d. Format the legend so that it is in the Right position. e. Move and resize the chart so that the upper left corner is located within cell G2 and the lower right corner is located within cell O18. 9. Marcello already created a PivotTable to determine how many sales were associated with each pickup location. He now wishes to format the table to make it easier to read and determine the average sales per participant at each location. Go to the Location Report worksheet. Refresh the PivotTable report data. ( Hint : After refreshing the PivotTable data, the value in cell D18 should be 17410.) 10. Apply the Pivot Style Medium 14 to the PivotTable report. ( Hint : Depending on your version of Office, the PivotTable style may appear as Light Green, Pivot Style Medium 14.) 11. Modify the Sum of Share Cost field as described below: a. Summarize the values in the column using the AVERAGE function. b. Use Average Sale per Share as the name of the field. 12. Modify the number format of the Total Share Sales field, so that the values display in the Accounting number format, with 0 decimal places and $ as the symbol. ( Hint : Depending on how you complete this action, the number format may appear as Custom instead of Accounting.) 13. Change the Report Layout so that the report is viewed in Outline Form . 14. Switch to the Sales Total Report worksheet. Each customer has purchased the same farm share package each year they have participated in the program. Add a new calculated field to the end of the PivotTable report to calculate the cumulative share sales for each customer as described below: a. For the field, create a formula without using a function that multiplies the Share Cost by the Years Participating . ( Hint : The calculated field will automatically be added to your PivotTable.) b. Use Cumulative Share Sales as the custom name of the calculated field. ( Hint : The name will appear as “Sum of Cumulative Share Sales” in the PivotTable.) c. Apply the Accounting number format, with 0 decimal places and the $ symbol, to the field. 15. Go to the Share Type Report worksheet. Format the Season Slicer as described below: a. Format the Slicer using the Slicer Style Light 6 . ( Hint : Depending on your version of Office, the Slicer style may appear as Light Green, Slicer Style Light 6.) b. Use the Slicer to filter the PivotTable report to display only data for the Summer-Fall field. 16. Add another Slicer to the Model PivotTable report based on the Share Type field, then complete the following actions: a. Resize and reposition the Share Type Slicer, so that the upper left corner is located within cell D10 and the lower right corner is located within cell G26. b. Format the Slicer using the Slicer Style Light 6 . ( Hint : Depending on your version of Office, the Slicer style may appear as Light Green, Slicer Style Light 6.) c. Use the Slicer to filter the PivotTable report to display only data for Fruit, Fruit & Vegetable, and Vegetable 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: 2018-2021 Share Totals Worksheet Final Figure 2: Non-Produce Chart - Trends Worksheet Final Figure 3: 2018-2021 Share Size Chart Worksheet Final Figure 4: Share Size Pivot Worksheet Final Figure 5: 2018-2021 Share Sales Worksheet Final Figure 6: North Pickup Location Report Worksheet Final Figure 7: Season Size Chart Worksheet Final Figure 8: Location Report Worksheet Final Figure 9: Sales Total Report Worksheet Final Figure 10: Share Type Report Worksheet