Can I have some Excel help please?

[spongy]

As some on here may be aware I have just started an HNC Engineering course and my first unit assignment is on Business Management Techniques.

The hypothetical question is I work for a company and am able to produce 500 units a year of a product that will have a likely selling price of £11.50.

I have......

variable costs of £5.75 per unit.
semi variable costs of £175 for up to 100 units
£350 for 101 to 300 units
£525 for 301 to 500 units.

Fixed cost of £1750 regardless of output level.

I need to know what the minimum quantity is in order to break even by drawing a break even chart.

and

What the levels of profit will be for 100, 200, 300, 400, 500 units.

I can work out the second bit but for drawing an accurate break even chart I'd like to use excel to draw up a table and use formulas and use that info to plot a line graph to give me an exact answer and this where I am getting stuck.

I think the formula should be .....

(Unit quantity*selling price) - ((variable cost*unit quantity) + fixed cost + semi variable cost according to quantity)

How do I get a formula to work?

Will it be a vLookUP formula? But I also need to factor in a IF formula i think as the semi variable costs vary depending on how many units. Can I do a combination of both formulas?

I haven't used Excel since I left school in 1995 so am finding this all a bit tough.

Im not asking anyone to answer the questions for me as I need to learn this, just some help with the excel would be most welcomed!!

Cheers.

 

[KingKev]

Try this
http://m.wikihow.com/Do-a-Break-Even-Chart-in-Excel

You draw the income and costs lines separately and where they intersect that's your b-e volume

 

[Worthingite]

I think it's more of a pivot table thing, but I can't remember how to do it. A vlookup might work though

 

[spongy]

How about a vlookup formula for each cell for the 100, 200, 300, 400, 500 units and then an IF depending on the range 0-100, 101-300, 301-500?

I've tried several different ways and keep getting ##value##.

Unless I'm not setting my data table up properly and the formulas are trying to look in the wrong places for vlookup and if's?

I've been using Excel again for about 2 hours and I already remember hating it back then too.

 

[Dorset Seagull]

How about a vlookup formula for each cell for the 100, 200, 300, 400, 500 units and then an IF depending on the range 0-100, 101-300, 301-500?

I've tried several different ways and keep getting ##value##.

Unless I'm not setting my data table up properly and the formulas are trying to look in the wrong places for vlookup and if's?

I've been using Excel again for about 2 hours and I already remember hating it back then too.

I love Excel it's my favourite piece of software and the one I use most for calculating my supplier orders. I was in 7th heaven when someone showed me vlookup as it helped me streamline so many processes.

However although quite good at some things I use frequently you are straying into unknown territory as far as I am concerned. Pivot tables....always thought they might be useful but have never bothered to explore them

 

[s-p]

The key to solving this is understanding what information you need to display and then work backwards from there.

• Units
• Income
• Total Cost

Units: You need to calculate the values at 100,200,300,400,500, so they becomes the 5 fixed rows in your spreadsheet.

Income: =Units*11.50

To simplify the problem, I would calculate the variable, semi-variable costs and fixed costs in separate columns.

Variable Costs: =Units*5.75

Semi-Variable Costs: You have two options here. One is to manually set the value for each row, or you can set this via a formula like: =IF(Units<=100,175,IF(Units>300,525,350))

Fixed Costs: 1750


Total Cost: =VariableCosts + Semi-VariableCosts + FixedCosts

You can then plot a line graph using Units, Income and Total Costs to understand where Income and Total Costs intersect and provide the break even value.

Units	Variable Costs	Semi-Variable Costs	Fixed Costs	Total Cost	Income		Profit
100	£XXX.XX	 	£175.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
200	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
300	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
400	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX
500	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX

 

[Diego Napier]

The key to solving this is understanding what information you need to display and then work backwards from there.

• Units
• Income
• Total Cost

Units: You need to calculate the values at 100,200,300,400,500, so they becomes the 5 fixed rows in your spreadsheet.

Income: =Units*11.50

To simplify the problem, I would calculate the variable, semi-variable costs and fixed costs in separate columns.

Variable Costs: =Units*5.75

Semi-Variable Costs: You have two options here. One is to manually set the value for each row, or you can set this via a formula like: =IF(Units<=100,175,IF(Units>300,525,350))

Fixed Costs: 1750


Total Cost: =VariableCosts + Semi-VariableCosts + FixedCosts

You can then plot a line graph using Units, Income and Total Costs to understand where Income and Total Costs intersect and provide the break even value.

