Cash bill denominations in a cash bag

[duteberta]

I am having trouble figuring what function (calculation) to use.

First, an overview of why I need this. I run fun promotions in schools. When a child sells widgets in a fundraiser, I let them draw cash out of a cash bag as a reward. Here are the exact instructions to the students...

For every 3 widgets you sell in our fundraiser, I will let you draw 1 bill out of a cash bag. The cash bag will be filled with $1's, $5's, $10's and $20's. Example: If you sell 9 widgets, you get THREE pulls out of the cash bag (one bill per draw).

Given the TOTAL NUMER OF DRAWS to be done within a school, I want an Excel formula (or calculation) to tell me how many $1's, $5's, etc to put into the cash bag.

As a general rule I want more lower denominations (such as $1's) and fewer high denominations (such as $20's).

I want to spend an exact predetermined dollar amount TOTAL on this promotion.

For example:

GIVEN: I need 891 draws at a school.
GIVEN: I want to spend $2,219 total.

How many $20 bills should I put in the bag?
How many $10 bills should I put in the bag?
How many $5 bills should I put in the bag?
How many $1 bills should I put in the bag?

I know that there could be MANY answers to this question, depending on exactly how many of each denomination I wish- but I don't really care about the exact amount. I am content to let Excel determine that for me as long as there are MORE lower denominations (such as $1's) and FEWER high denominations (such as $20's).

My brain just isn't big enough to make Excel work for me on this. Is it even possible?

 

[gardnertoo]

One way to approach this problem will be to determine the number of one's that must be allocated to balance each specimen of higher denomination in order to arrive at an average prize equaling your total budget divided by the number of draws. Your example numbers, $2,219 among 891 draws, yield an average prize value of $2.49. To achieve this, each twenty must be accompanied by twelve one's to yield an average of $2.46, just below the target. Each ten must be accompanied by five one's, and each five by two one's. This will give you defined "sets" of prizes. It is extremely unlikely that the number of bills will add up to the number of draws, so at the end the spreadsheet will have to fill in the gaps with one's, driving down the average. This will result in some leftover money, which can be dealt with as either a savings, or as one or more "bonus" prizes. See spreadsheet below. The green cells are the only places you need to enter numbers, everything else is calculated. You can play around with the values in cells F9:F11 to get whatever proportion of twenties to tens to fives, and see how it affects the amount of money in the bonus pool.

Book1
	A	B	C	D	E	F	G
1	Total:	$ 2,219.00		Denom	Qty	Amount
2	Draws:	891		$20	24	$480
3	Average:	$ 2.49		$10	48	$480
4	Prize groups:	24		$5	96	$480
5				$1	723	$723
6				Bonus	1	$56
7
8	Denomination	$1's per	Average	Total	Bills per set	Sets per group	Amount per group
9	20	12	$ 2.46	$ 32.00	13	1	$ 32.00
10	10	5	$ 2.50	$ 15.00	6	2	$ 30.00
11	5	2	$ 2.33	$ 7.00	3	4	$ 28.00
12			Totals:	$ 54.00	22	7	$ 90.00
Sheet1

 

[Dave Patton]

You could probably use Solver and/or
sophisticated formulas but the following may suffice.


Amount 0 $20 $10 $5 $1 Num Req Diff
2,219.00 0 110 1 1 4 116 891 775
2,219.00 0 40 40 52 759 891 891 0



C3 =INT(($A3-SUMPRODUCT($B3:B3,$B$2:B$2))/C$2)

The first row determines the minimum bills required for the sum.
You can then manually adjust the distribution.

 

[wsjackman]

for 981 draws and a total of 2219 dollars a not so fast brute force macro can be generated such as

[code
Sub trial()
n = 10
For one = 244 To 981 Step 5
Cells(1, 1) = one
For five = 3 To one - 1
Cells(1, 2) = five
If one + 5 * five > 2219 Then GoTo 10
For ten = Int((981 - one - five) / 2) To five - 1
Cells(1, 3) = ten
If one + 5 * five + 10 * ten > 2219 Then GoTo 20
twenty = 981 - one - five - ten
Cells(1, 4) = twenty
num = one + five + ten + twenty
dol = one + 5 * five + 10 * ten + 20 * twenty
If dol = 2219 Then Cells(n, 5) = one: Cells(n, 6) = five: Cells(n, 7) = ten: Cells(n, 8) = twenty: n = n + 1
Next ten
20
Next five
10
Next one
End Sub

endcode]


it is not a very efficient macro and takes a while to run.
you could speed it up by not having it show the current numbers in
a1:D1 I do this to ensure it is working and to look for in process discrepancies

note if you use a different set up make sure you modify the ones start loop value
in this case the integer portion of the ones count will be either 4 or 9
each dollar amount will have its own pair of pissibilities.

in this setup there are 447 at-least-1-20$-bill-combos. you could put in more criteria to get what balance of bills you want