Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Breakeven Chart - Wont Start At Zero

Forum Register
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 self-taught - 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

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
Create a Column Chart with a Macro in Excel
- This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Create a Bar Chart With a Macro in Excel
- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

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 set-up 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 pie-of-bar 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 pie-of-bar 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 3-D 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 break-even 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?!