Break Even Analysis problem

[Cheeseburger]

Hey Everyone I am a new intern at a company and I am trying to work on a spreadsheet that will do some calculations in excel but I am having trouble with the calculations and would be greatful for any advice.

Here is the situation in simplest terms to try and help you guys understand the problem. I have a machine that cuts metal. But it cuts metal at a decreasing rate of efficiency and eventually needs to be replaced. Here is an example of what I mean. A brand new machine cuts metal at an efficiency of 2 pounds of metal used per foot of metal cut. Eventually the machine becomes less efficient and it requires 3 pounds of metal to cut one foot and so on. Metal is expensive, so as more and more metal is used to cut one foot, the proccess is becoming less and less efficient and more and more expensive. Eventually we are better off just purchasing a new machine, which for this example lets say costs 10,000 with a scrap value of 2000. The rate at which the machine become less efficient is constant at .01 pounds more of metal required per foot cut after every 1000 feet cut.

Here is all the required informaton

Cost of new machine = $10,000
Salvage value of used machine - $2000
Rate of efficiency of Brand new machine = 2 pounds of metal required per foot cut
Rate at which machine becomes less efficient = .01 lbs of metal per foot more required every 1000 feet of metal cut
cost of metal per foot = $2

So if you subtract the salvage value of $2000, the cost in the end of buying and using a new machine is $8000. So what I need to calculate in excel is the break even point (in feet produced) when the cost of using more and more metal every foot produced due to a less efficient machine equals the cost of a new machine ($8000). Basically, When is the best time to buy a new machine.

If anyone has any advice I would greatly appreciate it.

[Cheeseburger]

Any ideas?

[MrShorty]

Have you looked at the Solver/Goal Seek utilities?

[Pauleyb]

Why does this remind me of a homework problem I had in an Ops class?

I think this can be solved with a few simple equations, but I am trying to understand where you think the break even point is. Is it when the cumulative metal used equals $8K or when a certain job of, let's say, 1000' of cut metal is equal to $8k? I assume it is the former.

First, determine a function for the efficiency based upon the number of feet cut (variable name: nfc)
Second, determine a function that calculates the cost of metal used with the input parameter of nfc. There is a trick to this, since the nfc also determines the efficiency. Go through your head of how much 1 foot would cost. Then think how much 1000' would cost. Then think how much 10K' would cost. That should tell you how you need to handle the efficiency for this function. Also, (and this is a big hint) don't fall into the trap of including the 'base' cost of the metal used (i.e. the intial 2 ft). You want to focus on the 'extra' metal used due to the inefficiencies, since if there were no inefficiencies, you would keep using this machine regardless of how much metal you have run through it.


Once you have that function, solve it for $8000, either using goal seek or some simple algebra.

I hesitate to just provide the formula, since this seems like a mental exercise your bosses expect you to be able to achieve.

Pauley

[Cheeseburger]

This isn't really a mental exercise because my bosses don't have the answer to the question. They need away to figure out when to replace their machines, and I thought hmm maybe I could do something like this in excel (my idea not theirs). I just dont know how I would do the formulas. I will try and work through the problem with your advice but if you feel generous with helping with the formulas I would appreciate it. This isn't an exercise like a homework assignment, this is a real world problem that they didn't have an answer for and I said "let me see what I can come up with." Doing something like this was my solution, I just need help with how I would do the formulas.

Honestly the problem is much more complicated than the one I presented. There a number of issues that make it not nearly as simple that I need to see if I can come up with an answer to. For example, ever machines rate of decline is different, so I need to develop a unique rate of decline based off current performance of the machine. Also some machines break early and need to be replaced. There are a lot of complications that this solution to the problem may not even be feasible, I just needed this as a starting point to determine if this is the way to go.

Thank you for the advice.

[MarvinP]

Hi Guys,

I used to teach high school math and got good at word problems. It becomes a REAL problem when you can't look in the back of the book for an answer. Most people want to start by writing down the formula. This isn't how REAL problems are solved. I had a college class about solving problems where I leaned this technique. It comes from one of the last great pure mathematicians http://en.wikipedia.org/wiki/How_to_Solve_It.

Here are my steps.

