Breakeven Chart  Wont Start At Zero


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hi guys.
Desperate for some help in producing a breakeven chart for my assignment. I have a data table using the values given but the chart won't start at zero ie when sales are zero, variable costs are zero but fixed costs are 17500.
I'm not very good at Excel  having been largely selftaught  so I would appreciate very simple instructions if anyone can help!!
I hope I have attached a copy of the data table and graph to show what I've done.
Many many thanks for anyone who can help.
Similar Excel Video Tutorials
Count Months from Start and End Date
 Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm
Formula to Count Months from Start and End Date. See the functions DATEDIF, DAT ...
4 ways to Add New Data To A Chart
 See how to add data to a chart after the chart has already been created: 4 methods:
1) Copy data, click on chart, Paste
2) Click on chart, d ...
Helpful Excel Macros
Get Values from a Chart
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Similar Topics
I need to create a breakeven chart in Excel 2007. I have my total variable costs, total fixed costs and sales price. The chart needs to show how many units I need to break even. My intial data has the sales prices changing over three years. Any help would be appreciated
Jamie
Hi Excel genuises
I am trying to calculate a breakeven point with total fixed costs and variable number of people. With the detail below how is it possible to calculate a breakeven number of people and the value and then the number of people and value to show 15% margin?
REVENUE
Package price
$ 6.99
per person
Our revenue
$ 3.50
per person
COST OF SALES
Cost
$ 1.75
per person
Net Revenue
$ 1.75
per person
Total cost
Cost 1
(2084.00)
Cost 2
(54.50)
TOTAL COS
(2140.25)
Overhead
(4122.00)
CAPEX
(103.00)
TOTAL COSTS
(6365.25)
High Guys,
I need some help writing a few formulas in the attached spreadsheet. The Data_Input sheet where all the information will come from I have setup Name ranges for each category.
I need to write the following formulas in the calculation sheet.
1 Fixed costs per week
2 Variable costs per drink in normal hours
3 Variable Costs per Drink in Overtime
4 The breakeven point in the number of 375ml drinks per week Inoring overtime
5 If the selling price per drinks changes, while all other data remains the same, what is the minimum selling price per drink below which overtime will be required to breakeven. I will need an If statement to test whether overtime is required.
6 A formula that calculates the profit or loss in normal hours
7 Graph_Data sheet will need formulas to allow me to graph it
8 I need to calculate the number of drinks that must be sold to achieve that target profit of $45,000.
9 A formular that will warn me that a target profit I keyed in will require more that 15 hours overtime.
Thank You for Your Held in advance
Runner77
Hi,
I am trying to create a stacked bar chart similar to a pieofbar chart, but am unable to find a way to do this.
Specifically, this is what I'm looking to do:
1. I have the following data set: Total Fixed Costs=$60 and Total Variable costs=$30. The fixed cost component is made up of: Labor=$20, Maintenance=$30 and Supplies=$10.
2. I want to create a stacked bar chart, where my first stacked bar has the total fixed costs($60) and total variable costs($30) stacked on top of each other. Now I want my second stacked bar to display the details of the fixed costs ( i.e. Labor, Maintenance & Supplies) with lines going from the first bar to the second.
I was able to acheive the above with a pieofbar chart, where the pie chart showed the fixed & variable cost breakup and the stacked bar chart showed the breakup of the fixed costs.
Any help is much appreciated.
Thanks.
Hi
I have put together some breakeven analysis with a small P&L but as the breakeven number of sales and the P&L is dependant on the inputs the numbers do not remain constant even when formularized.
Is there a way (macro perhaps?) that I can have the breakeven number of sales AND the P&L change depending on the inputs as per below?
Many thanks
Inputs
Proposed price
$6.99
Revenue share
50%
New Revenue
$3.50
Revenue share to X
50%
X share
$1.75
Fixed cost
$6,471.00
Variable cost
$1
Breakeven P&L
REVENUE
Package price
60,511.42
Revenue
30,255.71
COST OF SALES
Revenue share
15,127.86
Variable cost per sale
8,656.86
Fixed cost
6,471.00
TOTAL COST OF SALES
30,255.71
Margin
0.00
Breakeven
%
Number of sales
0
8,657
15
9,955
Hi guys, I am doing a breakeven analysis and would like to paint certain areas of my chart. I want the profit area in blue and the loss area in red (loss area is the area to the left of the breakeven intersection; profit area is the area to the right of the breakeven intersection). Could you let me know how I could do this? I have attached the spreadsheet I am working on.
I have the Excel 2007 version but saved as and uploaded a 2003 version for those who don't have the latest one.
Thank you very much for your help!
Marcus
Hi,
I have a chart that automatically changes when the information changes. However i want to drop a line vertically and horizontally where my breakeven point is.
The only way i how overcome this is by manually drawing the line everytime the info changes.
Is there any possibility that excel can understand my graph and where the lines cross/intercept and automatically draw my vertical and horizontal lines?
Many Thanks
Dave
Hey guys this is my first post, found this site and it looks very helpful :D I have a homework assignment due soon. I am stuck on a step in the instructions our teacher gave.
Basically I was asked to create a 3D Column Chart using data that the teacher gives, change up the options/fonts a bit. I am now up to step 8 and it says:
8. Insert a data table without legend keys below the 3D chart to provide data values so that the reader is not confused about the relative sizes of the different columns. (Hint: Use the Data Table button in the Labels group on the Chart Tools Layout tab.)
I tried just clicking on it, going to the table and selecting "Show Data Table" and nothing happens!
I am so lost right now, could anyone help me out? Thanks for reading :]
Is it possible to show only selected data points in an Excal chart's data table?
I have a chart with three series of data which appear on the chart but I want to show only one set of values on the data table.
For instance:
Chart values of 2, 6, 8 for period 1
Graph all three values in period 1
Only show value of 6 in the data table
Hope that makes sense.
Thanks,
Bob
CODE
JAN
FEB
MAR
3456
23
43
12
6654
54
32
52
5432
76
23
54
6543
54
23
54
ITEM
JAN
FEB
MARCH
SALES COSTS
1560
2598
1200
FIXED COSTS
187
897
120
ACCOUNTING COSTS
45
12
15
Hi here is a basic version of what I need to do. I need to allocate costs for each code by percentage of sales for each month into a file in the format below.
Eg for the highlited item:
=23/(23+54+76+54)*1560=173.33333
For any item where the ammount = 0 I dont want an entry made.
Can anyone help? thanks
CODE
MONTH
ITEM
AMMOUNT
3456
01
SALES COSTS
173.33333
3456
01
FIXED COSTS
20.777778
3456
01
ACCOUNTING COSTS
5
3456
02
SALES COSTS
923.2562
3456
02
FIXED COSTS
318.7686
3456
02
ACCOUNTING COSTS
4.2644628
3456
03
SALES COSTS
83.72093
3456
03
FIXED COSTS
8.372093
3456
03
ACCOUNTING COSTS
1.0465116
I have attached a picture to try an explain what I need to do.
I would like to put a line between the negative a positive values to indicate the breakeven point.
I would this line to apply to the whole column. The table is 30 rows.
I understand that I can use conditional formatting to apply the line but I cannot figure out how to write the statement.
I want to say: =IF(AND(M1<0,N1>0)) then put a line to the left of N to show the breakeven (I know I will do this with borders no problem and there seconf and 3rd Logic is missing in the forumlae)
Am I complicating the issue or have I missed something completely? The statement needs to be applied from B1 to P1 as the value will breakeven at different points.
Thanks
Hi,
I have a large CSV data file that I am using as an "external data source"
for a Pivot table in an (autoupdate) template.
That works fine. It's the sales file created automatically by an
application. So it's "sales.csv" and my template with the Pivot table is
"sales.xlt".
But I also have costs, so I have another file where costs are recorded
manually. The data is in "Costs.xls" and I will have another Pivot table
where I analyse the costs : "Costs.xlt"
Now I want to offset Costs against Sales, so I am looking for some way to
get a new pivot table to read both the Sales.CSV and the Costs.XLS data and
combine the results.
Anyone here know how to do that ?
Or, can anyone point me to a site where that is explained.
TIA
Erasmus
Hi i need a complex chart with more entries.
I need some links with simple tutorials.
One of my table looks like this:
name....quantity.....value
....................2008..........................2009...............2010
name1......123......222.04......;......332..........654.5
name2.......32......338.22.......;.......12..........445.21
name3......984......112.01......;......877..........322.54
I have this table with 3 years, and another with months.
I don't want a chart for each row. I need a chart for all rows(names).
A chart with lines for every name, and each line to have a different color.
I haven't worked with chart before and i don't know were to start.
Thanks in advance.
Hi All,
I've found great explanations regarding Dynamic Charts (e.g. http://www.contextures.com/xlNames02.html or http://peltiertech.com/Excel/Charts/...hartLinks.html) however all examples refer to data within a table than extends vertically.
I however would like to creat a dynamic chart on a table with data going horizontally. I assume you have to alter the OFFSET formula to Count for the data going horizontally, but not sure how to do this?? For example, please refer to attached Chart sample in 'Sheet 1'.
Does anyone know how to kick start me on this??
Is it also possible to automatically collapse the x range on graph (e.g. data in attached only appears till July, so would like to only show chart till July, rather than Dec. However august data is entered, the chart would extend to show aug)
Regards
Tim
Hi,
I have a spreadsheet of store sales, costs and average sales. The data for each store is contained within a single tab, so I have 3 tabs for 3 stores.
I need to be able to create two graphs on each tab. The first graph containing sales and average sales, and the second graph containing costs and average sales.
I recorded a macro, which works fine for creating the first graph. But when I copy the code and add it too the end (and change the series to the cost data), I get errors.
I would also like to be able to position these graphs. Graph 1  cells A34 to F50 and Graph 2  cells H34  O50.
Of course I will need to rerun this code for the remaining stores as well.
How do I get a stacked column to go over x axis the way I want it to?
I'm looking to show the breakdown of sales into the costs  cost of goods, shipping, rent, staff costs, etc.. with profit the balance to add upto 100%.
This works fine in a stacked column where there is a profit, however, I have a year when there was a loss, the costs become greater than 100%  but I still want to show 100% above the x axis, with the remainder below.
Using the the data in a stacked chart, the year with a loss shows less than 100% above the x axis.
I've no doubt this has already been answered  but I couldn't find an answer in previous postings (I've previously found waterfall chart help).
Cheers,
Paul
Dear experts,
How can I get rid of N/As on the chart?
In the table I have formulas returning NA() if no data is available. The chart treats N/As the way I want it  not as zeros, but it makes the chart ugly.
I need to have data values on the chart (whenever available), and those guys come together with #N/As for other parts of the chart. And I cannot use the line chart (it seems to be doing what I am asking for) instead of the bar chart.
Thanks in advance.
I'm sure I'm overthinking this, and I've tried the Goal Seek breakeven analysis, but no joy.
I have a consignment store with fixed expenses (i.e., rent) and variable expenses (i.e., commission paid to consignors at a rate of 40%).
I'm trying to write a formula that tells me what I need in sales to cover my costs. For example, monthly fixed expenses are $1000. To cover that cost, how much do I need to sell, including payment of commission, to cover that $1000.
Hello, hope you can help.
I am trying to do a breakeven analysis in the form of a graph. I am
extreamly stuck on what to do
Ive searched help and it gives me the option of downloading a wrksheet to
input all the details but that is not what i am wanting. I just want to enter
the details in excel as I already know the amounts and then to be presented
with a simple graph with 3 entries.
What do I search under to help me input 3 sources of information in like the
fixed costs, selling costs and variable costs?
Im realy stuck!!!
Hello,
I'm trying to find the beakeven point for variable cash flows.
Cash Flows per year
Fixed Cost
1 yr
2 yr
3 yr
4 yr
5 yr
6 yr
7 yr
22,000,000
5,000,000
5,500,000
3,800,000
7,500,000
1,200,000
4,000,000
3,500,000
I know my breakeven point is around 4 years, but is there a formula I can use to calculate this?
I will be using this formula in a template where the fixed cost and cash flows will be changing.
My goal is to have the breakeven point calculated no mater what the Fixed Cost and Cash Flows display.
Many clients are concerned at what sales price will return a zero profit so that they can cash out and hit the road.
A simple one for you brainiacs. "A"  B  C + D = 0.
This means what sales price "A" minus closing costs and prorated taxes(B and C) plus escrow refunds (D) will equal "zero" . This is a break even formula that will show what price I must sell a house for after I back out expenses but add the escrow in. This is real important for a homeowner who has live in a home for only a couple of year and wants to sell but doesn't want to come to the table w/ 10 grand. Thanks again
Hi, I have one table where the data are produced by formulas. I want to make stake column chart using this data. I have attached the sample worksheet here which includes data as well as final chart also. which I made from the manual data. the scale of the chart is start from 8/1/2010. Please help me to find out what I am doing wrong making this chart.
Hi all,
I would like to have an Excel chart automatically update itself off of a table of data. Every month new information is added to the excel table that the chart is based off of and I do not want to have to change the source data each time updating the information. There are five metrics in the chart as well as changing dates that I would also like to have automatically update. I would like the chart to show the last 12 months of data. I have attached and example of the excel spreadsheet with the table and chart. I feel like there is macro you can write but I am not very good at VBA.
Thank you in advance for your help!!
bmatthie11Forum Doc.xls
Hi all,
I am currently using Excel 2007.
I have created a stacking bar chart based on sales information for the past 5 years by month. Currently, I have the chart and the data table displayed on the graph (chart layout) with the averages displayed as a second axis and I am wondering if it's possible for it to hightlight the highest sold months in the data table?
Thanks,
 Sam
Hi,
I'm not fantastic on Excel but am slowly learning so apologies if this is impossible or something really simple!!
I have a pie chart that takes information from a table with formulas so that when I update the table the pie chart also updates.
Some of the data in the table has values of 0 due to the formulas, this will increase as my table collects more info over the weeks/months. I have manged to work out how to hide data labels on the pie chart with a value of 0 as it made it look very messy. What I want to do is have the data labels on the pie chart show the value and the data title from the table (names in this case) but not show the 0 or the names of the people with 0. Is this possible, does this actually make sense?!