Can I have some Excel help please?

spongy · Sep 17, 2016

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 · Sep 17, 2016

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 · Sep 17, 2016

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 · Sep 17, 2016

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 · Sep 17, 2016

spongy said:
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 · Sep 17, 2016

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.

Code:

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 · Sep 17, 2016

s-p said:
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.

Code:

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! :clap:

s-p · Sep 17, 2016

Diego Napier said:
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:

Code:

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 · Sep 17, 2016

Diego Napier said:
This must be one of the best ever first posts!

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

Steve in Japan · Sep 17, 2016

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 · Sep 17, 2016

Lord Bracknell said:
... and it has been brewing for seven and a half years.

Very appropriate considering today's opponents.

spongy · Sep 17, 2016

s-p said:
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.

Code:

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

s-p said:
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:

Code:

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!

Steve in Japan said:
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 · Sep 17, 2016

spongy said:
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? :wink:

Hampster Gull · Sep 17, 2016

Dorset Seagull said:
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 · Sep 17, 2016

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

RexCathedra · Sep 17, 2016

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 · Sep 17, 2016

RexCathedra said:
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.

Got something to say or just want fewer pesky ads? Join us... 😊

Can I have some Excel help please?

Well-known member

Well-known member

Sexy Pete... :D

Well-known member

Once Dolphin, Now Seagull

New member

Well-known member

New member

On fire

Well-known member

New member

Well-known member

Well-known member

New member

Well-known member

Aurea Mediocritas

Come on Kylie, get a grip

Share this page