Homework answers / question archive / Shelly Cashman Excel 2019 | Modules 8-11: SAM Capstone Project 1a Porter 360 ANALYZE DATA AND SOLVE PROBLEMS GETTING STARTED Open the file SC_EX19_CS8-11a_FirstLastName_1

Shelly Cashman Excel 2019 | Modules 8-11: SAM Capstone Project 1a Porter 360 ANALYZE DATA AND SOLVE PROBLEMS GETTING STARTED Open the file SC_EX19_CS8-11a_FirstLastName_1

MS Excel

Share With

---

Shelly Cashman Excel 2019 | Modules 8-11: SAM Capstone Project 1a

Porter 360

ANALYZE DATA AND SOLVE PROBLEMS

• GETTING STARTED

• Open the file SC_EX19_CS8-11a_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_CS8-11a_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm 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_EX19_CS8-11a_FirstLastName_2.xlsm 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.
• Click enable content at the top, if required.
• To complete this project, you need to add the Developer tab. If this tab does not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the Developer check box, and click OK.
• To complete this project, you need to add the Power Pivot tab to the ribbon. From the File tab, click the Options button. In the Data options section of the Data tab, click the checkbox next to Enable Data Analysis add- ins: Power Pivot, Power View, and 3D Map, and click OK.
• To complete this project, you need to add the Analyze group to the Data tab on the ribbon. From the File tab, click the Options button. In the Add-ins section, use the dropdown menu to select the the Excel Add-ins and click Go... In the dialog box, click the checkboxes next to Analysis ToolPak and Solver Add-in, and click OK.

• PROJECT STEPS

1. Eva Delgado is a sales manager for Porter 360, an electronics firm in Reno, Nevada, that sells video doorbells and other smart home products. She is using an Excel workbook to analyze the company's sales and related data, and asks for your help in creating advanced types of charts and PivotTables and to automate parts of the workbook for others in the Sales Department.
   Go to the Survey worksheet, which is a form sales representatives can send to potential clients and collect information about them. Add an eye-catching title to the worksheet as follows:
   1. Insert WordArt using the Fill: Red, Accent color 1; Shadow WordArt style.
   2. Type Porter 360 Client Survey as the WordArt text.
   3. Change the Text Fill color to Aqua, Accent 5.
   4. Change the font size to 40 point.
   5. Move the WordArt so that it is centered in the top of the form in the range C2:I3.
2. Eva wants to make it easy for clients to contact the company and find information about products. Add links to the form as follows:
   1. In cell J6, add a link to the www.porter.example.com website.
   2. In cell J7, add a link to the info@porter.example.com email address.
   3. Use Contact Porter 360 as the ScreenTip for the email link.
3. Eva asks you to finish automating the form, which contains sample data for testing. In cell D23, enter a formula without using a function that displays the client's city from the named range City.
4. Add a sixth option to the "How did you hear about Porter 360 products and services?" section as follows:
   1. In cell H12, insert an Option Button (Form Control).
   2. Edit the option button text to use the Website to replace the placeholder text.
   3. Format the new option button to link it to cell $I$24 (if necessary).
   4. Format the option button to use 3-D shading.
   5. Position the new option button so that its top and bottom align with the Social media option button to its left, and that its left aligns with the TV ad option button above it.
5. Add a check box to the section titled "Which products do you want to know more about?" as follows:
   1. In cell G15, insert a Check Box (Form Control).
   2. Edit the check box text to use Video doorbell to replace the placeholder text.
   3. Format the new check box to link it to cell $L$23.
   4. Format the check box to use 3-D shading.
   5. Position the Video doorbell check box so that its box aligns with the top of the Security lights check box to its left.
6. Eva created a macro in Visual Basic named ClearData that clears the form for a new client. Add a button to run the macro as follows:
   1. In cell G17, insert a Button (Form Control) to the right of the Save Data button.
   2. Assign the ClearData macro to the new button.
   3. Edit the button to use Clear to replace the placeholder text.
   4. Format the new button to change the height to 0.3" and the width to 1".
   5. Position the Clear button to align with the top and bottom of the Save Data button on its left.
7. Eva asks you to change the format of the Save Data button so that it matches the Clear button. Change the format as follows:
   1. Edit the button to display Save as the button text.
   2. Change the font to 11 point Lucida Grande (Body).
8. Test the form as follows to make sure it works as Eva intended:
   1. Use the Save button to run the SaveData macro.
   2. Unhide the Client Information worksheet to verify that it contains the data from the Survey worksheet.
   3. Hide the Client Information worksheet.
9. Go to the Sales Comparison worksheet, which contains a table named Top_Sellers with a few errors. Correct the errors as follows:
   1. Use any error-checking method to determine the source of the error in cell E12, which should calculate the average sales in Year 1.
   2. Correct the error by editing the formula in cell E12.
   3. Use the Trace Precedents arrows to find the source of the #VALUE! error in cell G13.
   4. Correct the formula in cell G13, which should subtract the total Year 1 Sales from the total Year 2 sales.
   5. Remove the trace arrows, if necessary.
