Get documents about "Break-even Analysis Part 3 Constructing Graphs
Document Sample
Break-even Analysis
Part 3
Constructing Graphs
J. Aieta & D. Troxell 99
Start with a problem to be formulated
To publish or not to publish...
The Babson Press must decide whether or
not to publish a book. The estimated costs
of publishing this book include a fixed cost
(in leased equipment, rent, …) of $3,000
plus a variable cost (in materials, labor, …)
of $10 for each copy printed. Projected
sales figure suggest that the book can be
sold at $16 per copy. Under what condition
should they publish this book?
J. Aieta & D. Troxell 99
Using Chart Wizard
start with a prepared worksheet
NOTE: Refer to the Power Point Presentation
Break Even Analysis - Part 1 - Constructing a Spreadsheet
J. Aieta & D. Troxell 99
Using Chart Wizard
select functions to be graphed
Highlight the cells E2:H23
NOTE: From this point on, the Excel window and
only part of the spreadsheet will be displayed
J. Aieta & D. Troxell 99
Using Chart Wizard
initiate Chart Wizard
Click on the Chart Wizard icon
J. Aieta & D. Troxell 99
Using Chart Wizard
initiate Chart Wizard
NOTE: From this point on, only the
Chart Wizard window will be displayed
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 1: select chart’s type
Make sure that the Standard Types tab is being displayed
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 1: select chart’s type
In the Chart type: box, click on XY (Scatter)
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 1: select chart’s type
On the Chart sub-type: area, click on the box displaying smooth curves
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 1: select chart’s type
Click on Next >
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 2: set chart’s source data
Accept the defaults
by clicking on
Next >
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 3: set chart’s options
Make sure that the Titles tab is being displayed
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 3: set chart’s options
Click on Chart title: box and type
Break Even Analysis
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 3: set chart’s options
NOTE: The title is displayed on the chart
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 3: set chart’s options
Click on Value (X) Axis box and type
quantity
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 3: set chart’s options
NOTE: The horizontal axis is labeled
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 3: set chart’s options
Click on Next >
J. Aieta & D. Troxell 99
Using Chart Wizard
Step 4: set chart’s location
Accept the default (the chart will appear
on your worksheet) and click on Finish
J. Aieta & D. Troxell 99
Using Chart Wizard
format the legend
Right-click on the legend box
J. Aieta & D. Troxell 99
Using Chart Wizard
format the legend
Click on Format Legend...
J. Aieta & D. Troxell 99
Using Chart Wizard
format the legend
Click on the Placement tab
J. Aieta & D. Troxell 99
Using Chart Wizard
format the legend
Click on the Top option
J. Aieta & D. Troxell 99
Using Chart Wizard
format the legend
Click OK
J. Aieta & D. Troxell 99
Using Chart Wizard
format the legend
NOTE: The legend now appears at the top
J. Aieta & D. Troxell 99
Using Chart Wizard
format the horizontal axis
Click anywhere on the gray area,
on or below the horizontal axis
J. Aieta & D. Troxell 99
Using Chart Wizard
format the horizontal axis
Click on Format Axis...
J. Aieta & D. Troxell 99
Using Chart Wizard
format the horizontal axis
Click on the Font tab
J. Aieta & D. Troxell 99
Using Chart Wizard
format the horizontal axis
Highlight the Size: box
J. Aieta & D. Troxell 99
Using Chart Wizard
format the horizontal axis
Type 7
J. Aieta & D. Troxell 99
Using Chart Wizard
format the horizontal axis
Click OK
J. Aieta & D. Troxell 99
Using Chart Wizard
format the horizontal axis
NOTE: The font for the horizontal
axis now appears smaller (7pt)
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Right-click anywhere on the white area,
on or to the left of the vertical axis
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Click on Format Axis...
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
NOTE: Make sure that the
Font tab is being displayed
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Highlight the Size: box
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Type 7
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Click on the Number tab
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Click on the Currency option
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Click on twice to bring the
number of decimal places to 0
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
Click OK
J. Aieta & D. Troxell 99
Using Chart Wizard
format the vertical axis
NOTE: The font for the vertical axis now
appears smaller (7pt) with $ signs
J. Aieta & D. Troxell 99
Using Chart Wizard
change the color of profit function graph
Right-click anywhere on yellow line
J. Aieta & D. Troxell 99
Using Chart Wizard
change the color of profit function graph
Click on Format Data Series...
J. Aieta & D. Troxell 99
Using Chart Wizard
change the color of profit function graph
NOTE: Make sure that the
Patterns tab is being displayed
J. Aieta & D. Troxell 99
Using Chart Wizard
change the color of profit function graph
Click on the in the Color: option
J. Aieta & D. Troxell 99
Using Chart Wizard
change the color of profit function graph
Click on the selected color
J. Aieta & D. Troxell 99
Using Chart Wizard
change the color of profit function graph
Click on OK
J. Aieta & D. Troxell 99
Using Chart Wizard
change the color of profit function graph
NOTE: The profit function line is now colored green
J. Aieta & D. Troxell 99
Using Chart Wizard
change background color
Right-click anywhere on the gray
area, above the horizontal axis
J. Aieta & D. Troxell 99
Using Chart Wizard
change background color
Click on Format Plot Area...
J. Aieta & D. Troxell 99
Using Chart Wizard
change background color
Click on the selected color
J. Aieta & D. Troxell 99
Using Chart Wizard
change background color
Click on OK
J. Aieta & D. Troxell 99
Using Chart Wizard
change background color
NOTE: The background color is now pale yellow
J. Aieta & D. Troxell 99
Using Chart Wizard
move the chart
NOTE: From this point on, the spreadsheet
portion of the Excel window will be displayed
J. Aieta & D. Troxell 99
Using Chart Wizard
move the chart
Click on the white area inside the chart, on or close to the
chart’s outside boundary. Drag the chart to a desired location
J. Aieta & D. Troxell 99
Using Chart Wizard
move the chart
NOTE: The chart was moved to a new location
J. Aieta & D. Troxell 99
Using Chart Wizard
resize the chart
Click and drag the middle on the chart’s
lower outside boundary to resize it vertically
J. Aieta & D. Troxell 99
Using Chart Wizard
resize the chart
NOTE: The chart was resized vertically
J. Aieta & D. Troxell 99
Using Chart Wizard
resize the chart
Click and drag the middle on the chart’s
right outside boundary to resize it horizontally
J. Aieta & D. Troxell 99
Using Chart Wizard
resize the chart
NOTE: The chart was resized horizontally
J. Aieta & D. Troxell 99
Using Chart Wizard
interpret the chart
Cost and Revenue
lines intersect
Babson Press will break even
(500,8000)
if it prints 500 copies
(500,0)
Profit line intersects
the horizontal axis
J. Aieta & D. Troxell 99
Using Chart Wizard
modifying the chart
Enter new values
v = 3.75
F = 20,000
p=5
q_incr = 1,000
NOTE: The same spreadsheet can be used to analyze different instances
of the problem by just modifying the contents of the blue cells
J. Aieta & D. Troxell 99
Using Chart Wizard
modifying the chart
NOTE: As the contents of the blue cells are changed
both the table and the chart are automatically updated
J. Aieta & D. Troxell 99
