Need guidance on best approach for live summary tab of pay slips

Here is an interim solution. I'll need some guidance cleaning it up.

I took the data on the Pay Log 2017 and normalized it into a data table using this tool. https://www.excelforum.com/tips-and-...zing-data.html. This was a one-time shot. Eventually, we will get rid of the current version of the Pay Log.

The pivot table gives a glimpse of how the data can be manipulated. Eventually this will also be replaced with an Excel table and we'll add a column when the sheet is posted.

There is a new sheet called the control panel. It looks at the database, finds the last pay slip paid and calculates the next pay slip. When you click on the Make Pay slip button, it copies the Slip template to a new sheet. I noticed that the values were fairly constant for the deductions, etc. so I left them in the template so they come across as the default.

Clicking the button puts you on the newly-created page and you can change values as needed. The new sheets had a Post Slip button. When the slip first comes up, the value in Cell B3 is in bold red text and says Slip not posted. When you fill in your values and click on the button, this text says Slip Posted. Also the values are "scraped" from the form and put into the data table.

I invite you to play with and experiment with the functions to see if this is going in the direction you want. You can "reset" the function by deleting the created sheet and going into the Data Table and manually removing all records for the Pay Period.

The spreadsheet makes no reference to an employee name. So do you have a separate workbook for each employee? That will answer a number of questions that I have.

I also want to review the formulas you have in the Current Pay Log so I can replicate it. Also I need to add some "bullet proofing" so you can't try to create the same slip twice or add duplicate data. Also I would like to know if you need to "back out" bad data or if you have a need to override an existing pay slip.

This program creates a pay slip as a means of data entry (It is what you are used to seeing). Once the data is in the database, the pay slip is no longer required, and furthermore, it can be re-created from the data.

I also have some questions about the DAS & G-QST Paid sheet. I believe this information can also be generated from the database automatically.

Once you play with this for a while, you may come up with other ideas or questions. I'll proceed when I hear back from you.

I am "nailed to the wall" at the moment myself. I'll have to read the requirement again and probably have to reverse engineer my own work. Next week should be a lighter schedule. I won't tackle it until I am sure I have some contiguous time to get to it.

I'm just getting back into this issue. I think what I am going to have to do is as pay slips are created, I will have to add a record to a table with the pay slip number and the date for which the pay slip is created along with the other metrics you indicated that you want to track. There is nothing like an "normalized" database from which pivot tables of all description can be built.

One question I need answered. If a pay slip period extends over the end of a month, to which month do I gather the figures? Do the figures go into the month with the start date or the end date? Rather than go with "I think I know the answer," I'll go with what you tell me.

