Financial applications for excel spreadsheet

Running Head: EXCEL PAYROLL Excel Payroll Time Sheet System Plus Tips and Tricks Excel Payroll Time Sheet System
Plus Tips and Tricks
One of the more arduous tasks in smaller companies is calculating time sheets, often hand written or in word documents, for payroll. This is especially true for smaller companies without a synchronized time clock computer system. Using some of the versatile functionality of Excel spreadsheets and their capability to link and transfer information from other spreadsheets is certainly a viable solution for this problem. This would work best in a company where all staff members have their own computer terminal and are interconnected via the company’s network. The system would require a master spreadsheet that is linked to all the individual Excel timesheets for all the staff.
The first spreadsheet to be created would be the one for inputting the staff member’s punch in and punch out times. Each staff would have their own timesheet under their name. It would probably consist of a bi-weekly or weekly calendar, depending on the payroll, with each day dated and a row of cells to input times in and out, for lunch and then total. There are formulas within that will calculate the decimal time from the standard time being inputted to allow it to transfer easily into the payroll program. There would also be columns for any sick, vacation or personal time used. The staff person, ideally, would sit down at their computer and before doing anything else open up the spreadsheet input the date and then time, either manually or with the shortcut keys. Then when they go to lunch sign out and in and when they leave for the day, sign out. The total for the day will appear at the end and the total for the week at the bottom, including any personal time taken in the other columns. Each staff person would have their own spreadsheet to fill out and they would all be located on the same network drive.
The master spreadsheet would have the employee’s names in one column and then the totals for the week, or whatever pay period payroll would use, in the subsequent columns all on one spreadsheet. The totals on this sheet would be laid out the in the same order as on the timesheets to make linking the cells easier. To link you would have the master sheet open and then an individual’s timesheet. In the master sheet you would type an = sign into the cell you want to link and then switch to the timesheet, select the cell to link to there and hit enter. You will return to the master sheet and there is now a direct link to that cell on the master sheet. You can link the other cells merely by copying that master cell and pasting it into the adjacent rows, which should mirror the location on the timesheet and set the adjacent links accordingly, or you can just repeat the above procedure for each cell.
This process eliminates having to deal with individual spreadsheets at payroll time. All information is now collated onto one format that can easily be inputted into the payroll system, possibly even by an upload rather then manual input. This eliminates wasted time in collecting the sheets and sorting through them, as well as so much paper being used on printing out timesheets and submitting them. The HR or Payroll department would always have access to view the original timesheet if there were any questions.
Tips and Tricks
1.) Anyone who has imported data from sources other than Excel into an Excel file knows that you often have to edit and take out extra spaces that appear between text. I was happy to discover that there is a very simple formula in Excel that can “ TRIM” the extra spaces out of the line of text quickly and easily. Say you have imported some text that looks like this:
I have imported a great deal of spaced out text.
The formula to eliminated the extra spaces is = TRIM(A9) A9 being the cell that the spaced text is located in. It will produce the following result which I did using the formula:
I have imported a great deal of spaced out text.
2.) Another great shortcut key to use if you need to quickly copy information from the cell above the one you are in is using the Ctrl + D key. The key will copy the contents of the cell directly above in into that cell. It works for text and number as well as any formula within the above cell. The formula will, of course, change its contents to the move. For instance, if the formula was totaling the row it was in, then it will adapt to total the row that the copy has moved to. So if you copy the formula = SUM(A23: D23) from the cell above to the row below it will turn into = SUM(A24: D24). However, if you wish to copy the formula above but keep the cells it references the same you can use the following shortcut key Ctrl + ’ and the contents will paste and remain unchanged. (Contextures, 2008)
References
Contextures (2008). Contextures — Excel keyboard shortcuts. Retrieved on May 7 2008 from http://www. contextures. com/excel. htm
Excel Hints (2007) Excel hints: tips, tricks and hints for microsoft excel users. Retrieved on May 7 2008 from http://excelhints. com/2007/09/14/excel-trim-formula/#more-26