I was wondering if I could get some help on how to create a CVP graph in
Excel 2007.

A cost volume profit shows how costs, revenues, and profits vary with volume
(sales). You can either plot total cost, or fixed and variable costs, which
add up to total costs.

One way to show this is to make a break-even chart. Put unit sales (number
of items sold) in the first column, fixed costs in the second (which are a
constant), variable costs in the third column (these are typically a
straight line through zero), and in the fourth insert formulas that sum
fixed and variable costs to make total costs. If you want you can skip the
fixed and variable costs. In the next column, enter sales revenue. Select
the sales and cost data, and create an XY chart. The total costs is an
upward sloping line, and you'd better hope sales revenue is also upward
sloping, but steeper. Where the lines cross is the breakeven point, above
which additional sales bring in more revenue than they cost.

You can also show profit, which is revenue minus costs. In the column after
revenue above, enter formulas that subtract cost from revenue. Make another
XY chart, again using units sold as X, but this time use Profit as your Y
value. Profit starts out negative, but it crosses zero at the breakeven
point and increases further as units sold increases.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"cch54" wrote in message
...
I was wondering if I could get some help on how to create a CVP graph in
Excel 2007.

Sure, I'd be happy to help you create a Cost Volume Profit (CVP) graph in Excel 2007. Here are the steps you can follow:

1. Open a new Excel workbook and enter your data in a table. Your table should have columns for units sold, sales price per unit, variable cost per unit, fixed costs, and total costs. Be sure to label your columns.
   
2. Calculate your total revenue by multiplying the units sold by the sales price per unit. You can do this by entering a formula in a new column next to your units sold and sales price per unit columns. For example, if your units sold are in column A and your sales price per unit is in column B, your formula in column C would be "
   Formula:

   =A*B 

   ".
   
3. Calculate your total variable costs by multiplying the units sold by the variable cost per unit. You can do this by entering a formula in a new column next to your units sold and variable cost per unit columns. For example, if your units sold are in column A and your variable cost per unit is in column D, your formula in column E would be "
   Formula:

   =A*D 

   ".
   
4. Calculate your total costs by adding your fixed costs to your total variable costs. You can do this by entering a formula in a new column next to your fixed costs and total variable costs columns. For example, if your fixed costs are in cell F1 and your total variable costs are in cell E2, your formula in cell G2 would be "
   Formula:

   =F1+E2 

   ".
   
5. Create a new column for your profit or loss by subtracting your total costs from your total revenue. You can do this by entering a formula in a new column next to your total revenue and total costs columns. For example, if your total revenue is in cell C2 and your total costs are in cell G2, your formula in cell H2 would be "
   Formula:

   =C2-G2 

   ".
   
6. Select all of your data, including your column labels, and click on the "Insert" tab in the Excel ribbon.
   
7. Click on the "Line" chart type and select the first option, which is a basic line chart.
   
8. Your chart will now be created. You can customize it by adding a chart title, axis titles, and formatting the chart as needed.
   
9. To add a break-even point line to your chart, you can add a new series to your chart. Enter the formula for your break-even point in a new column, such as "
   Formula:

   =F1/(B2-D2) 

   ". This formula assumes that your fixed costs are in cell F1, your sales price per unit is in cell B2, and your variable cost per unit is in cell D2.
   
10. Select your chart and click on the "Design" tab in the Excel ribbon.
    
11. Click on the "Select Data" button in the "Data" group.
    
12. Click on the "Add" button in the "Legend Entries (Series)" section.
    
13. Enter a name for your new series, such as "Break-Even Point".
    
14. Enter the range for your new series, which should be the column with your break-even point formula.
    
15. Click "OK" to close the "Select Data Source" dialog box.
    
16. Your break-even point line should now be added to your chart. You can format it as needed by selecting the line and using the formatting options in the Excel ribbon.

That's it! You should now have a CVP graph in Excel 2007. Let me know if you have any questions or need further assistance.