Colin: What advantages do you think making this model for you would make?
Mr. Sheen: I believe it will save us time and money. We will not have to be calculating the cost for each paint job from scratch and we will not have to be looking for other information, as all the paint prices will already be in the computer. We will also be able to act quicker and present customers with the information on the moment and they will then not get fed up and go somewhere else.
Colin: How many workers do you have employed at the moment?
Mr. Sheen: At the moment we only have eight workers but only two work on each job. It is very rare when there are more but it does happen.
Colin: Do they have their own insurance or does the company have its own insurance?
Mr. Sheen: No we have an insurance, which covers them.
Colin: How many hours do they work in a day and how many square metres can they paint in that time?
Mr. Sheen: Each worker is only allowed to work six hours a day and in one day of work they will probably be able to paint twelve square metres.
Colin: How much does each worker get paid an hour?
Mr. Sheen: Workers have to get paid £8 an hour.
Colin: Why have you not thought about setting up a computer model before?
Mr. Sheen: Well, frankly it is because we haven’t made all that much money and because we only opened two months ago.
Colin: What type of jobs does your company deal with?
Mr. Sheen: Workers can only be employed if the area to paint is more than 45 square metres.
After gathering all this information (plus the costs of the more common brands and finishes, see appendix 1) I have decided to start my model to help this company and suit its needs.
Specification
Based on the information I have gathered my spreadsheet model will be structured so that it is user friendly and easy to understand. I will try to reproduce the most common situations that can be found in houses e.g. irregular shaped rooms. To make the spreadsheet more user friendly I will use different colours for different sections.
Because I will be using Excel for my model it will have a wide range of functions that I will be able to use, one example is the rounding up function. I will not be able to tell someone that they need 1.35 cans of paint to paint a wall they will need 2 cans.
When making my model, I will take many things into consideration, which I will be able to put into my spreadsheet (inputs: paint costs, room dimensions etc), and I will be able to’- find out any information the company wants or needs (outputs: how much it will cost to hire a painter for so many hours and so many days at a certain hourly pay rate). I will also find out the cost of each individual room so that the company can have more options for customers who wish to paint somewhere in their home.
The spreadsheet model will also enable Mr.Sheen to change any output he does not feel is reasonable by simply changing the appropriate inputs. It will merely take a few seconds, as the outputs will be connected to the inputs by formulas. The costs of developing the spreadsheet will be explained in the resources.
My evaluation will be based firstly on whether it works properly, secondly that it is faster at finding the cost of painting a wall of a certain size in a certain colour and finish than the current system and thirdly whether the shop assistants and owners find it easy and efficient to use.
Design of solution
After carrying out my investigation and analysing the results, and seeing how information is currently collected and used, I have decided that my model will be one that when a certain length and height is entered, the area, tins of paint required, total cost and how much and how long it will take a certain amount of painters to paint the wall will all be automatically worked out. First I will design the model, then I will test it to make sure that everything is running smoothly. After that is complete I will write up user and technical documentations, which will teach the staff how to use the model. To save time for the user, my model will show the cost of five different finishes of paint at for two different makes of paint the same time.
Below are some algorithms, showing how I created my model and how I designed different things within my model.
To carry out investigation of problem
- Find out method currently being used.
- Find out the disadvantages of method.
- Gather up all information needed to make model for example, cost of paints, cost of hiring workers etc.
- Ask why would using a computer spreadsheet model help to solve this problem.
- Analyse the information gathered and design spreadsheet.
YES
NO
To make the spreadsheet model
- Open up Microsoft Excel 2000.
- Open up a new spreadsheet.
- Write in headings for columns and rows, for example, tins of paint needed, total cost, etc.
- Write in suitable values for headings
- Connect cells using formulas in an appropriate way so that the total cost and other values are given correctly when cell values are changed.
- Make the spreadsheet user friendly, for example, by adding coloured areas, putting certain values in bold, etc.
- Add a title at the top of the spreadsheet.
- Test the spreadsheet if you feel that you need to.
- Does the spreadsheet suit the needs of the company? If it doesn’t go back and start again.
- Save the spreadsheet.
NO
YES
NO
YES
To connect cells with formulas
- Click on the cell you want the result of the formula to be in.
- Click on the = sign on the toolbar or enter an = sign in the cell.
- Click on the first cell you want to be in the formula.
- Then key in how you want it to be joined to the next cell in the formula, either by addition, subtraction, multiplication or division.
- Then enter the next cell you want to be in the formula.
- Carry on putting cells into to the formula until you have the correct formula.
- Press enter or the green tick on the toolbar.
- Is the answer acceptable? If not change the inputs.
- Change the value of one of the variables in the formula, one of the cells that affects the formula, to check if the formula is working correctly.
- Save the spreadsheet.
NO
YES
To save and store the model for quick access
- When you want to save the model, or any documents related to it, click on ‘File’ on the toolbar and then ‘Save As’.
- Click on ‘Save In’.
- Click on ‘C:’
- Create a folder in which to save spreadsheet with a suitable and easy name to remember, such as ‘Paint spreadsheet’.
- Then click on ‘Paint Spreadsheet’.
- Click ‘Save’.
- Then to be safer you could also save it in a floppy disk as another backup copy.
- Once the folder is created and you have given it a suitable file name save it in the ‘3 ½ floppy A’ instead of the C drive.
I will have used some of these steps already but I will have to follow all of them to make the best spreadsheet model possible. These steps are important because if you don’t save the work or only save it to the computer and it breaks down you loose all the information and if you don’t input formulas correctly you can mess up the whole model.
Resources
Now I will be describing the hardware needed by the company to be able to run the spreadsheet model.
Firstly, and most obviously, they will need a computer. They need it to do everything concerned with the spreadsheet. I would recommend a new computer such as a Pentium 4 1.7 GHz.
They will need a keyboard, so they can type information into the spreadsheet, and a mouse so that they can access the spreadsheet and its different features.
A screen will be needed to be able to view the spreadsheet.
A printer would also be of use for many different reasons. So that hard copies of spreadsheets can be printed out and kept as records as well as printing out other documents and information to do with jobs. A laser, colour printer would be the best one seeing as we want the colours we see on the screen to come out on the printed copies and also laser printers print very fast and the ink does not get wet or smudge. This though is very expensive and not accessible by the company involved, as they have a tight budget, so a normal ink jet printer will have to do. In this case we will be using a Hewlett Packard 930C DeskJet.
They will also need floppy disks to make regular backup copies of all of the jobs done and different spreadsheets used to calculate the costs of the jobs. I think that floppy disks would be better to use as backup disks than CD-ROM’s because even though they store much less data than CD’s, they are a lot cheaper, can be used more than once and spreadsheets take up very little space anyway so quite a few can be stored on one floppy disk.
Now I will be describing the software that the company needs to be able to run the spreadsheet model.
First they will need an operating system to be able to use the computer, but this usually comes with the computer so it does not need to be purchased. I would recommend the use of an up to date operating system such as Windows 2000 or Windows Millennium but we will be using Windows 98.
Then they will obviously need Microsoft Excel to be able to make spreadsheet models. I would recommend a new version of Excel, such as Excel 2000, seeing as this has the most features and functions which can be used to enhance the spreadsheet, but this usually comes installed on the operating system.
They could also have Microsoft Word if they chose so, to write up reports or other information concerning jobs, etc. Again I would recommend a new version of Word, such as Word 2000, seeing as it is the most updated version and has most new features, but again this usually comes installed on the operating system.
All the hardware and software needed to make the spreadsheet will cost around £1000- £1200 but seeing as the company does not have that kind of money at the moment the computer they will be using has been loaned to them by a family member of an employee.
