New Perspectives Excel 365

New Perspectives Excel 365 | Module 8: SAM Project A Iris Footwear

EXPLORE BUSINESS OPTIONS WITH WHAT-IF TOOLS

September 8, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file NP_EX365_8A_FirstLastName_1.xlsx as NP_EX365_8A_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_8A_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. Pedro Olmos is a sales analyst for Iris Footwear, an athletic footwear manufacturer in New Orleans, Louisiana. He is developing a workbook to analyze the profitability of a new line made from sustainable materials. He asks you to help analyze sales data to determine how the company can increase profits. Go to the EcoPro Income Analysis worksheet, which lists the revenue and expenses for the EcoPro shoe and calculates the net income. Pedro wants to compare the financial outcomes for varying amounts of shoes sold and identify the number of pairs the company needs to sell to break even. He has already named cells for many values in the range B6:B27. Create a one-variable data table as follows to calculate the revenue, expenses, and net income based on units sold:

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

b. In cell E6, enter a formula without using a function that references the Total_revenue cell, which is the expected total sales for the EcoPro shoes.

c. In cell F6, enter a formula without using a function that references the Total_expenses cell, which is the expected total expenses for the EcoPro shoes.

d. In cell G6, 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 D6:G16 and then complete the one-variable data table, using cell B6 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 D5:F16).

b. Resize and position the chart so it covers the range H4:M16.

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

a. Use Break-Even Point as the chart title.

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

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

4. Pedro also wants you to examine how varying the sales price and volume affects net income for the EcoPro shoes. He has already entered the net income in cell D20 and sales prices in the range E20:I20. 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 D20:I30, create a two-variable data table using the price per unit (cell B7) as the Row input cell.

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

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

5. Pedro has created two scenarios in the EcoPro Income Analysis worksheet. The Current scenario assumes the current values for units sold, price, and fixed expenses (salaries and benefits, distribution, and miscellaneous). The Reduced 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: Increased Price Scenario Values

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

a. Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Current, Reduced Price, and Increased Price scenarios. Use the total revenue, total expenses, and net income in the range B25:B27 as the result cells.

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

7. Return to the EcoPro Income Analysis worksheet. Create a Scenario PivotTable report of the three scenarios displaying the total revenue, total expenses, and net income (the range B25:B27) 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. Pedro 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:E21.

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

10. Go to the Product Mix worksheet, which lists three Iris Footwear shoes made from recyclable and sustainable materials. Pedro 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 E24).

b. Reach the objective by changing the optimal product mix (B11:D11).

11. Set constraints as follows when using Solver:

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

b. Iris Footwear needs to produce 10,000 or more of each shoe model, so the optimal mix values for each model (range B11:D11) must be at least 10,000.

c. Those same values in the range B11:D11 must be integers because the company must sell complete pairs of shoes.

d. The Remaining values for each assembled part (range I6:I16) must be greater than or equal to zero because the company cannot produce more shoes 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 G19:G26, 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

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Scenario PivotTable Worksheet

Final Figure 3: EcoPro Income Analysis Worksheet

Final Figure 4: Answer Report 1 Worksheet

Final Figure 5: Product Mix Worksheet

Need help with this project?