Excel capstone

Illustrated Excel 2016 | Modules 9-16: SAM Capstone Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png Illustrated Excel 2016 | Modules 9-16: SAM Project Capstone 1a

Breeze Smart Home Devices

AUTOMATING AND ANALYZING DATA

GETTING STARTED

Open the file IL_EX16_CS9-16a_FirstLastName_1.xlsm, available for download from the SAM website.

Save the file as IL_EX16_CS9-16a_FirstLastName_2.xlsm by changing the “1” to a “2”.

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

1. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

1. Support_IL_EX16_CS9-16a_Orders.accdb

With the file IL_EX16_CS9-16a_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.

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.

This project requires you to use the Solver and the Analysis ToolPak add-ins. If these add-ins are not available on the Data tab in the Analysis group (or if the Analysis group is not available), click the File tab, and then click the Options button. In the left pane of the Excel Options dialog box, click the Add-Ins option. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. In the Add-Ins dialog box, click the Analysis ToolPak and the Solver Add-In check boxes and then click the OK button. Follow any remaining prompts to install the Analysis ToolPak and Solver.

To complete this project you will need to display the Developer tab. To add this tab to the Excel Ribbon, click the File tab to open Backstage View and then click the Options button. In the Excel Options dialog box, click the Customize Ribbon option and click the Developer check box. Click the OK button to close the Excel Options dialog box and confirm that the Developer tab appears in the Excel Ribbon.

PROJECT STEPS

Will Deyes is the sales manager for Breeze Smart Home Devices, a growing company that sells smart devices such as lights, locks, plugs, and thermostats that can be controlled with a smartphone. Will has asked you to update a workbook to show recent sales data and identify trends. He has reviewed the workbook and inserted comments about its contents.

Switch to the Sales Overview worksheet and respond to a comment as follows:

a. Read Will’s comment in cell C5, and then change the value in cell C16 to $416,000 to respond to Will’s request.

b. At the end of Will’s comment in cell C5, press ENTER, and then add OK, it’s done. (including the period).

In response to a comment, modify the Total Sales by Region chart in the range D1:G12 as follows:

c. Read Will’s comment in cell C10, and then change the chart type to a Clustered Bar chart using the first chart option.

d. Add the data in the range B9:C9 to the chart.

e. Apply the Style 5 chart style to coordinate with other charts in the worksheet.

f. Change the colors of the chart to Monochromatic Palette 5 in the Monochromatic section of the Change Colors gallery. (Hint: Depending on your version of Office, the name of the color may appear as Color 9.)

g. Delete Will’s comment in cell C10.

Modify the axes to identify the data in the bar chart as follows:

h. Add 2019 Sales as the primary horizontal axis title.

i. Display the units on the primary horizontal axis in Thousands.

j. Add Inside End data labels to the bars.

k. Remove the legend from the chart.

To show trends in the quarterly sales data for each region, add sparklines to the worksheet as follows:

l. Add Column Sparklines to the range G15:G21 based on data in the range C15:F21.

m. Apply the Blue, Accent 5 sparkline color (9th column, 1st row of the Theme Colors palette) to coordinate with the colors in the other charts.

n. Display a High Point marker in the sparklines to quickly identify the highest sales quarter for each region.

o. Change the High Point marker color to Orange, Accent 2 (6th column, 1st row of the Theme Colors palette) so that the high points use the orange accent color in the worksheet.

Make the Total Quarterly Sales - 2019 chart in the range I1:O12 easier to interpret as follows:

p. Add Sales as the primary vertical axis title.

q. Increase the font size of the primary vertical axis title to 12 pt.

r. Add Quarters as the primary horizontal axis title.

s. Increase the font size of the primary horizontal axis title to 12 pt.

Will wants to make the Quarterly Sales by Region chart easier to interpret. Format the clustered column chart in the range H14:O27 as follows:

t. Change the chart type to a Stacked Column chart grouped by Quarter.

u. Change the fill color of the Mid-Atlantic data series to Blue in the Standard Colors palette (8th column) to make the data series less prominent.

v. Remove the legend.

w. Add a Data Table With Legend Keys to the chart.

x. Resize the stacked column chart so it covers the gray shading in the range H14:O34.

Switch to the Sales by Product worksheet. Modify the Quarterly Sales Trend clustered column chart in the range C1:F11 so it compares products across all quarters by switching the row and column data.

Modify the column sparklines in the range F15:F19 to make it easier to compare the product sales in each quarter as follows:

y. Change the sparkline type to Line.

z. Apply the Blue, Sparkline Style Dark #5 sparkline style.

