Challenge: A simulation to calculate the difference between the sum of exact and rounded values

[acantor]

I’m preparing my tax return. What a slog. I needed a diversion. Thus, this challenge.

 

During tax season, there’s one task I especially dislike: Gather receipts for expenses I’ve incurred during the past year, and add up the amounts I can claim as deductions.

 

I’ve got hundreds of receipts. Sorting them and adding expenses takes me a couple of days. It’s pure tedium!

 

Fortunately, an accountant I know suggested a way to ease this task. His idea cuts the time and effort in half:

 

Round every number to the nearest dollar. For example, $44.44 becomes $44, and $500.50 becomes $501. This strategy reduces the number of keystrokes for each number by three: no decimal point and no cent values. Two keystrokes for “44” vs. five keystrokes for “44.44”.

 

He assured me the difference is insignificant. The tax collectors won’t care if my sums are off by a couple of dollars. They’re more interested in identifying tax evaders and fraudsters than taxpayers who take little shortcuts!

 

But I got curious. Is the difference REALLY only a couple of dollars? What is the true difference between a sum that consists of exact values, and a sum that consists of rounded values?

 

The challenge: Write a simulation to help develop your intuition about the differences.

 

Your script randomly chooses, say, 1000 values between, say, $1.00 and $2000.00, and sums them. Also, round and sum the values. Calculate the difference between the two sums expressed in dollars (or euros, pounds, rupees, shekels, or whatever!) and as a percentage.

 

Run the macro many times to get a feel for the range of values.

 

Not sure a challenge of creating a financial simulation will appeal to everybody. I found it instructive. By running the macro repeatedly, I discovered the difference between the sums of exact and rounded values is often less than a dollar -- and rarely more than five dollars. The percentage difference between the two values has never been more than 0.02%!

[rberq]

If the macro chooses random values, and if the random function works properly, then by definition statistical theory says you will get a very small percentage difference.

 

So to more closely match real finance, I downloaded six hundred credit card charge amounts from my bank -- the most that I could access online. 

The percentage difference was -- wait for it -- 0.020499%. 

I thought the result might be skewed because so many items are priced as x dollars and 99 cents.  But scanning the numbers, there were few charges like that, because most purchases were not single items but a roll-up for many items. 

[acantor]

Even if all 600 items were priced at x dollars and 99 cents, the rounding difference would be $6, or 1%.

 

I've been experimenting to try to make the simulation more realistic. For example, my expenses that are less than $10 and more than $200 are outliers. So I changed the upper and lower range. I messed with the total number values. When I repeated the experiment with ten values instead of 100 or 1000, there was a difference, but not by much.

 

Rberq, did you do the sums via a macro, or did you "cheat" by using a spreadsheet? 😏

[rberq]

I cheated, because I wanted to use real-world values.  I was surprised that my real numbers had the same tiny discrepancy as your Monte Carlo simulation.

[acantor]

Before I post a solution, here's the challenge within the challenge... which might be the most challenging part!

 

In coding this simulation, we generate random values within a fixed range. For example, the low end of the range might be $10, and the upper end might be $199.99. So here is a list of all possible values:

 

10.00

10.01

...

10.99

11.00

...

199.98

199.99

 

The challenge within the challenge is that Macro Express generates random integers:

 

Variable Set Integer %x% to a random value between 1 and 100

 

Given this limitation, how would you use Macro Express to generate random non-integers between 10.00 and 199.99, and with decimal values between 00 and 99?

 

I came up with two solutions, one of which was messier than I was comfortable with. I'm curious how others might tackle this.

[rberq]

Repeat Start (Repeat 2000 times)
  Variable Set Integer %in% to a random value between 1000 and 19999
  Variable Modify Integer %in%: Convert to Decimal (%indecimal%)
  Variable Modify Decimal: %individed% = %indecimal% / 100
  Variable Modify Decimal %individed%: Convert to Text String (%instring%)
  Variable Modify String: Append %instring% to text file, "c:\temp\numbers.csv"
  Text Box Display: Values
End Repeat
Macro Return

 

 

[acantor]

Rberq, That's the same approach I settled on. But I took a circuitous route before I got there:

 

1. Choose a random integer for the dollars value, from the lower to the upper limit, e.g., 10 to 2000.

2. Choose a random integer for the cents value, 0 to 99.

3. Convert both values to string variables.

4. If Cents < 10, append a zero to the start, e.g., 8 --> 08.

5. Construct the result by putting together three strings: the Dollar value, a decimal point, and the cents value.

6. Convert the result to a decimal variable so it can be easily rounded, added, etc.

 

