Understanding Cost Volume Profit XP XP Relationships Cost - - PDF document

▶
understanding cost volume profit
SMART_READER_LITE
LIVE PREVIEW

Understanding Cost Volume Profit XP XP Relationships Cost - - PDF document

Feb 23, 2023 20 likes •164 views

3/29/2012 MS Excel Performing What If Analyses Analyses Understanding Cost Volume Profit XP XP Relationships Cost volume profit (CVP) analysis expresses the relationship between a companys expenses, its volume of

slide-1
SLIDE 1

3/29/2012 1

MS Excel Performing What‐If Analyses Analyses

XP XP

Understanding Cost‐Volume‐Profit Relationships

  • Cost‐volume‐profit (CVP) analysis expresses the

relationship between a company’s expenses, its volume

  • f business and the resulting profit or net income
  • f business, and the resulting profit or net income
  • Variable expenses change in proportion to the amount
  • f business a company does
  • A fixed expense is an expense that must be paid

regardless of sales volume

  • A variable expense is part variable and part fixed
  • The point where revenue equals expenses is called the

break‐even point

– Break‐even analysis

Engineering Staff College of India

slide-2
SLIDE 2

3/29/2012 2

XP XP

Understanding Cost‐Volume‐Profit Relationships

Engineering Staff College of India

XP XP

Working with What‐If Analysis and Goal Seek

To perform what‐if analysis:

