Is this a floating decimal problem...how would you solve it?

[trolle]

Working on a business case template and I have calculated a break-even price. When I enter this price in my template I get an end result of 11 Euros...it's frustrating that it doesn't give me 0 as it should (let us assume that my math is correct).

There are a bunch of difference currencies in my template so I start with a simple 3x3 matrix with the currency units: USD, EUR, and DKK (Danish kroner). I want everything in EUR so in all my calculations I am constantly changing the currencies to EUR. My currencies are with 6 decimal places.

If I change the DKK-EUR currency from 0.134367 to 0.134 my end result changes from 11 to -35,869.

The questions are:
1. Is this a floating decimal problem? I have read up on this.
2. How would I solve this, if at all possible? Can't really grasp my head around it (the option of changing the settings in Excel did not yield anything useful...it got worse). How would you solve it?

[MrShorty]

Hard to say if it is a floating point error, but my first inclination is that it is not. You have an input that is changing by 4/1300~0.4%, but the output changes by 44/11~400%. Using double precision, that seems like a very large error propagation.

If it is due to a floating point error, the first one that comes to mind is that you have a "subtraction involving two very nearly equal numbers" step somewhere in the sequence of calculations. You have not shared any of the details of your calculation, so we cannot see if this is the case, but you should be able to step through your calculations to see if this is possible.

I expect that, whether it is floating point or some other error, finding this bug and solving it is going to require breaking down your calculation sequence into manageable steps, testing each step to see that it is correct, until you find the step or steps that are incorrect.

[trolle]

Thanks for the advice.

My template is pretty structured, as far as I think. I try to make the files standalone with plenty of notes so that I can remember how something was done.

I tried to change one currency value:
EUR to DKK @ 7.44321
I reduced it to 7.4
This gave me a "result" of nearly -74.000 € for something which should be 0 (My template calculates a "break-even" price)
This small change in currency gave a small change in "break-even" unit price in DKK but which is multiplied up many times to give an annual picture.

My cost is in EUR
My revenue is in DKK...which then gets converted to EUR
So I assume this is the culprit

What is the solution...if there is one? A currency adjustment for the difference with the template set to one year (i.e. 11 € in profit)?
Any financial gurus on the board?

[MrShorty]

"Scaling up" a calculation can magnify seemingly small errors. Is the problem part of the "scaling up" step, or is it in the "break even" step, or something else?

I have often been warned against "rounding" the results of intermediate calculations. I can't say for sure that that is what is going on here, but that might be one question -- what is the reason for rounding the conversion factor from 7.44321 to 7.4? Does the 7.44321 correctly return 0 in the break even calculation? If the 7.44321 is the correct break even result, then it can be important to use the same value, without rounding, in the scale up step.

[trolle]

I have been digging into the template because I naturally assumed that a "break-even" price would give me a calculated result of 0. I cannot get a zero result. Instead I get a positive of 11€. This isn't a big deal, but I wanted to understand where and why this was happening. So, I started adjusting parameters to see the effect.

I have not intermediate rounding in the template. All calculations are formulas. The only absolute numbers in calculations are e.g. 12 to represent a monthly or annual result.

I can and will continue to use the currency rates set that give a result of 11€ per year.

My boss is naturally going to ask why this is happening and I want to be able to explain in an understandable way.

So, to answer your question:

what is the reason for rounding the conversion factor from 7.44321 to 7.4?

I was trying to understand why I cannot get a result of 0 using a calculated break-even. I wanted to see what a currency adjustment does and it gave some very large changes.

Does the 7.44321 correctly return 0 in the break even calculation?

No, but it gives a result of 11€ per year, which is pretty near 0 since we are talking about figures in the millions of revenue and cost.

If the 7.44321 is the correct break even result, then it can be important to use the same value, without rounding, in the scale up step.

No rounding in the template. All formulas reference the currency matrix at the top of the sheet.

[trolle]

I finally realized a simple check is to remove the currency changes and just do the pricing in €.

This gave me a result of 0.

That was easy.