aa. Display the High Point and the Low Point in the sparklines.

Modify the 2019 Sales by Product Type chart in the range G1:L19 to make the chart easier to analyze as follows:

ab. Change the chart type to a Combo chart.

ac. Use Clustered Column as the chart type for the Lights, Locks, Plugs, and Thermostats data series.

ad. Use Line as the chart type for the Average data series.

ae. Change the color of the line in the Average data series to Orange, Accent 2 (6th column, 1st row of the Themes Colors palette) to make the line more noticeable.

af. Remove all the gridlines from the chart.

ag. Change the Maximum Bounds of the vertical axis to 900,000.

ah. Add a Linear Trendline based on the Thermostats data series.

ai. Change the weight of the new trendline to 2¼ pt.

aj. Apply the White, Background 1, Darker 15% shape fill color (1st column, 3rd row of the Theme Colors palette) to the Plot area of the chart to separate the plot area from the rest of the chart.

ak. Remove the primary horizontal axis title from the chart.

Switch to the Sales by Rep worksheet. Will created a macro to insert a chart of sales by each sales representative. From the last time he ran the macro, the worksheet displays a chart in the incorrect format (a bar chart). The chart also includes sales data for Harsia and Lewison, but not Sattinger. Will has already updated the macro to produce a column chart instead of a bar chart. Troubleshoot and edit the data range in the macro as follows:

al. Assign the SalesChart macro to the button labeled Chart Sales by Rep.

am. Edit the SalesChart macro by changing the selected range in the following lines of code to include the Sattinger label in cell A8 and sales value in cell C8:

Range("A5:A7,C5:C7").Select

and

ActiveChart.SetSourceData Source:=Range( _ "'Sales by Rep'!$A$5:$A$7,'Sales by Rep'!$C$5:$C$7")

an. Save the changes, then delete the bar chart.

ao. Click the Chart Sales by Rep button to run the updated SalesChart macro to verify the chart includes the information for all three sales reps.

ap. Resize and reposition the column chart so the upper-left corner is in cell E4 and the lower-right corner is in cell K18.

Will also created a macro named RepBonus, which determines which sales reps receive a bonus each quarter. Cell D8 incorrectly indicates Sattinger should not receive a bonus though her sales exceeded the quota. Troubleshoot the RepBonus macro as follows:

aq. Edit the RepBonus macro in the Visual Basic Editor. Correct the following code at the end of the module so that Sattinger will receive a bonus when her sales exceed $450,000:

If Range("C8") >= 4500000 Then

Range("D8").Select

ActiveCell.Formula = "Yes"

Add a button to the worksheet as follows so that Will can run the RepBonus macro by clicking the button:

ar. Below the Chart Sales by Rep button, insert a Rectangle to use as a button.

as. Reposition and resize the rectangle so it covers the range A13:C14.

at. Enter Assign Bonuses as the button text.

au. Change the font size of the button text to 14 pt.

av. Apply the Middle Align and Center alignment options.

aw. Apply the Moderate Effect - Blue, Accent 5 shape style to the new button.

ax. Assign the RepBonus macro to the Assign Bonuses button.

ay. Run the RepBonus macro. (Hint: The value in cell D8 should be “Yes”.)

Will asks you to work with the company’s current revenue and sales data to develop scenarios and what-if analyses that identify ways to increase profits on thermostats.

Switch to the Thermostats worksheet, which contains a few errors. Correct the errors as follows before performing the analyses that Will requests:

az. Select cell C8 and then use Error Checking to determine the cause of the divide by zero error. Examine the formula and then modify it so that revenue amount is divided by the total sales amount to calculate the percentage of total.

ba. Format the value in cell C8 using the Percentage number format with 0 decimal places.

bb. Select cell F11. Use the Trace Precedents button to locate the cells on which that figure is based, then correct the formula so that it divides the total R&D cost for Aurora models by the number of Aurora units sold.

bc. Format the value in cell F11 using the Comma Style number format with 2 decimal places.

Will has already created two scenarios in the Thermostats worksheet. Create a third scenario to examine the effects of a 10 percent sales reduction in thermostat models as follows:

bd. In the Scenario Manager, add a new scenario and use Unit Sales Decrease 10% as the scenario name.

be. Change the units sold values in the range F12:H12.

bf. Use the information shown in Table 1 below as the values for the changing cells.

Table 1: Unit Sales Decrease 10% Scenario Values

Changing Cell

Value

Aurora_Units_Sold (F12)

19800

Crystal_Units_sold (G12)

18315

Glide_Units_Sold (H12)

15930

bg. Show the results of the Current scenario.

