Shelly Cashman Excel 365

Shelly Cashman Excel 365 | Modules 8-11: SAM Critical Thinking Capstone Project C Pepi PEVs

ANALYZE DATA AND SOLVE PROBLEMS

September 22, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file SC_EX365_CT_CS8-11C_FirstLastName_1.xlsm as SC_EX365_CT_CS8-11C_FirstLastName_2.xlsm

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsm file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_CT_CS8-11C_FirstLastName_2.xlsm open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

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

3. Files downloaded from the website are safe and do not contain viruses, but due to a recent Microsoft policy update, macros in downloaded files are disabled by default. To complete this project, you will need to enable macros in the file. To enable macros on this file: o Open Windows File Explorer and go to the folder where you saved the file. Right-click the file and choose Properties from the context menu. At the bottom of the General tab, select the Unblock checkbox and select Apply, and then click OK.

4. 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.

5. 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.

6. 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. Samira Agarwal is a sales manager for Pepi PEVs, a company in Salem, Oregon, that sells personal electric vehicles (PEVs) such as electric scooters, carts, and bicycles. 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 Customer Survey worksheet, which is a form sales representatives can send to potential customers and collect information about them. Samira asks you to make it easy for customers to contact the company and find information about products. Add links to the form as follows:

a. In the first cell below the "Links" heading, add a link to the www.pepi.example.com website.

b. In the second cell below the "Links" heading, add a link to the info@pepi.example.com email address and use Contact Pepi as the ScreenTip.

2. In the cell containing the "Links" heading, add a comment to provide the following reminder to the sales representatives: You can change the link in cell J8 to your email address

3. Samira asks you to finish automating the form, which contains sample data for testing. In the appropriate cell in row 23, 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 Pepi products and services?" section as follows:

a. Insert an Option Button (Form Control) below the TV ad option button, and then edit the text to display Website as the label.

b. Format the new option button to link it to the same cell and use the same type of shading as the other option buttons in the section.

c. Position the Website 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 "Which products do you want to know more about?" section as follows:

a. Insert a Check Box (Form Control) in the cell between the Electric scooters and Hoverboards check boxes, and then edit the text to display Electric carts as the label.

b. Format the new check box to link it to the appropriate cell and use the same type of shading as the other check boxes in the section.

c. Position the Electric carts check box so that its box aligns with the top of the Electric scooters check box to its left.

6. Samira 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:

a. Insert a Button (Form Control) two cells to the right of the Save Data button.

b. Assign the ClearData macro to the new button.

c. Edit the text to display Clear on the button.

d. Change the height to 0.3" and the width to 1".

e. Align the top of the Clear button with the top of the Save Data button.

7. Samira asks you to change the format of the Save Data button so that it matches the Clear button. Change the format as follows:

a. Edit the text to display Save on the button.

b. Change the font to 11-point Gill Sans MT (Body).

8. Test the form as follows to make sure it works as Samira intended:

a. Use the Save button to run the SaveData macro.

b. Unhide the Customer Information worksheet to verify that it contains the data from the Customer Survey worksheet, and then hide the Customer Information worksheet.

9. Go to the Annual Sales worksheet, which contains a table named Top_Sellers with a few errors. Correct the errors as follows:

a. Use any error-checking method to determine the source of the #NAME? error for the cell that should calculate the average sales in Year 1, and then correct the error by editing the formula in the cell.

b. Use the Trace Precedents arrows to find the source of the #VALUE! error.

c. Correct the formula to show the difference between the Year 2 sales and the Year 1 sales, and then remove the trace arrows, if necessary.

10. The line chart shows gross monthly sales in Year 2. Samira wants to forecast the trend for the next two periods. Add a trendline to the line chart that shows values increasing or decreasing at a steady rate and forecasts two periods.

11. The Ad_Expenses table compares the amount of advertising expenses to sales in Year 2. Samira asks you to create a chart that shows the relationship between the expenses and the sales.

a. Insert a Scatter chart that shows the relationship between the ad expenses and the sales, including the column headings.

