Product Analysis Worksheet - PDF

Document Sample

```					                        C A S E 1 0 : P E R F O R M I N G A W H AT- I F A N A LY S I S

SKILLS
NEWLEAF PAPER                                                          ❖ Use Goal Seek to calculate
a solution
COMPANY: NEW                                                           ❖ Create a one-variable
data table

PRODUCT                                                            ❖ Explore the principles of
cost-volume-profit
relationships

ANALYSIS                                                          ❖ Create a two-variable
data table

❖ Create and apply different
CASE SCENARIO                                                                 Excel scenarios

Ryan in Sales has enlisted the help of Angela in Accounting to perform      ❖ Generate a scenario
summary report
some what-if analysis on a potential new product line, called
BrandBuilder™ Personalized Notebooks. Their first task is to build a        ❖ Generate a scenario
model that can analyze the expected revenue, expenses, and net                PivotTable report
income. Then, they need to determine the potential effect that price
❖ Run Solver to calculate
might have on attracting customers to purchase the personalized               optimal solutions
notebooks. Angela determines that data tables are perfect for such a
task. Ryan is concerned that data tables will not serve as well when        ❖ Create a Solver Answer
presenting their findings to management, so Angela suggests they use          report
Goal Seek and Scenario Manager features to provide brief summaries          ❖ Save and load a
for presentation purposes. Finally, they decide to use Solver, an add-in      Solver model
available for Excel, to demonstrate that the current manufacturing
environment can cost–effectively support the proposed product line.

S T U D E N T DATA F I L E
CASE 10      44             PERFORMING A WHAT-IF ANALYSIS

Use Goal Seek to Calculate a Solution
1. Open the Case10_Username_1 workbook, and then save the file as
Case10_Username_2. In the Documentation sheet, verify your name is in cell B4, and
enter the current date in cell B5.
2. In the Bonuses worksheet, enter a formula in cell D22 that totals the bonus amounts in
the range D8:D21.
3. Format cell D22 with a Purple, Accent 4, Lighter 40% fill color.
4. Use Goal Seek to set the value in cell D22 to the value \$9,000 by changing cell D5. Your
worksheet should look similar to Figure 10-1, except that Figure 10-1 shows a Goal
Seek result for \$6000 and yours will be for \$9000.

FIGURE 10-1    Bonuses worksheet with \$6000 Goal Seek (yours should be \$9000)

Create a One-Variable Data Table
5. In the Analysis worksheet, enter formulas in cells D4:G4 that reference the values for
Projected customers (per month), Total Monthly Revenue, Total Monthly Expenses, and
Net Monthly Income, respectively.
6. In cells D5:D15, enter the values 0 to 50 in increments of 5.
7. In the range D4:G15, create a one-variable data table that calculates the Revenue,
Expenses, and Income based on the Customers value.
NEWLEAF PAPER COMPANY: NEW PRODUCT ANALYSIS                      45   CASE 10

8. Format the data table with the Number category, showing no decimal places, using
the 1000 separator, and showing negative numbers displayed in red surrounded by
parentheses.

Explore the Principles of Cost-Volume-Profit Relationships
9. For the range D3:F15, create a cost-volume-profit chart of the sub-type Scatter with
Straight Lines, and then move the chart to a new sheet called CVP Chart.
10. Add a chart title above the chart called Cost-Volume-Profit, and a primary horizon-
tal axis title below the Value (X) axis called Customers per Month. Your CVP Chart
worksheet should look similar to Figure 10-2.

FIGURE 10-2   Completed CVP chart

Create a Two-Variable Data Table
11. In the Analysis worksheet, enter the values 0 to 50 in cells D19:D29 in increments of 5.
12. In the range E18:J18, enter the values \$10 to \$20 in increments of \$2.
13. In cell D18, enter a formula that references the value for the Net Monthly Income.
CASE 10      46           PERFORMING A WHAT-IF ANALYSIS

14. In cell E17, create the label Selling Price, and then merge and center the label in the
range E17:J17.
15. In the range D18:J29, create a two-variable data table with the BrandBuilder selling
price value as the row input cell and the Projected customers (per month) value as the
column input cell.
16. Format the data table with the Number category, showing no decimal places, using the
1000 separator, and showing negative numbers displayed in red surrounded by paren-
theses. Your Analysis worksheet should look similar to Figure 10-3.

FIGURE 10-3    Completed two-variable data table

Create and Apply Different Excel Scenarios
17. Define names for the cells B5, B6, B8, B23, and B25 using the text from their leftmost
adjacent column, resulting in the cells having the following names, respectively:
BrandBuilder_selling_price, Projected_customers_per_month, Total_Monthly_ Revenue,
Total_ Monthly_Expenses, and Net_Monthly_Income. (Hint: Use the Defined Names
group on the Formulas tab.)
18. Use Scenario Manager to create the four scenarios shown in Figure 10-4.
NEWLEAF PAPER COMPANY: NEW PRODUCT ANALYSIS                   47   CASE 10

FIGURE 10-4   Data for four scenarios

Change Cells          Best Case   Worst Case   High Volume   High Price
BrandBuilder          20          10           15            20
selling price
Projected customers   50          10           50            30
(per month)

Generate a Scenario Summary Report
19. Create a scenario summary report for the four scenarios. Include the Total Monthly
Revenue, Total Monthly Expenses, and Net Monthly Income as the results cells. Your
Scenario Summary worksheet should look similar to Figure 10-5.

FIGURE 10-5   Completed Scenario Summary worksheet
CASE 10      48           PERFORMING A WHAT-IF ANALYSIS

Generate a Scenario PivotTable Report
20. Create a scenario summary pivot table report for the four scenarios. Include the Total
Monthly Revenue, Total Monthly Expenses, and Net Monthly Income as the Results
cells. Your Scenario PivotTable worksheet should look similar to Figure 10-6.

FIGURE 10-6    Completed Scenario PivotTable worksheet
NEWLEAF PAPER COMPANY: NEW PRODUCT ANALYSIS                         49     CASE 10

Run Solver to Calculate Optimal Solutions
21. In the Production Planner worksheet, use Solver to minimize the target cell (cell H21),
which is the cost of producing notebooks each day, by changing the values in cells
H5:H7 and H10:H12.
• Add a constraint that the machines to use (cells H5:H7) must contain integer values.
• Add a constraint that the machines to use (cells H5:H7) must be greater than or
equal to 1.
• Add a constraint that the machines to use (cells H5:H7) must be less than or
equal to number of available machines (cells E5:E7).
• Add a constraint that the number of notebooks to make per day (cells H10:H12)
is greater than or equal to 4000.
• Add a constraint that the number of notebooks to make per day (cells H10:H12)
is less than or equal to the daily maximum based on machines in use (cells
H16:H18).
• Add a constraint that the total number of notebooks to make per day (cell H13)
is greater than or equal to the demand (cell H20).
Run Solver. It may take several minutes for Solver to arrive at a solution; if it reaches
its maximum time without finding a solution, continue running Solver. Once Solver
finds a solution, verify that all the constraints have been satisfied. If you find con-
straints that have not been satisfied, modify the constraints you set in Solver as needed
and then run Solver again. Do not close the Solver dialog box.

CASE 10      50          PERFORMING A WHAT-IF ANALYSIS

Save and Load a Solver Model
23. Save the current Solver model in the Production Planner worksheet starting in cell A18.
Your Production Planner worksheet should look similar to Figure 10-8.

FIGURE 10-8    Completed Production Planner worksheet

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 75 posted: 7/11/2011 language: English pages: 8
Description: Product Analysis Worksheet document sample
How are you planning on using Docstoc?