The end Table should look along the like this (shortened version):
How I created sheet 2, improved Payroll: -
- I opened my payroll file and copied the table on sheet 1 I then clicked on the sheet 2 tab and pasted it onto this sheet.
-
Now on sheet 2 I changed a few things to the original table. I added Overtime rate underneath Tax rate in cell A5 where NAMES was and the rest was added on.
- I also changed the Tax rate to 22%.
-
In cell B5 I typed in 2 for the overtime hours. I then added Overtime hours in Column C (cell C6) then Rate of Pay next to it in cell D6. After that I added Gross Pay, Personal Allowance, Tax National, Insurance and Net Pay all in the same Row (6)
- I then added the appropriate employees information under the headings.
-
However I needed to add formulas for Gross Pay, Tax, National Insurance and Net Pay. So for Gross Pay I used: =(B7*C7+(C7*D7*$B$5)
-
For Tax I typed: =(E7-F7)*$B$E
-
For National Insurance I typed: =E7*0.05
-
Finally for Net Pay I used: =E7-G7-H7
The improved Payroll sheet should look like this:
How I created sheet 3, Payslips: -
-
I opened my Payroll file I had saved then clicked on the sheet 3 tab.
-
In the cell A1 I typed in the name of my business again which is Wizards Video Club.
-
In A2 I typed payslip for, in A4 I typed Date and in the last two cells below it I typed the week the employee began working and the week they ended working before they received their wages.
- In cell B2 I typed in the name of the employee and in the cell next to it I typed the month that they were working in.
- In Column B (cell b3) I typed in the hours worked as usual however I had to add some new headings.
-
Then in Column C (cell C3) I typed Overtime Hours instead of Rate of Pay. Then I added Rate of Pay and Gross Pay afterwards in Columns D and E.
-
I then added Personal Allowance in Column F (cell F3) and then Tax as usual in the next Column.
-
I added National Insurance in Column H (cell H3) before adding Net Pay.
- I then filled in the employee’s information from the stores paper invoices.
-
I know linked this sheet to the 2nd sheet by highlighting the employees row on the 2nd sheet then clicking on the Edit tab and Copy.
-
Then I opened up the new sheet I had just created and clicked on the Edit tab then Paste Special and finally Paste Link. This created a link between the two pages. This meant that if I changed the Hours worked or the Overtime hours on the 2nd sheet it will automatically change the 3rd sheet (Payslip).
The end payslip should look like this:
Interview with the Owner of the business: -
- How long has this business been established?
It started off at our home over 20 years ago when me and my brother starting selling videos.
- What is involved in running your business?
I buy the videos from various warehouses from east London, which are stored in my stores the videos go directly to my customers who have booked them in advance. If any videos are not rented out within a certain time limit we refund them. If the delivery is huge we have to use transport companies to deliver the goods, but if the deliveries are small or reasonable we use our own van, which is operated by one of the store workers.
- How do you record your finances, what is involved and how long does it take?
Once the goods are rented out we keep invoices, which are then filed. At the end of each month I personally add up the bills and calculate the sales, decrease/increase in stock levels, profit/loss, monthly expenses and VAT. This process takes up the whole day as the sums are lengthy and take up a lot of time. At the end of the year income tax is calculated referring to the profit or loss made throughout the twelve months.
- How useful will this new computer-based system be to your company?
The computer system has enabled my company to keep track of the latest stock levels, sales, staff expenses, etc. This meant my company can solve any problems (like if our expenses start spiralling out of control) immediately we can adjust the way in which we run our company, with a computer based system I will have better control over my company and its finances.
- What advantages and disadvantages do you think the computer system has?
The computer is accurate and will save me a lot of time but I am worried about the cost of a PC and software needed to run my finances, also if anything happens to the computer all my finances will be destroyed.
- What has the new system able to do?
The new system is be able to explain to the user the amount of videos staff has sold and at what quantity, also their expenses should was shown so that the profit/loss can be worked out. The information was to be displayed in an organised way so that it is user friendly and the user can access the data easily. For this the information needs to be shown in some sort of order, i.e. it needs to be shown in rows and columns. The system must be able to save the information so that progress can be tracked and any changes made.
- How has the new system perform?
The new system performed accurately, as it performed important calculations. If there were any mistakes then the overall profit/loss became incorrect but was changeable. It also has to perform quickly, when any of the data is changed, automatically all relevant data should change.
From this statement I can say that I have solved the initially problem because the new system I have created has benefited the owner. I have shown the owner that the previous manual method he was using was not as accurate as a computer system. Also he has saved a lot of space in his store and was bale to make the store look nicer and attractive.
EVALUATION
Other people opinions: -
Person 1: He thought that my spreadsheets were sufficient for the type of business I was running. He like the way in which it was set out and how I used the correct information to create payslips. However he thought that I would need a bigger database if I were to expand on my business and that I would need an on going maintenance. He thought that it was very simple to use and therefore didn’t take long to input information and see the result.
Person 2: He thought that my spreadsheet was very easy to use and was not as complicated as he initially thought. He liked the way it was set out because it wasn’t confusing. He added that he would have like to see more information about the employees i.e. their age. One thing he didn’t like about my spreadsheet was the payslips I created because it had unnecessary information like net pay and gross pay. Finally he added that my spreadsheet was very good for the business I was running because it wasn’t too big or too small.
On going maintenance: -
To keep an on going maintenance I will have to update my data at certain intervals like once a week. I will also update my information every time I sack or hire an employee. This is because I might get staffs names mixed up and give a payslip to someone how is no longer working for me or I could not give an employee a payslip because they are not on my spreadsheet.
My opinions: -
This project in my opinion has been a very successful one. I met the user’s need, which was my original and main target, and these needs were met with a sufficient amount of detail.
For the time allocated, I was satisfied with the way my spreadsheet was set up and indeed I used a variety of formulas and functions that were all relative to the work. These functions allowed me to set the spreadsheet to calculate data automatically in most cases, which is one of the main things I believed a user would need.
I think that I could have made my spreadsheet better by making it more detailed in certain aspects such as the length of time I set the spreadsheet over. This could only have been achieved if I had had more time to work on the project however, so it would have been too hard to include a great amount of detail in such a short space of time. I could also have included a wider range of items on sale as the amount I had were really for a smaller type store who would more often than not leave the transfer of data on to a spreadsheet due to there not being enough data to permit doing so.
Doing my spreadsheet over a year rather than a month would have given the user a much better idea of how their business is doing. Only doing a month restricts their knowledge of the store and a yearly record would enhance this view and allow them to make future decisions under better judgement.
I believe that I could have improved the discount aspect of my spreadsheet, as it is slightly out of sync with the rest of my work. The main detail for that particular worksheet was the names of the customers but again I could not have added such depth with the time allowed.
If I were to do this project again, I would perhaps do a different type of store to allow me and the user to use a variety of new and different functions, as well as get a better outlook on how the productivity of sales varies with different types of stores.
Overall, I have found this project a very interesting and intriguing one and am proud to say that I met the user’s needs and thus met the main target set out at the start of the work.