1. Look for the question mark in the problem first. In Cheezeburgers text he has no question marks. He doesn't know what he needs to find.
2. Make a simpler problem. My problem would be, "How much does it cost to cut 10 pounds of metal?"

The method starts by using a guess. In my classes I would always use 10! You and I know that 10 is the wrong answer but checking to see if 10 is the correct answer then gives the process for building the formula.

Because Cheeseberger didn't ask a simple question, it was hidden in the "Break Even analysis" it may be two questions. Guess a number! See if it is the correct guess. If it isn't you now have a method for checking

After re-reading the problem it looks like the question is WHEN. Guess 1 Year. See if that is a good guess. Break the problem down into it's parts. Read the link.

hope this helps.

BTW - most of my students never liked to guess, they always just wanted the formula. Students that couldn't guess and answer and check it, never got very good at word problems.

[Pauleyb]

Okay, see if this makes sense and point out any flaws.

ftcut = # of feet cut

Your efficiency adder formula is: effadd = .01*ftcut/1000

The trick in determining the effadd across multiple feet is to use the average effadd for all of the metal used. This ends up being effadd/2 since the first 'foot' has an effadd of 0 and the last foot would be whatever the formula above generates. Dividing by 2 should be the average across all ftcut.

The amount of wasted metal is: wm = ftcut*effadd/2. I believe this is accurate, since, if the effadd were 0 (i.e. no loss of efficiency), then the formula above would be 0, which would imply you should never replace the equipment.

The cost of the waste is: wc = wm*metal cost = $2*wm.

Setting wc = to $8K and solving for ftcut should yield the number of ftcut where the metal lost equals $8K.

expanding the formulas to be ftcut:
$8000 = $2 * ftcut * (0.01 * ftcut/10000)/2

Solving for ftcut
ftcut = (8000*100000)^0.5 = 28,284 ft

Now, I could argue that this is not the true break even, and I think that is the bigger issue of this problem. The calculations above just provide the number of feet cut when the total ftcut inefficiencies could have paid for a new machine. Typically, a break even analysis in performed upon two different offerings. In this case it would compare a new machine with one that has already cut some amount of metal and has some inefficiencies. Putting it another way, comparing a new machine against one that has just cut 1 foot of metal (and thus has a slight inefficiency) will show that after a certain amount of feet (probably in the billions) it would have been better to scrap the 'old' machine and buy the new one.

As you say, there are many other variables, wear & tear, depreciation, opportunity cost, etc. Generally, you should have a corporate rate of return on investments and also use that in determining when equipment should be replaced.

Pauley

[Pauleyb]

I corrected the final calculation a bit, since I had forgotten the divide by two for the efficiency...

To expound more on the break even ananlysis. Let's say you have Machine A which has cut some amount of feet and thus has some inefficiencies. Now you want to compare it with a new machine (Machine B). The break even would be, starting today, when would the inefficiencies of MachA minus the inefficiencies of MachB equal $8K. The break even here would be in the number of feet cut. You would need to convert feet cut to time based upon your typical run rates. Let's say that time is t1 and the age of Machine A is t0.

The more feet cut by MachA the sooner purchasing MachB is justified (i.e. t1 shrinks).

Looking at your corporate rate of return, would help answer what is the appropriate t1, since the $8K is really an investment and management should weigh whether putting the $8K into 'the bank' and getting their rate of return is a better investment than buying the new equipment Where this becomes somewhat circular is that the $8K investment changes over time, as well. So, if your calculation above says that the break even is 4 years from now (for the waste to equal $8K), well the $8K in the bank would have grown through interest, so you really should wait a bit longer until your waste cost equals the investment in the bank.

Using some math, you could find the time where purchasing MachB is a better investment than putting the money in the bank for a machine that is t0 old. You want to solve for t0, which would then derive t1. You are now outside the realm of Excel help and should talk to your corporate accountant.

Pauley

[Cheeseburger]

thank you for the help and i am starting to understand the logic behind this but unfortunately the problem slightly changed...

the loss from inneficiency is measured in lbs the machine produces not feet.

also i can only give you the added weight due to inefficiency per lb the machine produces.

For example, I can tell you.... For every new pound of metal the machine produces, .02 lbs of waste is added

I would guess that .02 times the number of lbs produced would become the new efficiency adder... but then what?

[Cheeseburger]

edit*********