Units    Variable Costs    Semi-Variable Costs    Fixed Costs    Total Cost    Income        Profit
100    £XXX.XX         £175.00          £1,750.00     £XXXX.XX     £XXXX.XX     £XXXX.XX 
200    £XXX.XX     £350.00          £1,750.00     £XXXX.XX     £XXXX.XX     £XXXX.XX 
300    £XXX.XX     £350.00          £1,750.00     £XXXX.XX     £XXXX.XX     £XXXX.XX 
400    £XXX.XX     £525.00          £1,750.00     £XXXX.XX     £XXXX.XX     £XXXX.XX
500    £XXX.XX     £525.00          £1,750.00     £XXXX.XX     £XXXX.XX     £XXXX.XX

This must be one of the best ever first posts!

 

[s-p]

This must be one of the best ever first posts!

Bless you.

Just to expand on my original post, if you want a more accurate graph you'll want to see the step change in semi-variable costs (i.e. 100 to 101, 300 to 301). You can display this by expanding the data range as follows:

Units	Variable Costs	Semi-Variable Costs	Fixed Costs	Total Cost	Income		Profit
100	£XXX.XX	 	£175.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
101	£XXX.XX	 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
200	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
300	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
301	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX
400	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX
500	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX

Then used a scatter graph with straight lines and markers.

 

[Lord Bracknell]

This must be one of the best ever first posts!

... and it has been brewing for seven and a half years.

 

[Steve in Japan]

NSC is going soft. People will even do your homework for you, instead of telling you to go away and work it out for yourself.

 

[whitelion]

... and it has been brewing for seven and a half years.

Very appropriate considering today's opponents.

 

[spongy]

The key to solving this is understanding what information you need to display and then work backwards from there.

• Units
• Income
• Total Cost

Units: You need to calculate the values at 100,200,300,400,500, so they becomes the 5 fixed rows in your spreadsheet.

Income: =Units*11.50

To simplify the problem, I would calculate the variable, semi-variable costs and fixed costs in separate columns.

Variable Costs: =Units*5.75

Semi-Variable Costs: You have two options here. One is to manually set the value for each row, or you can set this via a formula like: =IF(Units<=100,175,IF(Units>300,525,350))

Fixed Costs: 1750


Total Cost: =VariableCosts + Semi-VariableCosts + FixedCosts

You can then plot a line graph using Units, Income and Total Costs to understand where Income and Total Costs intersect and provide the break even value.

Units	Variable Costs	Semi-Variable Costs	Fixed Costs	Total Cost	Income		Profit
100	£XXX.XX	 	£175.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
200	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
300	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
400	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX
500	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX

Bless you.

Just to expand on my original post, if you want a more accurate graph you'll want to see the step change in semi-variable costs (i.e. 100 to 101, 300 to 301). You can display this by expanding the data range as follows:

Units	Variable Costs	Semi-Variable Costs	Fixed Costs	Total Cost	Income		Profit
100	£XXX.XX	 	£175.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
101	£XXX.XX	 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
200	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
300	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX 
301	£XXX.XX 	£350.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX
400	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX
500	£XXX.XX 	£525.00 	 	£1,750.00 	£XXXX.XX 	£XXXX.XX 	£XXXX.XX

Then used a scatter graph with straight lines and markers.

You are an absolute star my friend!

NSC is going soft. People will even do your homework for you, instead of telling you to go away and work it out for yourself.

How exactly has he done that then? He hasn't answered my questions with figures has he? He's just told me how to use Excel to find out my own figures which hopefully I can now do.

I've still got to analyse and discuss the findings, look at profitability, gross margins, materials, machine speeds and feeds to improve production times and try and improve the gross unit production quantity. But hey....

 

[jakarta]

I've still got to analyse and discuss the findings, look at profitability, gross margins, materials, machine speeds and feeds to improve production times and try and improve the gross unit production quantity. But hey....

Have you tried switching it off and then switching it on again?

 

[Hampster Gull]

I love Excel it's my favourite piece of software and the one I use most for calculating my supplier orders. I was in 7th heaven when someone showed me vlookup as it helped me streamline so many processes.

However although quite good at some things I use frequently you are straying into unknown territory as far as I am concerned. Pivot tables....always thought they might be useful but have never bothered to explore them

Pivot tables are great for analysing data sets

 

[spongy]

I'm going you have to Google what a pivot table is....

 

[RexCathedra]

The key to solving this is understanding what information you need to display and then work backwards from there.

Truer words were never spoken.

Excel is one of those things where plunge in the deep end and tweak it as you go is buying trouble. I find myself sometimes even planning out using -- gasp -- paper and pencil first.

 

[The Brighton Bear]

Truer words were never spoken.

Excel is one of those things where plunge in the deep end and tweak it as you go is buying trouble. I find myself sometimes even planning out using -- gasp -- paper and pencil first.