Illustrated Excel 2019 | Module 12: SAM Project 1a Tech Togs PERFORM WHAT-IF ANALYSES GETTING STARTED •             Open the file IL_EX19_12a_FirstLastName_1

Subject:MS ExcelPrice:20.99 Bought3

Share With

Illustrated Excel 2019 | Module 12: SAM Project 1a

Tech Togs PERFORM WHAT-IF ANALYSES

GETTING STARTED

•             Open the file IL_EX19_12a_FirstLastName_1.xlsx, available for download from the SAM website.

•             Save the file as IL_EX19_12a_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.

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

o             Support_EX19_12a_Sales.accdb

•             With the file IL_EX19_12a_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.

•             This project requires you to use the Solver add-in. If this add-in is not available on the Data tab in the Analyze group (or if the Analyze group is not available), install Solver as follows:

o             In Excel, click the File tab, and then click the Options button in the left navigation bar. Click the Add-Ins option in the left pane of the Excel Options dialog box. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. In the Add-Ins dialog box, click the Solver Add-In check box and then click the OK button. Follow any remaining prompts to install Solver.

•             To complete this project, you need to add the Power Pivot tab to the ribbon as follows:

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

•             When downloading Access support files, your browser may display a warning message similar to "This type of file can harm your computer." SAM Project files are always considered safe, so you can disregard this message.

                PROJECT STEPS

1.            Akio Mori is a financial analyst for Tech Togs, which manufactures wearable technology. He is working with the company's revenue and sales data in an Excel workbook, and asks you to help him develop scenarios and what-if analyses that identify ways to increase profits for the company's smart watches.

Go to the Expense Report worksheet. Akio has created two scenarios on the worksheet, one that includes current values, and another that reflects a 5 percent increase in sales. Akio also wants to know how profits would change if the company sold 3 percent fewer smart watches of each model. Create a third scenario to provide this information for Akio as follows:

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

b.            Change the values in the range F12:H12.

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

                Table 1: Unit Sales Decrease 3% Scenario Values

 

Changing Cell     Value

Basel_Units_Sold (F12) 8245

Geneva_Units_Sold (G12)           7988

Lucerne_Units_Sold (H12)           8119

 

2.            Akio asks you to compare the results of the three scenarios.

Compare the scenarios as follows:

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

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

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

3.            Akio also wants to compare the scenario results.

Create another scenario comparison as follows:

a.            Return to the Expense Report worksheet. Create a Scenario PivotTable Report for result cells B8:B10, F13:H13.

b.            On the Scenario PivotTable worksheet, format all values using the Currency number format with 2 decimal places and the $ symbol.

c.             Return to the Expense Report worksheet and show the results of the Unit Sales Decrease 3% scenario.

4.            Akio also wants to determine how increasing or decreasing the number of Basel smart watches will affect gross profit.

Go to the Profit Analysis worksheet, and then create a one-variable data table as follows:

a.            In cell G7, enter a formula that references the gross profit for Basel smart watches (cell B13).

b.            Create a one-variable data table based on the Basel sales data in the range F7:G12.

c.             Use the number of Basel units sold (cell B12) as the column input cell.

d.            Format the range G8:G12 using the Currency number format with 0 decimal places and the $ symbol to match the formatting of cell G7.

5.            Akio projects that sales of Basel smart watches will increase by 3 percent in the coming year and associated expenses will rise by 2 percent. He wants to see the result in profit if sales and expenses change at different rates.

Determine the changes in profit as follows:

a.            In cell F17, enter a formula that references the projected profit for Basel smart watches (cell B21).

b.            Create a two-variable data table in the range F17:K23.

c.             Use the expense increase percentage (cell B20) as the row input cell.

d.            Use the projected growth percentage (cell B18) as the column input cell.

6.            Akio asks you to determine how many Geneva smart watches the company needs to sell to earn a gross profit per watch of $10.00.

Provide this information to Akio as follows:

a.            Use Goal Seek to set the gross profit per Geneva smart watch (cell C14) to a value of 10.

b.            Change the number of units sold (cell C12) to achieve the goal.

7.            Akio wants to perform a similar analysis for Lucerne smart watches by determining the price the company needs to set to earn a gross profit per Lucerne smart watch of $12.00.

Provide this information to Akio as follows:

a.            Use Goal Seek to set the gross profit per Lucerne smart watch (cell D14) to a value of 12.

b.            Change the price (cell D8) to achieve the goal.

8.            Tech Togs is designing a new smart watch named Zurich and plans to work with three vendors to manufacture the watch. Akio wants to know how to minimize the total manufacturing cost.

Switch to the Zurich worksheet. Run Solver to solve this problem as follows:

a.            Set the objective to minimize the total manufacturing cost (cell E12).

b.            Use the units produced by each vendor (the range B11:D11) as the changing variable cells.

c.             Adjust the total manufacturing cost by each vendor using the constraints in Table 2.

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

                Table 2: Solver Constraints

 

Requirement     Cell Reference  Comparison Operator    Constraint

The units produced must be integers.    B11:D11                int           integer

Vendors must produce at least 3,500 units.          B11:D11                >=           B16

Gansu can produce a maximum of 8,000 units.   Gansu_Units      <=           B17

Q Star can produce a maximum of 4,500 units.    Q_Star_Units     <=           B18

El Nido can produce a maximum of 8,000 units.  El_Nido_Units   <=           B19

Tech Togs requires 20,000 Zurich smart watches.               Total_Units         =             B20

 

9.            Akio asks you to create a Solver Answer Report to compare the original values with the Solver.

a.            Create the Solver Answer Report by returning to the Solver Parameters dialog box, running Solver again, and then saving the results as an Answer Report.

b.            Use Zurich Answer Report as the name of the new worksheet.

10.          Akio has an Access database containing data about orders for the current month. He asks you to import the two tables in the database so he can analyze the data.

Import the database tables as follows:

a.            Get data from the Access database named Support_EX19_12a_Sales.accdb.

b.            Select the Orders and Products tables to import.

c.             Choose to only create a connection to the data and add the data to the Data Model.

11.          Akio wants to examine the imported order and product data as a PivotTable so that he can compare the units sold to retail and web channels.

Create the PivotTable as follows:

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

b.            Add the ProductName field from the Products table to the Columns area.

c.             Add the Channel field from the Orders table to the Rows area.

d.            Add the Units field from the Orders table to the Values area.

12.          Create a relationship between the imported tables as follows so that Akio can combine and properly compare their data:

a.            Use Power Pivot to manage the Data Model.

b.            In the Power Pivot for Excel window, create a relationship between the Orders and Products tables, choosing ProductID as the column that relates the tables.

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: Scenario Summary Worksheet

 

 

 

                Final Figure 2: Scenario PivotTable Worksheet

 

 

 

                Final Figure 3: Expense Report Worksheet

 

 

 

                Final Figure 4: Profit Analysis Worksheet

 

 

 

                Final Figure 5: Sales Power Pivot Worksheet

 

 

 

                Final Figure 6: Zurich Worksheet