10. The line chart in the range C15:G29 shows gross monthly sales in Year 2. Eva wants to forecast the trend for the next two periods.
    Add a Linear Forecast trendline to the line chart.
11. The Research_Expenses table in the range I3:K15 compares the amount of research and development (R&D) expenses to sales in Year 2. Eva asks you to create a chart that shows the relationship between the expenses and the sales.
    1. Insert a Scatter chart that shows the relationship between the R&D expenses (range J3:J15) and the sales (range K3:K15).
    2. Resize and position the Scatter chart so that it covers the range I16:M29.
    3. Use R&D and Sales as the chart title.
12. Eva finds that the data points are clustered too close together. She asks you to make the Scatter chart easier to interpret by moving them farther apart.
    Change the Minimum bounds of the horizontal axis to 3000 to allow more space for the data points on the chart.
13. Go to the Orders worksheet, which contains order details for the month of May in a table named Orders. Eva wants to make sure that anyone entering the product information for other months enters the correct product IDs.
    Add data validation to the range C4:C30 as follows:
    1. Set a data validation rule for the range C4:C30 that allows only data from the list in the range H4:H11. (Hint: Select the range using the dropdown.)
    2. Add an Input Message using Product ID as the Input Message title and the following text as the Input message: Enter the ID for this product.
    3. Add an Error Alert using the Stop style, Product ID Error as the Error Alert title, and the following text as the Error message: The Product ID is incorrect.
14. Identify the invalid data in the worksheet and correct the entry as follows:
    1. Circle the invalid data in the worksheet.
    2. Use the DB100 as the correct Product ID for the invalid data.
    3. Clear any remaining errors in the worksheet.
15. Eva wants to display the payments customers made in each of five regions where Porter 360 does business.
    Insert a recommended PivotTable based on the Orders table as follows:
    1. Insert the Sum of Payment by Region recommended PivotTable.
    2. Use May Sales by Region as the name of the new worksheet.
    3. Apply the Sky Blue, Pivot Style Medium 13 style to the PivotTable.
    4. Add a second copy of the Payment field to the Values area of the Field List, and then change its summary function to Average so that Eva can compare the average payments to the totals.
    5. Change the number format of the two value fields to Currency with 2 decimal places and the $ symbol.
    6. Use Total Sales as the column heading in cell B3, and use Average Sales as the column heading in cell C3.
16. Insert a PivotChart based on the new PivotTable to help Eva visualize the data, as follows:
    1. Insert a Combo PivotChart based on the PivotTable in the May Sales by Region worksheet.
    2. Display the Total Sales as a Clustered Column chart and the Average Sales as a Line chart.
    3. Include a secondary axis for the Average Sales data.
    4. Change the PivotChart colors to Monochromatic Palette 5.
    5. Resize and position the chart so it covers the range A12:F24.
    6. Hide all the field buttons to allow more room for the data.
    7. Display the legend at the top of the PivotChart.
17. Return to the Orders worksheet. In the range H14:I18, Eva wants to list the total order amounts for the five regions where Porter 360 does business. Extract this information from the PivotTable on the Sales by Region worksheet as follows:
    1. In cell I14, use the GETPIVOTDATA function to display the total order amount for the Midwest from the PivotTable on the May Sales by Region worksheet (cell B4). (Hint: Type an equal sign in cell I14 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.)
    2. In cell I15, use the GETPIVOTDATA function to display the total order amount for the Northeast from the PivotTable on the May Sales by Region worksheet (cell B5).
    3. In cell I16, use the GETPIVOTDATA function to display the total order amount for the Northwest from the PivotTable on the May Sales by Region worksheet (cell B6).
    4. In cell I17, use the GETPIVOTDATA function to display the total order amount for the Southeast from the PivotTable on the May Sales by Region worksheet (cell B7).
    5. In cell I18, use the GETPIVOTDATA function to display the total order amount for the Southwest from the PivotTable on the May Sales by Region worksheet (cell B8).
18. Eva also wants to analyze May sales by product type. Create another PivotTable as follows:
    1. Create a PivotTable based on the Orders table.
    2. Place the PivotTable on a new worksheet, and then use May Sales by Product Type as the name of the worksheet.
    3. Use MaySales as the name of the PivotTable.
    4. Display the Product Type and then the Product ID fields as row headings.
    5. Display the Payment field as the values.
    6. Change the Number format of the Sum of Payment values to Currency with 0 decimal points and the $ sign.
    7. Apply the Sky Blue, Pivot Style Medium 13 style to match the other PivotTable in the workbook.
19. Format the MaySales PivotTable to reduce clutter, as follows:
    1. Change the layout to show the PivotTable in Tabular form.
    2. Hide the field headers.
20. Porter 360 is considering whether to raise the price of each product by 10 percent. Add a calculated field to the MaySales PivotTable to show this increase as follows:
    1. Create a calculated field using Price as its name.
    2. The formula should multiply the Payment field value by 0.1, and then add the result to the Payment field value to calculate the increased price.
    3. Use Increased Price in cell D3 as the column heading for the calculated field.
    4. Use Current Price in cell C3 as the column heading for Payment field values.
    5. Resize columns C and D to their best fit.
