Illustrated Excel 2019 | Module 12: SAM Project 1b Avant Quilts PERFORM WHAT-IF ANALYSES                 GETTING STARTED •             Open the file IL_EX19_12b_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

Illustrated Excel 2019 | Module 12: SAM Project 1b Avant Quilts

PERFORM WHAT-IF ANALYSES

                GETTING STARTED

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

•             Save the file as IL_EX19_12b_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_12b_Orders.accdb

•             With the file IL_EX19_12b_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.            Jessa Torres expanded her quilting hobby into a small part-time business called Avant Quilts, which sells artisan quilts via websites and retail outlets throughout the United States. She has entered the company's revenue and sales data in an Excel workbook, and asks you to help her develop scenarios and what-if analyses that identify ways to increase profits for her products.

Go to the Expenses worksheet. Jessa has created two scenarios on the worksheet, one that includes current values, and the other that reflects an increase in sales. Jessa also wants to know how profits would change if she sold 10 percent fewer quilts of each type. Create a third scenario to provide this information for Jessa as follows:

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

b.            Change the values in the range F13:H13.

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

                Table 1: Unit Sales Decrease Scenario Values

 

Changing Cell     Value

Aspen_Units_Sold (F13)               23

Napa_Units_Sold (G13) 20

Santa_Fe_Units_Sold (H13)        24

 

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

Compare the scenarios as follows:

a.            Create a Scenario Summary Report for result cells B9:B11, F14:H14.

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.            Jessa also wants to compare the scenario results.

Create another scenario comparison as follows:

a.            Return to the Expenses worksheet. Create a Scenario PivotTable Report for result cells B9:B11, F14:H14.

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 Expenses worksheet and show the results of the Unit Sales Decrease scenario.

appl

4.            Jessa also wants to determine how increasing or decreasing the number of Aspen quilts will affect gross profit.

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

a.            In cell G8, enter a formula that references the gross profit for Aspen quilts (cell B14).

b.            Create a one-variable data table based on the Aspen sales data in the range F8:G13.

c.             Use the number of Aspen units sold (cell B13) as the column input cell.

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

fo

5.            Jessa projects that sales of Aspen quilts will increase by 5 percent in the coming year and associated expenses will rise by 1.5 percent. She 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 F18, enter a formula that references the projected profit for Aspen quilts (cell B22).

b.            Create a two-variable data table in the range F18:K24.

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

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

6.            Jessa asks you to determine how many Napa quilts the company needs to sell to earn a gross profit of $3000.00.

Provide this information to Jessa as follows:

a.            Use Goal Seek to set the gross profit for Napa quilts (cell C14) to a value of 3000.

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

use

7.            Jessa wants to perform a similar analysis for Santa Fe quilts by determining the price the company needs to set to earn a gross profit per Santa Fe quilt of $100.00.

Provide this information to Jessa as follows:

a.            Use Goal Seek to set the gross profit per Santa Fe quilt (cell D15) to a value of 100.

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

8.            Jessa is creating a new quilt design named Tahoe and plans to work with three textile businesses to produce the quilt. Jessa wants to know how to minimize the total manufacturing cost.

Switch to the Tahoe 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 quilter (the range B11:D11) as the changing variable cells.

c.             Adjust the total manufacturing cost by each quilter 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

Quilters must produce at least 20 units. B11:D11                >=           B16

HireOut can produce a maximum of 50 units.      HireOut_Units   <=           B17

Penn can produce a maximum of 55 units.           Penn_Units        <=           B18

Varia can produce a maximum of 60 units.            Varia_Units        <=           B19

Avant Quilts requires 100 Tahoe quilts.  Total_Units         =             B20

 

9.            Jessa 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 Tahoe Answer Report as the name of the new worksheet.

10.          Jessa has an Access database containing data about quilt orders for the current quarter. She asks you to import the two tables in the database so that she can analyze the data.

Import the database tables as follows:

a.            Get data from the Access database named Support_EX19_12b_Orders.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.          Jessa wants to examine the imported order and product data as a PivotTable so that she 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 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 Jessa 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: Expenses Worksheet

 

 

 

                Final Figure 4: Profit Analysis Worksheet

 

 

 

                Final Figure 5: Sales Pivot Worksheet

 

 

 

                Final Figure 6: Tahoe Answer Report Worksheet

Final Figure 7: Tahoe Worksheet

Final Figure 8: Tahoe Answer Report Worksheet