It worked, but was a bit contrived! Your approach is better.

[acantor]

Here's my version.

 

// A simulation to calculate differences between a sum of exact and rounded values.
// The script generates random integers 100 times higher than the actual range. 
// For example, if the range is 10 to 200, the script uses values 1,000 to 20,000. 
// By dividing the result by 100, we obtain a value with two decimal places. E.g., 12345 --> 123.45
Variable Restore: Restore All Variables
 
// Run the simulation how many times?
Variable Set String %NumberOfSimulations%: Prompt
Variable Modify String %NumberOfSimulations%: Convert to Integer (%NumberOfSimulationsInt%)
 
// How many random values do you want to generate?
Variable Set String %NumberOfRandomPicks%: Prompt
Variable Modify String %NumberOfRandomPicks%: Convert to Integer (%NumberOfRandomPicksInt%)
 
// Get the range of values
Variable Set String %InitialLowValueStr%: Prompt
Variable Set String %InitialHighValueStr%: Prompt
Variable Modify String %InitialLowValueStr%: Convert to Integer (%InitialLowValue%)
Variable Modify String %InitialHighValueStr%: Convert to Integer (%InitialHighValue%)
 
// Multiply by 100 so we can later divide by 100 to obtain a value with two digits after the decimal point.
Variable Modify Integer: %LowValue% = %InitialLowValue% * 100
Variable Modify Integer: %HighValue% = %InitialHighValue% * 100
 
// Loop for each simulation
Repeat Start (Repeat %NumberOfSimulationsInt% times)
  Variable Set Decimal %SumExactD% to 0
  Variable Set Decimal %SumRoundD% to 0
   
  // Loop the number of random values that will be summed.
  Repeat Start (Repeat %NumberOfRandomPicksInt% times)
    Variable Set Integer %x% to a random value between %LowValue% and %HighValue%
    Variable Modify Integer %x%: Convert to Decimal (%xD%)
     
    Variable Modify Decimal: %DollarsCentsExactD% = %xD% / 100
    Variable Modify Decimal %DollarsCentsRoundD%: Copy Value (%DollarsCentsExactD%)
    Variable Modify Decimal: Round %DollarsCentsRoundD% to 0 decimal places
     
    Variable Modify Decimal: %SumExactD% = %SumExactD% + %DollarsCentsExactD%
    Variable Modify Decimal: %SumRoundD% = %SumRoundD% + %DollarsCentsRoundD%
     
  End Repeat
   
  Variable Modify Decimal: %DifferenceD% = %SumExactD% - %SumRoundD%
  Variable Modify Decimal: Round %DifferenceD% to 4 decimal places
   
  Variable Modify Decimal: %DifferencePercentD% = %DifferenceD% / %SumExactD%
  Variable Modify Decimal: %DifferencePercentD% = %DifferencePercentD% * 100
  Variable Modify Decimal: Round %DifferencePercentD% to 4 decimal places
   
  Text Box Display: Results
End Repeat
 
Variable Save: Save All Variables