Compare the results of the three scenarios as follows:

bh. Create a Scenario Summary Report for result cells B8:B10, F13:H13.

bi. On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column E.

bj. Delete the contents of the range B16:B18 to remove the notes.

Return to the Thermostats worksheet. Determine how changing the number of Aurora thermostats sold will affect gross profit as follows:

bk. In cell F17, enter a formula that references cell F13, the gross profit of Aurora thermostats.

bl. Select the range E17:F22 and then create a one-input data table.

bm. Use the value in cell F12 as the column input cell because cell F12 contains the units sold amount for Aurora thermostats.

Will asks you to determine how many Crystal thermostats the company needs to sell to earn a gross profit per unit of $18.00. Provide this information to Will as follows:

bn. Use Goal Seek to set cell G14 (the gross profit per Crystal thermostat) to a value of 18.

bo. Change the number of units sold in cell G12 to achieve the goal.

Switch to the Controllers worksheet. Breeze Smart Home Devices is developing a controller for managing smart home devices from a single control panel. The company plans to work with three vendors to manufacture the controller. Will wants to know how to minimize the total manufacturing cost. Run Solver to solve this problem as follows:

bp. Set the objective as minimizing the value in cell E12 (Mfg_Total_Cost). (Hint: Be sure to select the Min option to minimize the value.)

bq. Use the range B11:D11 as the changing variable cells.

br. Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:

Table 2: Solver Constraints

Requirement

Cell Reference

Comparison Operator

Constraint

The units produced values must be integers.

B11:D11

int

integer

Vendors must produce at least 5,000 units.

B11:D11

>=

B16

Beckes can produce a maximum of 12,000 units.

Beckes_Units

<=

B17

SmartEnergy can produce a maximum of 7,500 units.

SmartEnergy_Units

<=

B18

TW Assembly can produce a maximum of 12,000 units.

TW_Assembly_Units

<=

B19

Breeze Smart Home Devices requires 25,000 controllers.

Total_Units

=

B20

bs. Make unconstrained variables non-negative and use GRG Nonlinear as the solving method, and then run Solver. Keep the solution, then return to the Solver Parameters dialog box. Save the model beginning in cell D16, then close the Solver Parameters dialog box.

Will wants to compare the values you found using Solver to the original values. Provide this information as follows:

bt. In the Scenario Manager, add a scenario using Optimal Cost as the scenario name. (Hint: The Controllers worksheet already contains a predefined scenario containing the original values of cells B11:D11.)

bu. Accept the current values for the changing cells, which show the results of the Solver model.

bv. Create a Scenario Summary report using cell E11 (which has the defined name Total_Units) as the result cell.

bw. Change the name of the Scenario Summary 2 worksheet to Controllers Scenario to distinguish it from the other Scenario Summary worksheet.

Will asks you to produce summary statistics on the controllers data. Provide this information as follows:

bx. Return to the Controllers worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.

by. Use the range H12:L12 (the gross profit from years 1 through 5) as the input range.

bz. Indicate that the data is grouped by rows.

ca. Display the statistics in the output range starting in cell G16 of the Controllers worksheet.

cb. Produce summary statistics.

cc. Enter Controller Projections in cell G16 to provide a descriptive heading for the statistics.

Will has an Access database containing customer order data for the first quarter of 2020. He asks you to bring this data into the Excel workbook.

Switch to the Orders worksheet. Import and format the Access data as follows:

cd. Import the data from the Orders table of the Access file Support_IL_EX16_CS9-16a_Orders.accdb into the worksheet at cell A4.

ce. Import the data as an Excel table.

cf. Apply the Table Style Light 13 table style to the imported data. (Hint: Depending on your version of Office, the table style may apear as Blue, Table Style Light 13 instead.)

cg. Apply the Accent 5 cell style to the range A4:G4.

Calculate the total sales for each order and sort the table as follows:

ch. Add a column named Total Sales to the right of the UnitPrice column.

ci. Resize column H to its best fit.

cj. In cell H5, insert a formula without using a function that multiplies the Quantity by the UnitPrice. If the formula does not automatically fill the rest of the table column, copy and paste the formula into the range H6:H67.

ck. Format the UnitPrice and Total Sales values using the Currency number format with the $ symbol and 2 decimal places.

cl. Sort the records by the Total Sales field from Smallest to Largest.

Calculate the total number of units sold as follows:

cm. Add a Total Row to the table.

cn. Use the Total Row to calculate the sum of the values in the Quantity column.

Use Orders as the defined name of the table.

Will wants to display the quantity of products ordered by customer and type. To provide this information, create and format a PivotTable as follows:

