Illustrated Excel 365 | Module 12: SAM Project B Zarco Designs

GETTING STARTED

1. Save the file IL_EX365_12B_FirstLastName_1.xlsx as IL_EX365_12B_FirstLastName_2.xlsx

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

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

2. With the file IL_EX365_12B_FirstLastName_2.xlsx 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.

PROJECT STEPS

1. Jacobo Zarco owns Zarco Designs, a small business that creates custom jewelry for jewelry stores. He is working with the revenue and sales data for the business in an Excel workbook, and asks you to help him develop scenarios and what-if analyses that identify ways to increase profits for his products. Go to the Income Analysis worksheet. Jacobo has created two scenarios on the worksheet, one that includes current values, and another that reflects a 5 percent increase in sales. Jacobo also wants to know how profits would change if he sold 3 percent fewer pieces of jewelry of each type. Create a third scenario to provide this information for Jacobo 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 G12:I12.

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 Bracelets_Units_Sold (G12) | 306 Earrings_Units_Sold (H12) | 357 Necklaces_Units_Sold (I12) | 354

2. Jacobo asks you to compare the results of the three scenarios. Compare the scenarios as follows:

a. Create a Scenario Summary Report for result cells C8:C10, G13:I13.

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. Jacobo also wants you to determine how increasing or decreasing the number of bracelet sales will affect gross profit. Go to the Profit Analysis worksheet, and then create a one-variable data table as follows:

a. In cell H7, enter a formula that references the gross profit for bracelets (cell C13).

b. Create a one-variable data table based on the bracelet sales data in the range G7:H12.

c. Use the number of bracelets sold (cell C12) as the column input cell.

4. Jacobo projects that sales of bracelets will increase by 3 percent in the coming year and associated expenses will rise by 2 percent. He wants you to display the result in profit if sales and expenses change at different rates. Determine the changes in profit as follows:

a. In cell G17, enter a formula that references the projected profit for bracelets (cell C21).

b. Create a two-variable data table in the range G17:L23.

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

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

e. Use a Custom number format to display the text "Projected Profit" in cell G17 instead of the formula result.

5. Jacobo asks you to determine how many earrings Zarco Designs needs to sell to earn a gross profit per unit of $65.00. Provide this information to Jacobo as follows:

a. Use Goal Seek to set the gross profit per earrings sold (cell D14) to a value of 65.

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

6. Jacobo wants you to perform a similar analysis for necklaces by determining the price Zarco Designs needs to set to earn a gross profit per necklace of $65.00. Provide this information to Jacobo as follows:

a. Use Goal Seek to set the gross profit per necklace (cell E14) to a value of 65.00.

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

7. Zarco Designs is developing a line of jeweled rings and and plans to work with three vendors to create the jewelry. Jacobo ask you to determine how to minimize the total manufacturing cost. Go to the Projections worksheet. Run Solver to solve this problem as follows:

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

b. Use the units produced by each vendor (the range C11:E11) 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 E16.

Table 2: Solver Constraints

Requirement | Cell Reference | Comparison Operator | Constraint The units produced must be integers. | C11:E11 | int | integer Vendors must produce at least 600 units. | C11:E11 | = | C16 JK can produce a maximum of 750 units. | JK_Units | = | C17 Moda can produce a maximum of 600 units. | Moda_Units | = | C18 Smith can produce a maximum of 800 units. | Smith_Units | = | C19 Zarco Designs requires 2,100 jeweled rings. | Total_Units | = | C20

8. Go to the Product Orders worksheet, which contains a table named Orders and another named Products. Jacobo wants you to analyze the order and product data. Add the data to the data model as follows:

a. Add the Orders table data in the range B7:F47 to the data model.

b. Add the Products table data in the range H7:J10 to the data model. [Hint: Score will be credited only after completing Step 9.)

9. Jacobo wants you to display the 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 Orders 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. [Hint: Ignore the message about creating a relationship as you do so in the next step.]

10. Create a relationship between the primary Products table and the related Orders table using Product ID 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 website to submit your completed project.

Final Figure 1: Scenario Summary Worksheet

Final Figure 2: Income Analysis Worksheet

Final Figure 3: Profit Analysis Worksheet

Final Figure 4: Projections Worksheet

Final Figure 5: Orders Power Pivot Worksheet

Final Figure 6: Product Orders Worksheet