21. Add a slicer to the MaySales PivotTable as follows to make it easy for Eva to filter the data:
    1. Add a slicer based on the Order Source field.
    2. Resize and position the slicer so it covers the range E4:F10.
    3. Change the slicer style to Sky Blue, Slicer Style Light 5.
    4. Use the slicer to filter the PivotTable to show only Hardware store data.
22. Eva also wants to create a PivotTable that includes data about sales representatives and their sales. She already added the tables containing this data to the Data Model. Eva wants to list each sales rep by name along with their region, number of products sold, and total amount of sales. Create a PivotTable that displays this information as follows:
    1. Use Power Pivot to create a PivotTable on a new worksheet, using Sales Reps as the name of the worksheet.
    2. Display the LastName field values from the Query – SalesReps data source as row headings.
    3. Display the Region field values from the Query – SalesReps data source as column headings.
    4. Add the TotalSales field from the Query - Sales data source to the Values area to sum the field values.
23. To properly combine data from the Sales and SalesReps tables, use the Power Pivot for Excel window to create a relationship between the Sales and SalesReps tables using the SalesRepID column to relate the tables.
24. Go to the Installations worksheet. The Installations table in the range A4:D30 lists the date a customer requested an installation, the date of the installation, and the number of days between the request and the final installation of a Porter 360 device. Eva wants to know how many products were installed in the periods listed in the range F4:F7.
    Insert a histogram to provide this information for Eva, as follows:
    1. Use the Data Analysis tool to create a histogram.
    2. Use the days between a request and installation (range D4:D30) as the input range.
    3. Use the Bin list (range F4:F7) as the bin range.
    4. Use cell A32 as the output range.
    5. Show a cumulative percentage and chart output in the histogram.
25. Modify the Histogram chart to incorporate it into the worksheet and display the data clearly, as follows:
    1. Resize and position the Histogram chart so it covers the range D32:L48.
    2. Change the chart layout to Layout 5 to include a data table with the chart.
26. The Accessories table in the range H3:K13 shows the sales for three categories of accessories, with each category divided into products and then into models. Eva wants to display these hierarchies of information in a chart.
    Insert a Sunburst chart to display the hierarchies for Eva as follows:
    1. Insert a Sunburst chart based on the accessories data in the range H3:K13.
    2. Resize and position the chart so it covers the range E14:L30.
    3. Use Sales of Accessories as the chart title.
    4. Apply Style 5 to the chart to include a legend and dark text.
27. Go to the Income Analysis worksheet, which analyzes revenue of the Doorbell Cam 100 based on units sold, variable expenses, and fixed expenses. Eva has already created a scenario named Expansion that calculates net income if Porter 360 increases the number of units sold, which also involves increasing related expenses, while maintaining the current price. She also wants to calculate net income if the price is raised to $209, which would reduce the number of units sold.
    Add a new scenario to calculate the net income based on raising the unit price as follows:
    1. Use Raise Price as the scenario name.
    2. Use the revenue data and fixed expenses (range B5:B6 and B18:B21) as the changing cells.
    3. Enter cell values for the Raise Price scenario as shown in bold in Table 1.

 

Cell	Value
Units_sold (cell B5)	1400
Price_per_unit (cell B6)	209
Payroll (cell B18)	61,456
Shipping_and_distribution (cell B19)	11,000
Storage (cell B20)	10,000
Miscellaneous (cell B21)	12,000

 

28. Compare the net income based on the current values and the two scenarios as follows:
    1. Create a Scenario Summary report using the net income (cell B27) as the result cell to show how the net income changes depending on the revenue and expense changes.
    2. Use Income Scenario Report as the name of the worksheet containing the report.
29. Go to the Trade Shows worksheet, which lists the special promotions the company runs when it attends trade shows in three locations. The company has a budget of $98,000 for the promotions, and Eva wants to know how many promotions to run at each trade show to stay within budget.
    Use Solver to find this information as follows:
    1. Use the total cost of the promotions (cell E11, named Total_promotion_cost) as the objective cell in the Solver model, with the goal of matching the value of 98000 in that cell.
    2. Use the number of promotions run in the three locations (range B9:D9) as the changing variable cells.
    3. Determine and enter the constraints based on the information provided in Table 2.
    4. Use Simplex LP as the solving method to find a global optimal solution.
    5. Save the Solver model in cell A13.
    6. Solve the model, keeping the Solver solution.

 

Constraint	Cell or Range
The number of promotions run is greater than or equal to 0	B9:D9
The number of promotions run is an integer	B9:D9
The company can run up to 4 promotions in Chicago	Chicago_promotions (cell B9)
The company can run up to 4 promotions in Las Vegas	Las_Vegas_promotions (cell C9)
The company can run up to 4 promotions in Orlando	Orlando_promotions (cell D9)

 

30. Eva wants to document the answer Solver found, including the constraints and a list of the values Solver changed to solve the problem. Produce an Answer report for the Solver model as follows:
    1. Solve the model again, this time choosing to produce an Answer report.
    2. Use Trade Show Answer Report as the name of the worksheet containing the Answer report.

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.