Once the pay slips are created, they are locked (At least I think so, I'll change this if it is not the case). I'll give you a table where you can enter in the new rates. Basically you'll make a copy of this spreadsheet each year and update the table. We'll also have to reset the numbering system.

End date counts for the month and the year! Got it. I'll have to think about how we roll over the end of the year. Although I am usually a proponent for keeping all the data in one place, I think that in this case it would get unwieldy. What I might do is have annual books for making the pay slips, but write the data, regardless of year into a "central database." This central database will also be where you can keep employee IDs and names and such that drive the drop-down cells.

Although I should make some progress today, it is unlikely that I will have a final product today .

I'm getting immersed in the project again. This looks like you have one workbook per person.

How about a single annual book for all people. You select the person's ID or name from a drop-down list. This list will be populated from the central database that will also provide the information for the person's rate and deductions. Also select the pay period you want. Leave it selected as you move onto the next person.

You fill in the hours and the program will compute the figures. Click the button to submit and all the data for that person is written to the central database. A person's individual pay slips can be generated from the database, and this same database can be used to consolidate the payments, deductions, etc. for the summary reports.

Individual slip data won't be maintained in the book. The database runs everything.

Here is a sketched-out prototype.

You maintain lookup data in the Pay Slip Data file. When you click the Read data button, it will read the data it needs from this file. Set the directory name and file name in cells C9 & C10.

The data entry button (to be developed) will take you to the Slip Template sheet - in the final version I will make this a user form to give more control and to make the input look nicer. However the sheet mimics the form closely so it is worth looking at.

Select the pay period you want in Cell B1. The start date and end date will be looked up for you.

Select the employee ID in Cell B2. If you would prefer to look up based on employee name, let me know and I can make that happen. When you enter the employee ID the employee's Name, rate, document type, bank name, account type and type of employee will be looked up for you.

I gave you a place to add the hours in here since there is no reason to have to navigate into the form otherwise.

The Post Slip button will enter the data into a temporary database and you will be asked if you want to do another employee. If you answer yes, I'll clear the form except for the pay period, and you can enter the next employee. If you answer no, you will be taken back to the control panel sheet.

The post all slips button will post the data to the permanent database and clear out the temporary database.

When you exit the program I will check to see if the temporary data has been cleared. If not, I will give you an advisory message that you still have data to post and you will have the option to go back and post it or continue closing the file. The temp data will still be there when you open the file the next time.

As I mentioned, I'll give you a separate spreadsheet to do reporting based on the permanent data.

I have a question about the deductions. Are these fixed or do they vary with the amount you earn? For example, the more you earn, the more you pay in taxes. I do not see formulas for that. Also can the person declare a fixed amount to be taken out? That information can also be stored in the Employee Lookup tables.

I'll need the clarification I asked for on the deductions.

Here is the next step in the production. This version uses Excel Forms. Click on the Enter Pay Slip forms button. This launches the form.

Use the drop down to select the pay period. The begin and end date will be filled in automatically.

Use the drop down to select the employee ID. The rest of the employee data will be filled in automatically.

Enter the hours.

From here on I'll need the information about the deductions so I can make the correct formulas.

Clicking on the exit button posts nothing. It just exits the form. I will probably add a "you didn't post anything, are you sure you want to quit?" Dialog box.

Clicking on the Cancel button clears the form but leaves it up there. The Pay period is not cleared.

Clicking on the Post button will post the appropriate data for the employee, and clear the form except for the pay period. You can always override the default pay period. In fact, if you give me the rules like "It's always the pay period ending with last Friday, I can make the form come up with the default value. At the moment the form initially launches with a blank pay period. It will "remember" the last pay period used until it is closed and re-launched again.

I still have some cosmetic work to do on the form.

Once I get the posting to the temporary table taken care of, I will build the piece to append that data to the "permanent" database. That is pretty much "standard" code. Nothing tricky about it.

Obviously, I am trying to set you up for a January 1st implementation. Attached is the latest version - I've taken it to the point where you can enter in hours and it will compute the Gross Pay. Once we get the deduction questions answered, I will move on to those computations, and actual posting. I do not anticipate any problems with those.

You can attempt a Post now if you want. I do have the error checking in place to make sure you have filled in all the blanks that must be filled: Pay Period, Employee and Hours. It even does a check to see that hours are numeric.

The 5 PM edition. This one has the calculations in it. The only thing left is determining if deduction amounts are supposed to be read in or calculated. I could not tell from looking at the pay slips.

annnd i was using one of the old files =\

Okay so for the employee / employer contributions we can simply add static multipliers in a table and pull that info automatically. Let me get those for you.

Okay, so i have to go make my son dinner but in the offchance that you have the appetite to dive in all of the equations to calculate the Quebec deductions (so all but "Income tax - Fed") for 2019 are here: https://www.revenuquebec.ca/document...2019-01%29.pdf

The ideal would be for me to be able to change the rates in future years by just downloading this guide and adjusting the numbers.

I'll pop back in after he's eaten!

Cheers,


Jay

Snowed in today, not that you'd think 10 inches of snow is a lot, but it is for North Carolina.

The purpose of Cell B1, the path to the databases, is for you to put in the path where *you* decide you want to store the database. The best way I've found to do this is to create the directory and navigate to it using Widows Explorer. Then click in the address bar and copy / past the address into the cell in the workbook.

Excel VBA can read data from cells in the workbook, so rather than "hard code" it into the code, I decided to put the path name in a cell that can be defined by you, the end user.

I'll look at the link you provided. I've done a payroll program a long time ago back in the 1980's when my wife and I owned a business. You Canadians are a sensible lot; I am expecting it to be less complicated than the U.S. counterpart.

Everybody's information will be stored in the same file. I suggest that you make a backup of this file every time you run a payroll.

From this file you should be able to run almost any kind of report you can think of including generating individual pay slips, bi-weekly pay summaries or annual summaries. As for the pay slips, I could make it so they look like the format you have now.

The data will be stored in a "normalized" format meaning that it will be organized in a format very suitable for pivot table analysis. I suggest you go on line and look for some tutorials on pivot tables.

Here is the relationship between the two files. One of them is the "working" file or "Front End" in database parlance. This is the file that launches the form and is used for data entry. This file can be run from anywhere on your network and by anyone who has access to the directory in which you place the data file.

The data file must remain in the same place and have the same name, once you decide where to put it. The data file is know as the "Back End" in database parlance. It is the known location that all the front ends can go to to get and place their data.

The data file contains the historical records of everyone's time sheets. It also contains some other tables such as employee IDs and names. It may also contain tax tables, etc. Unless you need to make some sort of correction or edit employee information or change the tax tables, you should not have to open this file.

When you open the front end, you will have an option to download the data. This will download employee information, pay period information (start and end dates) and tax table information (if I need to use tables). It does not download historical pay slip information. That stays put in the data file. However, you have a table in the front end that is of the same structure as the back end except that it has no data in it.

As you fill out the various pay slips using the form, the form will write this information into the table in the front end. When you "check out" the program appends the data to the table in the back end and clears out the table in the front end. One consequence of this is that you can have multiple people doing pay slips, provided that they are no doing the same pay slips.

I will put in checks to make sure you don't have duplicate data in the back end and give you instructions on how to clean it out if it happens.

As to what is "active" on the form.

Use the drop down list to select the pay period. This should automatically fill in the start and end dates for that pay period. The program gets this data from the downloaded pay period table. Change it in that one place and everybody gets the correct copy.

Use the drop down list to select the Employee ID. This should automatically fill in the employee name, rate, document type, bank name, account type and employee type. This information is also downloaded from the data file.

Enter in the hours. As you enter in the hours, the other computations take place.

The right side of the screen should fill in automatically based on the pay period, employee id, hours and whatever the tax information reveals to me. I don't have the computations for the deductions yet.

As for the buttons.

Exit simply exits the screen. The program checks to see if you have data in the hours field. If you do you get a warning and you can go back to the form or exit without doing anything.

Cancel clears out the fields in the form and does nothing else.

Post enters the data into the table in the front end. There are checks to see that you have entered in the mandatory data: pay period, employee id and hours. I have the checks working, I do not write data to the table yet.

You will have a button on the Control Panel sheet to post the data to the back end. It is not there yet. When you click it, it will append the data to the back end and clear the table on the front end.

I will also add an additional check so that when you close the file, it will check to see if there is data still in the table. You will have the option to continue closing the file, in which case the data in the front end is not posted, but is not erased. It will still be there when you open the file the next time. You will also have the option to go back and post the data before closing the file.

The system is set up so that you can have different front ends to produce whatever reports you can think of. They will all read the data from the back end.

How are you calculating the deductions?

As best I can figure from the documentation you provided, it should be:

Estimated annual income = (YTD Pay) + (Pay period Pay * Remaining Pay periods)
This gets multiplied by 6% to a maximum of $1,170
In addition, an employee may specify a fixed amount to be withheld (I can store this in the personnel table).

I don't want to re-invent the wheel if you already have a way of calculating this.

Alright, and we're back =) I hope that your holidays were relaxing and calm. As I wrote in the various private messages which never got to you I spent the holidays in Wales visiting my girlfriend's family, which is why I couldn't connect with you over the period; internet was spotty and my time was mostly scheduled for me =\

So where were we. I have calculated the new source deductions for 2019, which are attached. I think that's the best approach to this, as their calculation is nebulous so i just use a website that spits the answer out when i select certain criteria. For the annual income i know what it would be for a given employee because (at least in the current use case) the annual amount is set. The only nuance would be if the person were to be given a raise mid-year. Then we'd just need to update the table for future pay slips.

There's also one caveat that I may have mentioned but I can't remember. This one is annoying, but critical. The subsidy program that is being used has a fiscal year-end of March 31 each year. They have a somewhat ridiculous policy wherein we have less than one week from that day to submit a reimbursement request for any un-claimed days up to March 31 (or else the door slams shut and the money is unrecoverable). This doesn't impact the table except for the following thing. These pay-slips are generated for 2 week periods. Wherever March 31 falls within that 2 week period the I must split the pay period in two [Start date --> March 31],[April 1 --> End date of two week period]. I have to pay the employee for a portion of the pay period up to March 31 so that I can submit the reimbursement claim, and then pay them the rest of the payment and the end of it. For 2019 it's actually a moot point because one pay period ends March 29, and the next actually begins on April 1 so it's a seamless transition. Some years it won't be, though. Is it worth adding something into the sheet to handle this? I ask because while this is a must for the current employee with the disability for whom the subsidy is claimed, it wouldn't be applicable to other employees and the sheet is now being built with a multi-employee approach in mind... - What are your thoughts? Maybe we can have an option in the employee profile to indicate if they are 'subsidized' and only if indicated would this March 31 split be implemented? Just an idea - maybe it's nuts...

I appreciate the approach you're taking - it's more robust. I'm going to wrap my head around it as best I can. My need is be be able to produce the pay slips that i can send individually to a funding agency, and to have access to all of the data for year-end reporting. I believe that the approach you're taking allows for that, correct?

Alright so I think that with the new source deductions we can move the production along to actually posting. I have the sheets open on my screen(s) but there seems to be an #ref! errors for the referencing of the Pay Period Data tab - are you getting errors as well?

Sorry again for not being able to communicate over the past month - I'm here now and you'll get a response within a day to any posts you make.

Cheers,


Jay

I don't see an attachment. I will re-engage.

In answer to some of your questions.

Yes, I like the idea of a look-up table for the deductions. If I read the tax laws correctly, I know how to handle a during the year pay raise: take the amount of money they have already earned, and add it to the amount of money they are projected to earn over the remainder of the year and look up on that.

At the moment, I have everyone on a hourly rate model. So if a person is salaried, then what you would need to do is divide annual salary by 2080 hours (number of work hours in the year).

I will set it up so that a raise computation is done for each employee. If the employee does not get a raise, accrued + projected earnings will remain constant for the year. Computers don't care if they do extra work, and not having to take the time to branch the logic would keep the computations flowing smoothly.

The same holds true for the subsidy program. We'll do it the same way for every person. I'll have to mull over how to do the March 1 split. It may be something as simple as updating the pay period data. As you pointed out, the break this year falls conveniently between pay periods 2019-07 and 2019-08. In the future you may have a pay period 2021-07A and pay period 2021-07B. It may be possible that this results in a 1 day pay period in one bucket and a 13-day pay period in the other bucket. I'll keep this in mind when developing the program to print individual pay checks so it all comes out as one check. It will be a minor inconvenience to you since you'll have to do double the entries for that overall pay period. it should not matter to what I have in mind for the annual summaries.

I can get moving on this once I have the lookup tables.

Hi - that's really odd... i'll re-attach now and see if it works.
Let me submit and see...

edit: success!

Also, yeah we can figure out how to handle the split as needed. it's a weird requirement but needed for the current structure; however, the average employee wont have it which is why i suggested the dropdown / checkbox approach.

Have I told you how thankful I am to have your help on this? I really appreciate it!

Cheers,



Jay

What I have here is a pay slip for one person. Are you telling me that the deductions are the same for everyone regardless of how much money they make annually or during that pay period?

I was expecting Something like this:

Let’s see how much you would withhold for an employee in 2018. You have a married employee with one withholding allowance and no pre-tax deductions. They earn $700 weekly. Using the above chart, you can see that the amount for one withholding allowance is $79.80 in 2018.

1. First, subtract $79.80 from $700 to get $620.20.

2. Using the 2018 income tax withholding tables, you find that this amount of $620.20 falls in the “Over $588 but not over $1,711” range. Look at the amount to withhold for this range.

3. The amount of income tax you must withhold for this range is $36.60, plus 12% of excess over $588.

4. Subtract $588 from $620.20 to determine the excess, which is $32.20. Then, multiply that number by 12%, which is 0.12. After, you must add that number to the flat rate of $36.60:

$620.20 – $588 = $32.20

$32.20 X 0.12 = $3.86

$3.86 + $36.60 = $40.46

You must withhold $40.46 from the employee’s wages for federal income tax.

Of course, I would need the appropriate tables.

The site does not help. It does not contain the tables I need. It only provides the answer without explanation as to how it got there.

The "table" you gave me was one person's pay slip. Perhaps you attached the wrong file.

I is probably best if you write (or visit) the tax office to get the tables and instructions on how to prepare withholding not just for income tax, but for the other deductions as well.

How are you filling this information in now?

Yes, I had envisioned a much larger scope. However, it's a good exercise for me should I ever "go freelance." It will look good on a resume.

I cannot do anything with the link you provided. I'm not a tax expert. I was expecting something along the lines of what is shown in the picture.