New Perspectives Excel 365 | Module 8: SAM Project B Quench Drinkware

GETTING STARTED

1. Save the file NP_EX365_8B_FirstLastName_1.xlsx as NP_EX365_8B_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 NP_EX365_8B_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.

3. · 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 PC - 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. o Mac - Click Tools, and then click Excel Add-Ins. Click the Solver Add-in option to enable it. Then, click OK.

PROJECT STEPS

1. Jermaine Hooper is a financial analyst for Quench Drinkware, a manufacturer of insulated mugs in Baltimore, Maryland. He is developing a workbook to analyze the profitability of a new series of drinkware for outdoor enthusiasts. He asks you to help analyze the sales data to determine how the company can increase profits. Go to the Expedition Income Analysis worksheet, which lists the revenue and expenses for the Expedition series of drinkware and calculates the net income. Create a one-variable data table as follows to calculate the revenue, expenses, and net income based on units sold:

a. In cell E6, enter a formula without using a function that references the Units_sold cell, which is the expected number of Expedition units sold.

b. In cell F6, enter a formula without using a function that references the Total_revenue cell, which is the expected total sales for the Expedition mugs.

c. In cell G6, enter a formula without using a function that references the Total_expenses cell, which is the expected total expenses for the Expedition mugs.

d. In cell H6, enter a formula without using a function that references the Net_income cell, which is the expected net income for the product.

e. Select the range E6:H16 and then complete the one-variable data table, using cell C6 as the Column input cell for the data table.

2. Provide a visual representation of the break-even data as follows:

a. Create a Scatter with Straight Lines chart based on the units sold, revenue, and expenses in the data table (the range E5:G16).

b. Resize and position the chart so it covers the range I4:N16.

3. Clarify the purpose of the chart and focus on the areas containing data as follows:

a. Use Break Even as the chart title.

b. Change the Minimum bound of the horizontal axis to 40,000 and let the Maximum bound adjust automatically.

c. Change the Minimum bound of the vertical axis to 1,700,000 and let the Maximum bound adjust automatically.

4. Jermaine also wants you to examine how varying the sales price and volume affects net income for the Expedition products. He has already entered the net income in cell E20 and sales prices in the range F20:J20. Create a two-variable data table as follows to calculate the net income based on the sales price and units sold:

a. For the range E20:J30, create a two-variable data table using the price per unit (cell C7) as the Row input cell.

b. Use the number of units sold (cell C6) as the Column input cell.

c. Apply a custom format to cell E20 to display the text Units/Price in place of the cell value

5. Jermaine has created two scenarios in the Expedition Income Analysis worksheet. The Current scenario assumes the current values for units sold, price, and fixed expenses (salaries and benefits, distribution, and miscellaneous). The Lower Price scenario assumes more units sold at a lower price. He asks you to create a scenario that assumes fewer units sold at a higher price. Create a scenario using the data shown in bold in Table 1 without applying any scenarios.

Table 1: Higher Price Scenario Values

Scenario Name | Higher Price Changing cells | C6:C7,C19:C21 Units_sold (B6) | 51,000 Price_per_unit (B7) | 40.50 Salaries_and_benefits (B19) | 625,500 Distribution (B20) | 170,000 Miscellaneous (B21) | 175,000

6. Provide a report on the scenarios for Jermaine as follows:

a. Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Current, Lower Price, and Higher Price scenarios. Use the total revenue, total expenses, and net income in the range C25:C27 as the result cells.

b. Go to the Scenario Summary worksheet and delete column D, which repeats the current values.

7. Return to the Expedition Income Analysis worksheet. Create a Scenario PivotTable report of the three scenarios displaying the total revenue, total expenses, and net income (the range C25:C27) for each scenario.

8. Go to the Scenario PivotTable worksheet. Make the PivotTable easier to interpret as follows:

a. Change the Value Field Settings of the Total_Revenue, Total_Expenses, and Net_Income values to use the Currency number format with 0 decimal places and the $ symbol.

b. Display negative values in red, enclosed in parentheses.

c. Remove the filter from the PivotTable.

9. Jermaine asks you to compare the three scenarios in a chart. Create a PivotChart as follows:

a. Create a Clustered Column PivotChart based on the PivotTable.

b. Resize and position the chart so it covers the range A8:E20.

c. Hide the field buttons to remove clutter from the PivotChart.

10. Go to the Product Mix worksheet, which lists the company's three most popular types of drinkware. Jermaine wants you to find the product mix that generates the most net income for the company. Run Solver to solve this problem as follows:

a. Set the objective as maximizing the percentage of difference between the even and optimal product mixes (cell F23).

b. Reach the objective by changing the optimal product mix (C11:E11).

11. Set constraints as follows when using Solver:

a. The total units sold in the optimal product mix (cell D19) must be 52,500.

b. Quench Drinkware needs to produce 15,000 or more of each type of drinkware, so the optimal mix values for each model (range C11:E11) must be at least 15,000.

c. Those same values in the range C11:E11 must be integers because the company must sell complete products.

d. The Remaining values for each assembled part (range J6:J13) must be greater than or equal to zero because the company cannot produce more mugs than the parts available.

12. Run Solver using the default settings, keep the solution, create an Answer Report, and then return to the Solver Parameters dialog box.

13. Save the Solver model to the range H19:H26, and then close the Solver Parameters dialog box.

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

Final Figure 3: Expedition Income Analysis Worksheet

Final Figure 4: Answer Report 1 Worksheet

Final Figure 5: Product Mix Worksheet