PAYROLL WITH EXCEL || BASICS
Introduction
When I hear the word payroll, nothing else comes to my mind asides from salary at the end of the month. As a young guy that grew up in Nigeria. Our parents and loved often joke, “I will put you on my payroll” and suddenly you get happy.
Have you ever wondered, what will happen if your uncle’s, sponsor's, or employer’s payroll is not efficiently managed?
Think no further, no money for you the following month…🤣🤣
Enough of the joke, let’s get to business.
Problem Statement
Payroll is an account statement in tabular sheets (most times) containing information about workers' pay at the end of the month, week, days or hours depending on your agreement.
This is my definition in simple terms. Payrolls are usually handled by the accounting department in an institution, what if you run a one-man business or a small organization of 2–2o and you want to easily pay your workers, how do you do that easily.
Well, let’s get to it.
Jumping right it
Solution
Well…It is up to you to solve this problem, by taking accurate account of the salary agreed with your workers, no matter how many easily. You definitely need a tool beyond paper and pencil mathematics to do this. Leveraging on a tool that can easily be uploaded to the cloud, helps to calculate and automate your payroll tasks, so you could relax and focus on business and growth more.
Excel
Excel is a tool made by Microsoft, it comes with windows systems. You can easily download and get it installed in your system if you don’t have one. It’s an effective tool for managing and building spreadsheets for various purposes.
Its application spans from building payrolls to an online attendance system and even a store inventory system. It is such a great analytical tool with superpowers, you should try it out.
I tried a fictional business, I’m the employer {JOSIAH ADESOLA} 😃😃. I created fictional names, including some of my friends in the names, the spreadsheet also contains their hourly rate, the number of hours worked weekly, overtime, overtime pay, and the total amount they made after the whole month. Don’t mind the currency, I used, but I think they would surely love it.
Excel Functions
I made use of some excel functions and learnt some new tricks in using excel spreadsheet.
SUM: The sum function is a very crucial one in excel. It is used to get the total of numbers in a column or row, depending on your choice. The syntax is written as =SUM(column1:column2) for instance =SUM(C2:C15). This will sum the numbers in that columns
Back to tricks☺☺ , I learnt that you could easily press ‘=’ sign then when SUM pop up, press the tab key to select it, then place your cursor on the column you want to add then drag to the ‘column2' of your choice. It worked like magic. Try it out!
Note: It’s quite important you use the ‘=’ sign or else excel doesn’t see it as a function.
AVERAGE: As the name implies, it’s used to get the mean or average of numbers, just the same way I explained the SUM function, you do it. The syntax is written as =AVERAGE(Column1: Column2)
Tricks: IF you want to calculate a whole column and you don’t know the last number of the column for large datasets, Just note the column letter. If the column letter is C for instance, just write ‘=AVERAGE(C:C)’, this function simply calculate all the number in that field.
Maximum: What if you want to know the richest employee you have, well it’s quite easy, just use the maximum function in this syntax, ‘=MAX(C4:C43)’
Minimum: If you can get the richest employee, you should be able to get the least paid easily, without straining your eyes. Using…uhmmm…try to guess it. Yeah, you did just great. The minimum function, the syntax is written as ‘=MIN(C3:C14)’.
Tip: Double click on a cell, like you see above to see the function
Random Numbers: This project does not use real figures, so how was I able to generate a lot of numbers without going through a lot of stress. I used a function. It’s called ‘=RANDBETWEEN(39, 50)’, the numbers there are just limit, the random numbers won’t go beyond or below them.
IF Function: To wrap up, I will talk about the IF function, it’s effective for making calculations, based on certain conditions. If you noticed, wow…I just used an if statement in my writeup 😉😉. Sorry to stray you away, back to business. The IF statement was used in the overtime hour pay, we wanted to give 50% of the original money to hardworking guys that do overtime.
The syntax, actually states ‘=IF(D4 > 40, D4–40, 0)’. To explain, this whole statement, it means, if the values in D4, which was the total hours of work for Week 1 workers, is greater than 40, subtract it from 40, D4–40, else give 0 hour, which means no overwork time. It made sense right.
Conclusion
I will be sharing the full worksheet here, to download. You can easily make a copy and edit it, or use it for your business and practice. The good thing is that, excel can be used by anyone, even without accounting backgrounds. Payrolls are super important for your business.
See you again, bye.
Post Script
This my DAY 1 of my 10 days of Data Analytics. Do well to correct me, clap, comment and share this content.