co. Insert a PivotTable on a new worksheet based on the Orders table.

cp. Use Pivots as the name of the worksheet.

cq. Add the Product field to the Rows area of the PivotTable.

cr. Add the CustomerID field to the Rows area below the Product field.

cs. Add the Quantity field to the Values area of the PivotTable. (Hint: The field appears as Sum of Quantity.)

ct. Apply Pivot Style Medium 13 to the PivotTable to use a design similar to the one applied to the Orders table. (Hint: Depending on your version of Office, the PivotTable style may appear as Sky Blue, Pivot Style Medium 13.)

cu. Turn off the field headers in the PivotTable.

cv. Display the PivotTable using the Show in Compact Form report layout.

Return to the Orders worksheet and add the record shown in Table 3 on the next page as the last record in the Orders table before the Total row. (Hint: The value for the Total Sales column is calculated automatically.)

Table 3: New Record for the Orders Table

Column Title

Value

OrderID

64

CustomerID

BR209

Region

South Atlantic

OrderDate

3/28/2020

Product

Thermostat

Quantity

5

Unit Price

$219.00

Switch to the Pivots worksheet and refresh the data source so it displays accurate data.

Will also wants to display the quantity and total sales of products by region. To provide this information, create and format a PivotTable as follows:

cw. Insert a PivotTable in cell D3 of the Pivots worksheet based on the Orders table.

cx. Add the Product field to the Columns area of the PivotTable.

cy. Add the Region field to the Rows area of the PivotTable.

cz. Add the Total Sales field to the Values area of the PivotTable. (Hint: The field appears as Sum of Total Sales.)

da. Change the Value Field Settings for the Sum of Total Sales values to apply the Currency number format with 0 decimal places and the $ symbol.

db. Apply Pivot Style Medium 13 to the PivotTable to use a design similar to the one applied to the Orders table. (Hint: Depending on your version of Office, the PivotTable style may appear as Sky Blue, Pivot Style Medium 13.)

To distinguish between the two PivotTables, use Products in cell A2 to identify the first PivotTable. Use Regions in cell D2 to identify the second PivotTable.

Provide another way to visualize the sales by region data by creating a PivotChart as follows:

dc. Create a Clustered Column PivotChart based on the PivotTable in the range D3:I11.

dd. Move and resize the PivotChart so that its upper-left corner is in cell D14 and its lower-right corner is in cell I35.

de. Add the title Sales by Region to the PivotChart.

df. Display the legend at the bottom of the chart.

dg. Change the colors of the chart to Monochromatic Palette 5 in the Monochromatic section of the Change Colors gallery. (Hint: Depending on your version of Office, the name of the color may appear as Color 9.)

dh. Hide all of the field buttons in the chart.

Will is interested in the performance of thermostats in certain regions. Filter the Regions PivotTable to display thermostat orders from the Midwest, Mountain, and Pacific regions as follows:

di. Insert a slicer based on the Region field in the range J3:K13, then use the slicer to filter the Regions PivotTable and display Midwest, Mountain, and Pacific orders only.

dj. Add a second slicer based on the Product field.

dk. Move the Product slicer to the right of the Region slicer so that its upper-left corner is in cell L3 and the lower-right corner is in cell M13.

dl. Use the Product slicer to filter the Regions PivotTable and display Thermostat orders only.

dm. Apply the Slicer Style Light 5 style to both slicers. (Hint: Depending on your version of Office, the Slicer style may appear as Sky Blue, Slicer Style Light 5.)

Return to the Orders worksheet. Complete the Quantity per Product section as follows:

dn. In cell K5, enter a formula that extracts data from cell B4 of the Products PivotTable on the Pivots worksheet.

do. In cell K6, enter a formula that extracts data from cell B9 of the Products PivotTable on the Pivots worksheet.

dp. In cell K7, enter a formula that extracts data from cell B17 of the Products PivotTable on the Pivots worksheet.

dq. In cell K8, enter a formula that extracts data from cell B24 of the Products PivotTable on the Pivots worksheet.

Your workbook should look like the Final Figure on the following pages. In the Thermostats worksheet, cell G12 is intentionally blurred. The Controllers worksheet Final Figure has been intentionally omitted. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Sales Overview Worksheet

Final Figure 2: Sales by Product Worksheet

Final Figure 3: Sales by Rep Worksheet

Final Figure 4: Scenario Summary Worksheet

Final Figure 5: Thermostats Worksheet

Final Figure 6: Controllers Scenario Worksheet

Final Figure 7: Pivots Worksheet

Final Figure 8: Orders Worksheet

2