Shelly Cashman Excel 2019 | Modules 8-11: SAM Critical Thinking Capstone Project 1c Porter 360 ANALYZE DATA AND SOLVE PROBLEMS   GETTING STARTED Open the file SC_EX19_CT_CS8-11c_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Shelly Cashman Excel 2019 | Modules 8-11: SAM Critical Thinking Capstone Project 1c

Porter 360

ANALYZE DATA AND SOLVE PROBLEMS

 

• GETTING STARTED

• Open the file SC_EX19_CT_CS8-11c_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_CT_CS8-11c_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_CT_CS8-11c_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.
   1. Insert WordArt using the Fill: Red, Accent color 1; Shadow WordArt style. Type Porter 360 Client Survey as the WordArt text.
   2. Move the WordArt so that it is centered in the top of the form in the range C2:I3.
   3. Change the Text Fill color to Aqua, Accent 5, and the font size to 40 point.
2. Eva wants to make it easy for clients to contact the company and find information about products. Add links to the form.
   1. Select the cell labeled "Porter website", add a link to the www.porter.example.com website.
   2. In the cell below, add a link to the info@porter.example.com email address. 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). Edit the option button text to use the Website to replace the placeholder text.
   2. Format the new option button to link it to cell $I$24 (if necessary), and format the option button to use 3-D shading.
   3. Position the new option button so that its top aligns 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). Edit the check box text to use Video doorbell to replace the placeholder text.
   2. Format the new check box to link it to cell $L$23, and format the check box to use 3-D shading.
   3. 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. Edit the button to use Clear to replace the placeholder text, and assign the ClearData macro to the new button.
   2. Format the new button to change the height to 0.3" and the width to 1". Position the Clear button to align with the top of the Save Data button on its left.
7. Eva asks you to format the Save Data and Clear buttons so that they match. Change the format as follows:
   1. Edit the Save Data button to display Save as the button text.
   2. Change the font of both buttons 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.
   1. Use any error-checking method to determine the source of the error in calculating the average sales in Year 1, and correct the error.
   2. Use the Trace Precedents arrows to find the source of the #VALUE! error in the total of the Difference column. Correct the formula, which should subtract the total Year 1 Sales from the total Year 2 sales.
   3. Remove the trace arrows, if necessary.
10. The line chart 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 on the right 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 and the sales.
    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.
    1. Set a data validation rule for the data in the Product ID column that allows only data from the list of product ids in column H.
    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: Product ID is incorrect.
14. Identify and correct the invalid data in the worksheet:
    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 custom name of the Sum of Payment column, and use Average Sales as the custom name for the Average of Payment column.
16. Insert a PivotChart based on the new PivotTable to help Eva visualize the data.
    1. Insert a Combo PivotChart based on the PivotTable in the May Sales by Region worksheet. Display the Total Sales as a Clustered Column chart and the Average Sales as a Line chart.
    2. Include a secondary axis for the Average Sales data. Change the PivotChart colors to Monochromatic Palette 5, and resize and position the chart so it covers the range A12:F24.
    3. Hide all the field buttons if necessary to allow more room for the data, and display the legend at the top of the PivotChart.
17. Return to the Orders worksheet. In the Orders per Region section, 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.
    In the appropriate cell, retrieve the total sales amount for the Midwest from the PivotTable on the May Sales by Region worksheet. Retrieve the data for the remaining regions to complete the section.
18. Eva also wants to analyze May sales by product type.
    1. Create a PivotTable based on the Orders table. Place the PivotTable on a new worksheet, and then use May Sales by Product Type as the name of the worksheet, and use MaySales as the name of the PivotTable.
    2. Display the Product Type and then the Product ID fields as row headings, and the Payment field as the values. Change the Number format of the Sum of Payment values to Currency with 0 decimal points and the $ sign.
    3. 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.
    Change the layout to show the PivotTable in Tabular form, and 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.
    1. Create a calculated field using Price as its name. 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.
    2. Enter Increased Price as the column heading for the calculated field, and enter Current Price as the column heading for Payment field values. Resize columns C and D to their best fit.
21. Add a slicer to the MaySales PivotTable to make it easy for Eva to filter the data.
    Add a slicer based on the Order Source field. Resize and position the slicer so it covers the range E4:F10. Change the slicer style to Sky Blue, Slicer Style Light 5, and 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.
    Use Power Pivot to create a PivotTable on a new worksheet, using Sales Reps as the name of the worksheet. Display the LastName field values from the Query – SalesReps data source as row headings, the Region field values from the Query – SalesReps data source as column headings, and 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 columns A through D 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 column F. Insert a histogram to provide this information for Eva.
    Use the Data Analysis tool to create a histogram. Use the data in the days column as the input range, the Bin list as the bin range, and cell A32 as the output range. 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.
    Resize and position the Histogram chart so it covers the range D32:L48. Change the chart layout to Layout 5 to include a data table with the chart.
26. The Sales of Accessories table 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 based on the entire Accessories chart. Resize and position the chart so it covers the range E14:L30. Use Sales of Accessories as the chart title, and 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.
    Use Raise Price as the scenario name. Use the revenue data and fixed expenses as the changing cells. Enter cell values for the Raise Price scenario as shown in bold in Table 1.

Table 1: Cell Values for the Raise Price Scenario

 

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.
    Create a Scenario Summary report using the net income as the result cell to show how the net income changes depending on the revenue and expense changes. 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.
    1. Use the total cost of the promotions as the objective cell in the Solver model, with the goal of matching the value of 98000 in that cell. Use the number of promotions run in the three locations as the changing variable cells.
    2. Determine and enter the constraints based on the information provided in Table 2.
    3. Use Simplex LP as the solving method to find a global optimal solution. Save the Solver model in cell A13, and then solve the model, keeping the Solver solution.

Table 2: Solver Constraints

 

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.
    Solve the model again, this time choosing to produce an Answer report. 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.