<COMMENT Value="A simulation to calculate differences between a sum of exact and rounded values."/>
<COMMENT Value="The script generates random integers 100 times higher than the actual range. "/>
<COMMENT Value="For example, if the range is 10 to 200, the script uses values 1,000 to 20,000. "/>
<COMMENT Value="By dividing the result by 100, we obtain a value with two decimal places. E.g., 12345 --> 123.45"/>
<VARIABLE RESTORE Option="\x00"/>
<COMMENT/>
<COMMENT Value="Run the simulation how many times?"/>
<VARIABLE SET STRING Option="\x01" Destination="%NumberOfSimulations%" Prompt="Run the simulation how many times?" Mask="FALSE" OnTop="TRUE" Left="Center" Top="Center" Monitor="0" Lines="\x00"/>
<VARIABLE MODIFY STRING Option="\x04" Destination="%NumberOfSimulations%" Variable="%NumberOfSimulationsInt%"/>
<COMMENT/>
<COMMENT Value="How many random values do you want to generate?"/>
<VARIABLE SET STRING Option="\x01" Destination="%NumberOfRandomPicks%" Prompt="How many random picks per simulation?" Mask="FALSE" OnTop="TRUE" Left="Center" Top="Center" Monitor="0" Lines="\x00"/>
<VARIABLE MODIFY STRING Option="\x04" Destination="%NumberOfRandomPicks%" Variable="%NumberOfRandomPicksInt%"/>
<COMMENT/>
<COMMENT Value="Get the range of values"/>
<VARIABLE SET STRING Option="\x01" Destination="%InitialLowValueStr%" Prompt="Lowest value to include: (Must be an integer!)" Mask="FALSE" OnTop="TRUE" Left="Center" Top="Center" Monitor="0" Lines="\x00"/>
<VARIABLE SET STRING Option="\x01" Destination="%InitialHighValueStr%" Prompt="Highest value to include: (Must be an integer!)" Mask="FALSE" OnTop="TRUE" Left="Center" Top="Center" Monitor="0" Lines="\x00"/>
<VARIABLE MODIFY STRING Option="\x04" Destination="%InitialLowValueStr%" Variable="%InitialLowValue%"/>
<VARIABLE MODIFY STRING Option="\x04" Destination="%InitialHighValueStr%" Variable="%InitialHighValue%"/>
<COMMENT/>
<COMMENT Value="Multiply by 100 so we can later divide by 100 to obtain a value with two digits after the decimal point."/>
<VARIABLE MODIFY INTEGER Option="\x02" Destination="%LowValue%" Value1="%InitialLowValue%" Value2="100"/>
<VARIABLE MODIFY INTEGER Option="\x02" Destination="%HighValue%" Value1="%InitialHighValue%" Value2="100"/>
<COMMENT/>
<COMMENT Value="Loop for each simulation"/>
<REPEAT START Start="1" Step="1" Count="%NumberOfSimulationsInt%" Save="TRUE" Variable="%Counter%"/>
<VARIABLE SET DECIMAL Option="\x00" Destination="%SumExactD%" Value="0"/>
<VARIABLE SET DECIMAL Option="\x00" Destination="%SumRoundD%" Value="0"/>
<COMMENT/>
<COMMENT Value="Loop the number of random values that will be summed."/>
<REPEAT START Start="1" Step="1" Count="%NumberOfRandomPicksInt%" Save="FALSE"/>
<VARIABLE SET INTEGER Option="\x05" Destination="%x%" Minimum="%LowValue%" Maximum="%HighValue%"/>
<VARIABLE MODIFY INTEGER Option="\x05" Destination="%x%" Variable="%xD%"/>
<COMMENT/>
<VARIABLE MODIFY DECIMAL Option="\x03" Destination="%DollarsCentsExactD%" Value1="%xD%" Value2="100"/>
<VARIABLE MODIFY DECIMAL Option="\x08" Destination="%DollarsCentsRoundD%" Variable="%DollarsCentsExactD%"/>
<VARIABLE MODIFY DECIMAL Option="\x04" Destination="%DollarsCentsRoundD%" Places="0"/>
<COMMENT/>
<VARIABLE MODIFY DECIMAL Option="\x00" Destination="%SumExactD%" Value1="%SumExactD%" Value2="%DollarsCentsExactD%"/>
<VARIABLE MODIFY DECIMAL Option="\x00" Destination="%SumRoundD%" Value1="%SumRoundD%" Value2="%DollarsCentsRoundD%"/>
<COMMENT/>
<END REPEAT/>
<COMMENT/>
<VARIABLE MODIFY DECIMAL Option="\x01" Destination="%DifferenceD%" Value1="%SumExactD%" Value2="%SumRoundD%"/>
<VARIABLE MODIFY DECIMAL Option="\x04" Destination="%DifferenceD%" Places="4"/>
<COMMENT/>
<VARIABLE MODIFY DECIMAL Option="\x03" Destination="%DifferencePercentD%" Value1="%DifferenceD%" Value2="%SumExactD%"/>
<VARIABLE MODIFY DECIMAL Option="\x02" Destination="%DifferencePercentD%" Value1="%DifferencePercentD%" Value2="100"/>
<VARIABLE MODIFY DECIMAL Option="\x04" Destination="%DifferencePercentD%" Places="4"/>
<COMMENT/>
<TEXT BOX DISPLAY Title="Results" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Courier;}}\r\n\\viewkind4\\uc1\\pard\\lang4105\\b\\f0\\fs48 Simulation:     %Counter% of %NumberOfSimulationsInt% \r\n\\par Range:          %InitialLowValue%.00 to %InitialHighValue%.00\r\n\\par Numbers to add: %NumberOfRandomPicksInt%\r\n\\par \r\n\\par Exact   Sum = %SumExactD%\r\n\\par Rounded Sum = %SumRoundD%\r\n\\par \r\n\\par Difference  = %DifferenceD%\r\n\\par Percentage  = %DifferencePercentD%%\\lang1033 \r\n\\par }\r\n" Left="521" Top="64" Width="1391" Height="674" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<END REPEAT/>
<COMMENT/>
<VARIABLE SAVE Option="\x00"/>