Excel - Way to calculate paying salaries

Question

I work at a restaurant chain with about 170 employees. Currently we pay most of them by cash, and our administration team counts 'what bank notes' to pay them manually.

So for example if one of our waiters gets paid 2152 Euro, then the function should calculate the following:

100 Euro * 21
50 Euro * 1
2 Euro * 1 

Anything like that in excel?


Show source
| function   | excel   2017-01-06 09:01 2 Answers

Answers to Excel - Way to calculate paying salaries ( 2 )

  1. 2017-01-06 09:01

    Perhaps you can try to use Truncate & Divide to calculate number of notes for each given note.

    for example, to get number of 100 note:

    NumberOf100Note = Trunc(givenAmount/100,0)

    NumberOf50Note = Trunc((givenAmount - 100 * NumberOf100Note) / 50, 0)

    etc. and etc.

    and you can build these very easily in excel, isn't it?

  2. 2017-01-06 10:01

    This is just one way to do it. I chose to use a helper column to keep the formulas simple for you

    Here is how it looks:
    cash example

    This formula goes in B3 and you should drag it down. This column shows the remainder due after each note. E.g. 52€ remaining after 2100€ in 100€ notes has been accounted for)

    =MOD(B2,A3)
    

    Then place this formula in C3 and drag it down

    =IF(B2<>B3,(B2-B3)/A3,0)
    

    That will give you how many of each note to dispense to the worker.

    Note
    This method simply assumes you want to issue the fewest notes. It would be a good idea to have a table which allows you to indicate if you are out of a particular note so that the model uses 2x€50 notes rather than 100€ notes, for example.

Leave a reply to - Excel - Way to calculate paying salaries

◀ Go back