Break-even Analysis Part 3 Constructing Graphs by drk68785

VIEWS: 20 PAGES: 63

									                           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

								
To top