Making a monthly revenue sheet, need help

[JakkeJakobsen]

Hi! I am making, or rather editing our monthly revenue sheet. This is what is in this sheet, column for column:
A: Date
B: Revenue
C: Debit card
D: Credit card
E: Night safe cash amount (we only deliver when we need more money in our account, as so much goes over card these days)
F: Billable credit, as if in a group rents the building, and want a bill in the mail instead
G: Cash Drawer amount at opening
H: Cash Drawer amount at closing (these are used to correct Difference below, if we have withdrawals from card to cash and the cash drawer amount is below our set amount)
I: Difference. If the Revenue-Debit card-Credit card-Night safe cash amount is 0, this is good. Billable credit is shown here as it isn't payed yet. Cash drawer also changes this column when its wrong (start 2500, end 2430, should've been 2450, says 20 in this case)
J: Night safe total, it adds up the night safe amount until there is a mark in column K, which resets this counter
K: We set an X here to show that at this date, the night safe amount was deposited, which resets the summarizing counter in J
L: The deposited amount
M: The deposited vs night safe total difference. If the night safe deposit amount equals the resetable counter, these show 0.
N: Any comments the cashier has for the day, reasons for difference for instance.

Below is an image of the sheet as it is, in Google Sheets. I have some issues with this setup, because when we reset the night safe deposit, the counter should be 0 on that day, as we don't add the amount for that day.

All formulas I got for each column:
I (Difference revenue): =IF(G7>H7,B7-C7-D7-E7-F7+G7-H7,B7-C7-D7-E7-F7+G7-H7)
J (Night safe total amount (Resetable): =if(K7="X",E7,'Januar 2017'!J37+E7) (this is only for the first day in a month, refers to previous months' sheet), =if(K7="X",E8,J7+E8) (this is the one for all but J7)
M (Night safe deposit difference): =if(K7="X",L7-'Januar 2017'!J37, " "), =if(K8="X",L8-J7, " ")

So, the main part of it (from A to I) is the old, current sheet, the cash drawer part I added a month or two ago. But to let us skip the calculator when checking the report deposit total comparing it to actual cash amount, I want a counter for the amount. However, this has to be resetable so it starts at 0 after a deposit has been done. One issue I have is at month change, this has to carry over from last day (28-31) and over to day 1 the next month.

Can anyone help me here?

 

[RCBricker]

ok so I recreated your sheet with a few more formulas to automate it a little better. IF you only want the formulas for the deposit then here you go:

=IF(K8="x",0,IF(F8<>"",I8+J7+E8+F8,I8+J7+E8))

This will reset the value to zero on deposit.

The other formulas I used are as follows


Column Cash for night safe:
=IF(SUM(B8-SUM(C8:D8))<0,0,SUM(B8-SUM(C8:D8)))

column Cash Drawer at Opening:
=IF(H7<2500,H7,2500)

column Cash Drawer at Close:
=IF(E8=0,G8+SUM(B8-SUM(C8:D8)),2500)

Column Difference:
=B8-SUM(C8:F8)+SUM(G8-H8)

*******NOTE your if statement formula for difference resulted in the exact same formula.

Column Night Safe total:
=IF(K8="x",0,IF(F8<>"",I8+J7+E8+F8,I8+J7+E8))

column Deposited Amount:
=IF(K8="x",J7+E8,0)

Column Deposit to night safe total difference:
=IF(K8="x",L8-J8,0)

HTH

 

[JakkeJakobsen]

ok so I recreated your sheet with a few more formulas to automate it a little better. IF you only want the formulas for the deposit then here you go:

=IF(K8="x",0,IF(F8<>"",I8+J7+E8+F8,I8+J7+E8))

This will reset the value to zero on deposit.

The other formulas I used are as follows


Column Cash for night safe:
=IF(SUM(B8-SUM(C8:D8))<0,0,SUM(B8-SUM(C8:D8)))

column Cash Drawer at Opening:
=IF(H7<2500,H7,2500)

column Cash Drawer at Close:
=IF(E8=0,G8+SUM(B8-SUM(C8:D8)),2500)

Column Difference:
=B8-SUM(C8:F8)+SUM(G8-H8)

*******NOTE your if statement formula for difference resulted in the exact same formula.

Column Night Safe total:
=IF(K8="x",0,IF(F8<>"",I8+J7+E8+F8,I8+J7+E8))

column Deposited Amount:
=IF(K8="x",J7+E8,0)

Column Deposit to night safe total difference:
=IF(K8="x",L8-J8,0)

HTH

We fill the cash drawer amounts in ourselves, they are not calculated, as we count it before opening to see that the float is correct compared to the closing amount the day before.

 

[RCBricker]

All those formulas is to be used as checks against counts. Use them if you find them helpful. I personally try to eliminate as much human entry as possible. Instead I try to use math and then the human can verify with their counts.