b. Modify the Scatter chart so that it covers the range J18:N30 and uses Ads and Sales as its title.

12. Samira 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 Maximum bounds of the horizontal axis to 18000 to allow more space for the data points on the chart.

13. Go to the Product Orders worksheet, which contains order details for the month of August in a table named Orders. Samira wants you to make sure that anyone entering the product information for other months enters the correct product IDs. Add data validation to the Product ID values as follows:

a. Set a data validation rule for the Product ID values that allows only data from the list of Product IDs in the table to the right of the Orders table.

b. 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.

c. 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. Circle the invalid data in the worksheet, use EH1015 as the correct Product ID for the invalid data, and then clear any remaining errors in the worksheet.

15. Samira wants you to display the payments customers made in each of four states where Pepi sells products. Insert a recommended PivotTable based on the Orders table as follows:

a. Insert the Sum of Payment by State recommended PivotTable using August Sales by State as the name of the new worksheet.

b. Apply the Ice Blue, Pivot Style Light 20 style to the PivotTable.

c. Add a second copy of the Payment field as a value in the PivotTable, and then change its summary function so that Samira can compare the average payments to the totals.

d. Change the number format of the two value fields to Currency with 2 decimal places and the $ symbol.

e. Use Total Sales as the heading of the column displaying sales totals, and use Average Sales as the heading of the column displaying sales averages.

16. Insert a PivotChart based on the new PivotTable to help Samira visualize the data, as follows:

a. Insert a PivotChart that includes the Total Sales as a Clustered Column chart, the Average Sales as a Line chart, and a secondary axis for the Average Sales data.

b. Change the PivotChart colors to Monochromatic Palette 5.

c. Hide all the field buttons to allow more room for the data, and then display the legend at the top of the PivotChart.

d. Resize and position the chart so it covers the range A10:E22.

17. Return to the Product Orders worksheet. In the Orders by State area, Samira wants to list the total order amounts for the four states where Pepi sells products. Extract this information from the PivotTable on the August Sales by State worksheet as follows:

a. In the appropriate cell in the Orders by State area, use the GETPIVOTDATA function to display the total order amount for Idaho from the PivotTable on the August Sales by State worksheet.

b. In the appropriate range, insert similar formulas that display the total order amounts for Oregon, Utah, and Washington, in that order, from the appropriate cells on the August Sales by State worksheet.

18. Samira also wants to analyze August sales by product type. Create another PivotTable as follows:

a. Create a PivotTable based on the Orders table.

b. Place the PivotTable on a new worksheet, and then use August Sales by Product as the name of the worksheet.

c. Use AugSales as the name of the PivotTable.

d. Display the Product Type and then the Product ID fields as row headings.

e. Display the Payment field as the values.

f. If necessary, change the Number format of the Sum of Payment values to Currency with 0 decimal points and the $ symbol.

g. Apply the Ice Blue, Pivot Style Light 20 style to match the other PivotTable in the workbook.

19. Format the AugSales PivotTable to make it easier to interpret. Change the layout to show the PivotTable in Tabular form, and hide the field headers.

20. Pepi PEVs is considering whether to raise the price of each product by 10 percent. Add a calculated field to the AugSales PivotTable to show this increase as follows:

a. Create a calculated field using Price as its name.

b. The formula should multiply the Payment field value by 10 percent, and then add the result to the Payment field value to calculate the increased price.

c. Use New Price as the column heading for the calculated field.

d. Use Current Price as the column heading for Payment field values.

21. Add a slicer to the AugSales PivotTable as follows to make it easy for Samira to filter the data:

a. Add a slicer based that lists the order sources.

b. Resize and position the slicer so it covers the range F4:H10.

c. Change the slicer style to Ice Blue, Slicer Style Light 5.

d. Use the slicer to filter the PivotTable to show only Online data.

22. Samira also wants you to create a PivotTable that includes data about sales representatives and their sales. She already combined the tables containing this data, and wants to list each sales rep by name along with their state, number of products sold, and total amount of sales. Create a PivotTable that displays this information as follows:

a. Use Power Pivot to create a PivotTable on a new worksheet, using Sales Reps as the name of the worksheet.

b. Display the LastName field values from the SalesReps data source as row headings, the State field values from the SalesReps data source as column headings, and add the TotalSales field from the Sales data source to the Values area to sum the field values.

c. Apply the Ice Blue, Pivot Style Light 20 style to match the other PivotTables in the workbook.

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 common column to relate the tables.

24. Go to the Shipments worksheet. The Shipments table lists the date a customer ordered a product, the date of the shipment, and the number of days between the order and the shipment. Samira asks you to show how many products were shipped in the periods listed in the Bins area. Insert a histogram to provide this information for Samira, as follows:

a. Use the Data Analysis tool to create a histogram.

b. Use the days between an order and a shipment as the input range and the Bin list as the bin range.

c. Display the output starting in the second cell below the Cust ID column in the Shipments table and show the 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:

a. Resize and position the Histogram chart so it covers the range G33:M48.

b. Change the chart layout to Layout 5 to include a data table with the chart.

26. The Accessories table shows the sales for three categories of accessories, with each category divided into products and then into model numbers. Samira wants you to display these hierarchies of information in a chart. Insert a Sunburst chart to display the hierarchies for Samira as follows:

a. Insert a Sunburst chart based on the accessories data and the table column headings.

b. Resize and position the chart so it covers the range G16:M31.

c. Use Accessories as the chart title and apply Style 4 to the chart to include a legend and lighter colors that reveal the text.

27. Go to the Income Analysis worksheet, which analyzes revenue of the Pepi XP e-bike based on units sold, variable expenses, and fixed expenses. Samira has already created a scenario named Expansion that calculates net income if the company increases the number of units sold and related expenses while maintaining the current price. She also wants to calculate net income if the price is raised to $900, 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:

a. Use Raise Price as the scenario name.

b. Use the Units sold and Price per unit revenue data and the data for the four fixed expenses as the changing cells.

c. Enter cell values for the Raise Price scenario as shown in bold in Table 1, noting that only the units sold and price per unit are different from the current values.

Table 1: Cell Values for the Raise Price Scenario

28. Compare the net income based on the current values and the two scenarios as follows:

a. Create a Scenario Summary report using the Net income data as the result cell to show how the net income changes depending on the revenue and expense changes.

b. Use Income Summary as the name of the worksheet containing the report.

29. Go to the Sales Events worksheet, which lists the special promotions the company runs when it attends sales events such as expositions and roadshows for cycling enthusiasts in three cities. The company has a budget of $179,000 for the promotions, and Samira wants to know how many promotions to run to stay within budget. Use Solver to find this information as follows:

a. Set an objective of matching the total cost of the promotions with the budget amount by changing the number of promotions run in the three locations.

b. Determine and enter the constraints based on the information provided in Table 2.

c. Use Simplex LP as the solving method to find a global optimal solution, solve the model, and then save it in the second cell below the Total cost row heading.

Table 2: Solver Constraints

Constraint | Cell or Range The number of promotions run is greater than or equal to 0 | C10:E10 The number of promotions run is an integer | C10:E10 The company can run up to 4 promotions in Portland | Portland_promotions The company can run up to 6 promotions in Salem | Salem_promotions The company can run up to 4 promotions in Seattle | Seattle_promotions

30. Samira 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, and then produce an Answer report on a worksheet that uses Events Answer Report as its name.

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 website to submit your completed project.

Final Figure 1: Customer Survey Worksheet

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Annual Sales Worksheet

Final Figure 3: August Sales by State Worksheet

Final Figure 4: Sales Reps Worksheet

Final Figure 5: August Sales by Product Worksheet

Final Figure 6: Product Orders Worksheet

Final Figure 7: Shipments Worksheet

Final Figure 8: Income Summary Worksheet

Final Figure 9: Income Analysis Worksheet

Final Figure 10: Events Answer Report Worksheet

Final Figure 11: Sales Events Worksheet

Need help with this project?