Cashier Balance Sheet

J

JustVern69

I'm trying to come up with a cashier balance sheet, fairly simple. I
have everything done, except my manager would like me to add another
column for the cashier who cannot count down a till to a specific bank
amount. This is compounded by his insistence that the balance sheet
list each denomination of coin and bill. The bank amount is $250.00.
If the till is 386.41. broken down as follows I need a list of coins
and bills to keep to make the 250 bank and to make a deposit. How can
you evenly divide the $amount into each denomination and make it
balance? Thanks! Vern
EXAMPLE:
Amount in till For 250 Bank
For Deposit
PENNIES 0.56 0.55
0.01
NICKLES 1.50 1.45
0.05
DIMES 3.10 3.00
0.10
QUARTERS 4.25 4.00
0.25

ONES 32.00 30.00
2.00
FIVES 85.00 40.00
45.00
TENS 80.00 60.00
20.00
TWENTIES 180.00 100.00
80.00

TOTAL 386.41 250.00
147.41
 
F

Fred Smith

You can get it to work in the example given by rounding each coin down to the
next highest denominated coin.

If a1 has the till amount of pennies, the amount to keep (b1) is:

=int(a1/0.05)*0.05

The amount to deposit (c1) is =a1-b1

Quarters, however, are problematic, because they are not a multiple of dimes.
Short of moving to Europe or Australia, where they had the sense to use 20-cent
pieces rather than 25, I would truncate the dimes to the nearest 50 cents,
rather than 25 cents.
 
D

Don Guillett

Here is one to try
$ 134.13
100.00 1 134.13
50.00 0 34.13
20.00 1 34.13
10.00 1 14.13
5.00 0 4.13
1.00 4 4.13
0.50 0 0.13
0.25 0 0.13
0.10 1 0.13
0.05 0 0.03
0.01 3 0.03
134.13
100 =INT(D2/B2) =B1
50 =INT(D3/B3) =ROUND(MOD(D2,B2),2)
20 =INT(D4/B4) =ROUND(MOD(D3,B3),2)
10 =INT(D5/B5) =ROUND(MOD(D4,B4),2)
5 =INT(D6/B6) =ROUND(MOD(D5,B5),2)
1 =INT(D7/B7) =ROUND(MOD(D6,B6),2)
0.5 =INT(D8/B8) =ROUND(MOD(D7,B7),2)
0.25 =INT(D9/B9) =ROUND(MOD(D8,B8),2)
0.1 =INT(D10/B10) =ROUND(MOD(D9,B9),2)
0.05 =INT(D11/B11) =ROUND(MOD(D10,B10),2)
0.01 =INT(D12/B12) =ROUND(MOD(D11,B11),2)
 
Top