The instructions (or rather the issue) is explained in the file. But as you can see when you open it up, the calculations when adding a commission rate is different to that if you were to work it out on a calculator. What is the reason for this being wrong?
I am not bothering to try to understand your calculations. But you seem to have at least several mistakes.
1. Your calculator inputs are rounded to 2 decimal places, but your Excel values are not.
You can see this by temporarily changing the formats to display at least 15 significant digits (13 decimal places, based on your smallest non-zero value).
Alternatively, you can enter formulas of the form =A1&"" in parallel columns. For example:
Select or hover the cursor over the cell to see the formula.
The comparable rounded calculations are:
E5: =IF(C5="","",ROUND(B5+D5,2))
F5: =IF(C5="","",ROUND(E5*($L$2+1)/(C5-1),2))
J5: =IF(C5="","",IF(I5<>0,ROUND(I5+J4,2),0))
K5: =ROUND(H5-I5+K4,2)
The most important rounding is in column F. The rounding in columns E, J and K are prudent in order to avoid anomalies of internal binary floating-point arithmetic.
2. You use 291.38 (D5) in your calculator computations, but I you use 391.38 (E5) in your Excel calculations (F5, H5).
I cannot say which is wrong because, again, I am not bothering to understand your calculations.
3. Your Excel calculations do not match your calculator computations.
Again, I cannot say which is right.
You describe your calculator computations as:
THE REAL MATHS ON A CALCULATOR = 10 X 45.66 -291.38 - 45.66 = 119.56 - 5%
AND THAT GIVES ME = 113.58 ON THE CALCULATOR WHEREAS ON THE SPREADSHEET IT SAYS 100.00 (K5)
Note that 113.58 = 119.56
*(1 - 5%), rounded.
The calculator computations corresponds to an Excel formula of the form:
(10*F5 + K4 - F5) * (1 - 5%)
But your computation in K5 is effectively (substituting comparable terms):
B5+10 - F5(?) + K4
Note: You actually have zero in place of -F5, because I5 is effectively IF(G5=
"L",F5,0). I wonder if that should be G5=
"W".
In any case, note that there is nothing resembling 10*F5; and B5+10 is 391.38, not 456.60.
And more to the point, K5 does not multiply by 1-5%, as your calculator computations do.