– Change the value of a worksheet cell (the input cell) b l l d ll ( h l – Observe its impact on one or more calculated cells (the result cells)

To perform Goal Seek:

– In the Data Tools group on the Data tab, click the What‐If Analysis button, and then click Goal Seek – In the Set cell box select the result cell and then in the To In the Set cell box, select the result cell, and then, in the To value box, specify its value (goal) – In the By changing cell box, specify the input cell – Click the OK button. The value of the input cell changes to set the value of the result cell

Engineering Staff College of India

slide-3
SLIDE 3

3/29/2012 3

XP XP

Working with What‐If Analysis and Goal Seek

Engineering Staff College of India

XP XP

Working with What‐If Analysis and Goal Seek

Engineering Staff College of India

slide-4
SLIDE 4

3/29/2012 4

XP XP

Working with One‐Variable Data Tables

  • A data table organizes the results of several what‐if

analyses within a single table

  • Insert a formula that references the input cell in the

upper‐left cell of the table

  • Insert input values in either the first row or first column
  • f the table
  • For input values in the first row, insert formulas

referencing result cells in the table’s first column; for input values in the first column, insert formulas referencing result cells in the table’s first row

Engineering Staff College of India

XP XP

Working with One‐Variable Data Tables

  • Select the table (excluding any row or column

headings). In the Data Tools group on the Data tab, click the What‐If Analysis button, and then click Data Table

  • If the input values are in the first row, enter the

cell reference to the input cell in the Row input cell box; if the input values are in the first cell box; if the input values are in the first column, enter the cell reference to the input cell in the Column input cell box

  • Click the OK button

Engineering Staff College of India

slide-5
SLIDE 5

3/29/2012 5

XP XP

Working with One‐Variable Data Tables

Engineering Staff College of India

XP XP

Working with Two‐Variable Data Tables

  • A two‐variable data table uses two input cells, but

unlike a one variable data table, only the value of a single result cell can be displayed single result cell can be displayed

  • Insert a formula that references the result cell in the

upper‐left cell of the table

  • Insert input values in the first row and first column of

the table

  • Select the table (excluding any row or column headings)
  • In the Data Tools group on the Data tab, click the What‐

If Analysis button, and then click Data Table

Engineering Staff College of India

slide-6
SLIDE 6

3/29/2012 6

XP XP

Working with Two‐Variable Data Tables

  • Enter the cell reference corresponding to the

input values in the first row in the Row input cell box; enter the cell reference to the input values in the first column in the Column input cell box

  • Click the OK button

Engineering Staff College of India

XP XP

Working with Two‐Variable Data Tables

Engineering Staff College of India

slide-7
SLIDE 7

3/29/2012 7

XP XP

Using Scenario Manager

  • Scenario Manager enables you to create as

many scenarios as you want, easily switching between the different scenarios to display the results of several what‐if analyses

  • Enter the data values in the worksheet for the

scenario I th D t T l th D t t b li k th

  • In the Data Tools group on the Data tab, click the

What‐If Analysis button, and then click Scenario Manager

Engineering Staff College of India

XP XP

Using Scenario Manager

  • Click the Add button in the Scenario Manager dialog box
  • In the Scenario name box, type a name for the scenario
  • In the Changing cells box, specify the input or changing

cells

  • Click the OK button
  • In the Scenario Values dialog box, specify values for

each of the input cells, clicking the Add button after each

  • Click the OK button

Engineering Staff College of India

slide-8
SLIDE 8

3/29/2012 8

XP XP

Using Scenario Manager

Engineering Staff College of India

XP XP

Creating a Scenario Summary Report

  • A scenario summary report lists the values for the

changing cells and result cells under each scenario

  • In the Data Tools group on the Data tab, click the What‐

If Analysis button, and then click Scenario Manager

  • Click the Summary button
  • Click the Scenario summary option button to create a

scenario summary report (or click the Scenario PivotTable report option to create a PivotTable describing the scenarios)

  • Select the results cells to display in the report
  • Click the OK button

Engineering Staff College of India

slide-9
SLIDE 9

3/29/2012 9

XP XP

Creating a Scenario Summary Report

Engineering Staff College of India

XP XP

Creating a Scenario PivotTable Report

  • A Scenario PivotTable report displays the results

from each scenario as a pivot field in a PivotTable

  • In the Data Tools group on the Data tab, click the

What‐If Analysis button, and then click Scenario Manager Cli k th S mmar b tt d th li k th

  • Click the Summary button, and then click the

Scenario PivotTable report option button

  • Click the OK button

Engineering Staff College of India

slide-10
SLIDE 10

3/29/2012 10

XP XP

Creating a Scenario PivotTable Report

Engineering Staff College of India

XP XP

Understanding Price Elasticity of Demand

Engineering Staff College of India

slide-11
SLIDE 11

3/29/2012 11

XP XP

Understanding Price Elasticity of Demand

  • The effect that price has on demand is called the

price elasticity of demand

– Relatively inelastic – Perfectly inelastic – Relatively elastic – Perfectly elastic Unit elastic – Unit elastic

Engineering Staff College of India

XP XP

Finding an Optimal Solution Using Solver

  • Solver is a program that searches for the optimal solution of a

problem involving several variables

  • Check whether Solver is already installed and activated If it is

Check whether Solver is already installed and activated. If it is, Solver will appear in Analysis group on the Data tab

  • Click the Office Button, and then click the Excel Options button
  • Click Add‐Ins in the left pane, and then click the arrow next to the

Manage box and click Excel Add‐ins

  • Click the Go button to open the Add‐Ins dialog box
  • Click the Solver Add‐in check box, and then click the OK button.

Follow the remaining prompts to install Solver, if it is not already installed

Engineering Staff College of India

slide-12
SLIDE 12

3/29/2012 12

XP XP

Setting Solver Parameters

  • In the Analysis group on the Data tab, click the

Solver button

  • In the Set Target Cell box, specify the target cell
  • Click the Max, Min, or Value of option buttons to

maximize, minimize, or set the target cell to a specified value

  • In the By Changing Cells input box, specify the

changing cells

Engineering Staff College of India

XP XP

Setting Constraints on the Solver Solution

  • In the Solver Parameters dialog box, click the Add

button

  • Enter the cell reference of the cell or cells containing

the constraint

  • Specify the nature of the constraint (<=, =, >=, int, or

bin)

  • Enter the constraint value in the Constraint box
  • Click the OK button to add the constraint and return to

the Solver dialog box

  • Repeat for each constraint you want to add

Engineering Staff College of India

slide-13
SLIDE 13

3/29/2012 13

XP XP

Setting Constraints on the Solver Solution

Engineering Staff College of India

XP XP

Saving and Loading a Solver Model

  • To save a Solver model:

– Open the Solver dialog box l k h b d h l k h d l b – Click the Options button and then click the Save Model button – Select the range to contain the parameters of the Solver model, and then click the OK button

  • To load a Solver model:

– Open the Solver dialog box Click the Options button and then click the Load Model button – Click the Options button and then click the Load Model button – Select the range within the current worksheet containing the Solver parameters, and then click the OK button

Engineering Staff College of India

slide-14
SLIDE 14

3/29/2012 14

XP XP

Saving and Loading a Solver Model

Engineering Staff College of India

XP XP

Saving and Loading a Solver Model

Engineering Staff College of India