Cashier Balance Sheet

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

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.

--
Regards,
Fred


"JustVern69" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> JustVern69
> ------------------------------------------------------------------------
> JustVern69's Profile:
> http://www.excelforum.com/member.php...o&userid=32464
> View this thread: http://www.excelforum.com/showthread...hreadid=522474
>

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)






--
Don Guillett
SalesAid Software
[email protected]
"JustVern69" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> JustVern69
> ------------------------------------------------------------------------
> JustVern69's Profile:
> http://www.excelforum.com/member.php...o&userid=32464
> View this thread: http://www.excelforum.com/showthread...hreadid=522474
>