Heath Mount school is located in the beautiful grounds of watton-at-stone. It is roughly halfway between Hertford and Stevenage. Below you can see a picture of the school, its logo and a map of where it is situated.
Heath Mount School is a very well known boarding school for girls and holds 250 students. There are 45 day girls but the rest are either weekly or fully boarders. The fees for daygirls are 3,000 but for boarders it is 5,000. There are many extra activities available and extra teaching as well. Obviously because of this every pupil’s bill will be different accordingly. The school offers a balanced Curriculum based around about 15 or so subjects. The sport, music, and art are exceptionally good and many students leave with a talent for either of these subjects. Each department has its own budget, which helps them work out how much money is spent over a twelve-month period. The school employs cleaners; laundry staff, gardeners, and maintenance for help in making the school look good and keeping it tidy.
Every school or business must have some form of gathering and keeping information stored either on paper or computer (however a computer is a much better and quicker way of doing this). At the moment the school uses what’s known as a ledger system. The ledger looks like a big book of paper with a spreadsheet written down in it by hand. As you can see this way of keeping track of the schools information is very time consuming and human error can occur regularly. The loss of data would occur regularly as pages will get lost, and data will be constantly altered. A calculator would have to be used to work out all the calculations, and every bit of information must be hand written. As you can see the school I am working for is getting rather fed up with this method of keeping specific information so, I have been asked by school to apply a system that will be able to carry out the following functions in a quick and easy way, that will immediately stop the loss in money. Here is what my solution can do:
* Keep track of the money being paid in and out of the school (e.g. fees and salaries) in an efficient way.
* To be able to order goods and services for the school (e.g. the food and staff).
* To be able to order education books and teaching materials for the students and staff, by looking at the departmental budgets
* To format the data in a way that is readable and effective.
There are many ways to solve this problem for the school, and here is an adequate example;
I could use the alternative of graphical representation. I would store the information in a word processing package, and the results would be hand-written in graph form, on paper. This is not as efficient as a spreadsheet simply because it is very time consuming. I could also just use a word processing package without graphs, only this time storing information in tables. However I wouldn’t get the calculations done for me and they wouldn’t change automatically either. It would be very time consuming, as it would involve me having to create lots of tables etc.
I surmise that setting up this spreadsheet will take me about 2 weeks to produce. This includes the total expenditure, income and balance. To gather all of the information, and enter it as data will take me a while, but after 2 weeks at the most I am pretty sure it will be ready.
A spreadsheet enables you to perform complicated calculations using numbers as well as to be able to do complicated calculations that I can rely on. There are many different types of spreadsheet software, an example of some of these are listed below:
Quattro Pro: well matched with MS Excel, Quattro Pro is every bit as attractive as a spreadsheet program. The chart creation is very customisable, and the Cross Tab function enables you to view your data in any combination. Plus, a notebook is capable of handling one million rows of data. Comes as part of WordPerfect. Office.
Lotus: Lotus 1-2-3 has IBM Via Voice, you can enter your data verbally, live data can be imported from the web, updating your spreadsheet analyses, and instinctive programming can fill in further data automatically.
Bye Design: This “free shareware” program comes in 15 different languages, and the developer invites any requests for other languages. There are over 300 worksheet functions, plus text and number formatting. You can also convert data to six different chart types.
This idea of using a spreadsheet would match the schools ledger system in the most obvious and relevant way, but would provide a quicker way of doing things. There will be no need to calculate or write out each sum by hand every time it needs to change, because the spreadsheet will do it for you automatically.
Having looked on the Web I have come to the conclusion of using MS Excel programme. The reason for this is because it is widely used, lots of people understand it and training will not take time. There are many aspects to any spreadsheet that make life a lot easier! The spreadsheet software Excel, consists of many different tools enabling you to perform certain things to either make the data look more respectable, but also to make it easier for the spreadsheet to be understood.
* Using the programme Excel enables you to perform complicated calculations that are quicker to do than using a calculator. A formula enables this act to carry out. Below is a screen dump of a formula, and its result.
* Excel also involves comments, which are very useful when trying to explain how a particular formula works. Below is an example of this. The comment says the salary for the House Staff is currently 500 per month.
When the cell that contains the comment is not highlighted or the mouse is not resting on it, then the comment will only show a little red triangle like in the example. This stops them from getting in the way all of the time. Comments save time in an effective way.
Spreadsheets can furthermore engage graphs (like the example below) to show results. There are many different types of graph e.g. line graphs, scatter graphs, pie charts and bar graphs. Below I have used a bar graph.
* Formatting and conditional formatting are very important in making the spreadsheet easier to read and understand. Like I said above, you could use colour to make certain data stand out, different font designs, sizes, or by making it bold, Italic, or underlined.
Here is an example of “conditional formatting”, which you can see below:
Above you can see that all the months are formatted in the colour violet, the different terms e.g. summer, winter, and spring are all purple. The reason for this is because it makes it clearer and easier to understand. It will be quicker for the person using the spreadsheet to look up information. It is also very useful if lets say a group of people owed the school money; you could format them in red. I have also made it clearer to read by spreading out the information and making the titles bold etc….
* The “copy and paste” tool is also accessible in a spreadsheet along with “cut”. This can save you from having to delete a huge portion of work and then having to write it out again only this time in different cells. Like other software’s on a computer you can also save your work and use the “undo and redo” tool. This saves a huge amount of time, and makes it less stressful! Lastly you can save and delete the data, making it easy to make changes. Once you are trained and happy with using excel then you can learn about using other tools that will make your spreadsheet look more professional.
Design of the ICT system
Create new school record
Collect finance data Enter finance data Print off spreadsheet
Give head of department
data-capture form Load spread sheet check printer for paper
Head of departments Create new cell Print preview
Complete data capture form
Enter new data Print spreadsheet
Data capture form
Yes Error on form?
Completed by head of
Input by bursar Visual check of form
Validation Display error message
New standard letter for each Mail merge
Letter to head of
For my solution I suggest that you use what’s known as a network. This involves a hub, a server, a modem and cabling. The server is a very big and powerful computer, and is the centre of the network. Without it, there would be less security, and no way of checking logging on passwords. The hub is a machine, which allows multiple connections to the server. Meaning it connects large numbers of computers together without all the hundreds of cables joining into the server.
The modem is what connects the computer to the Web. The bursar’s computer will be the only one that will have the modem as it connects single computers to the web via cabling plugged in to a switch. Without this system the Bursar’s computer will not be able to access the web. For my solution it is not absolutely necessary to include a router, but sometimes you might find that it will come in use.
Finally you will need cabling, which will join each computer to the server and hub via a cable. This will enable the Bursars department to access all the data from the server.
Obviously computers are going to be needed and I suggest desk tops PC’s instead of Laptops because they are easier to use in a network. The computer is the base of this solution as it contains the Excel software. A printer will come in handy when the spreadsheet needs to be printed. For example, if the data got so big that it took up too much storage space in the computer then it can be stored in a file, once printed. Scanners are also a great help when information on paper needs to be scanned onto the spreadsheet, or another file.
Below is a table showing the type of computer hardware that I suggest the school should use. I have also listed the reasons why I suggest this hardware along with their specifications:
Ultra-slim, lightweight design – up to 75% smaller than a traditional desktop PC!
Intel(r) Pentium(r) 4 1.70 or 1.9 GHz processor.
Intel(r) 845G chipset with 400 MHz front side bus DDR-Synch DRAM PC2100 Memory. Integrated Intel graphics and network interface. Controller Optional.
Hot-swap Multi Bay Drive and optional snap-on legacy module. Optional Multipart and optional Wireless Cards. Common software image and pre-installed manageability software. Limited warranty with three years parts, labor, and on-site service
The Magnia Z300/Z310 is a compact design – saves space with no loss of performance.
Modular architecture – so easy to grow your network, as needs change
Wireless connectivity – frees people to work where they want.
Cutting-edge IDE RAID and hot-swap technology – for excellent performance and reliable data security.
Easy to-manage with state-of-the-art management tools including proactive web-based monitoring.
The Lexmark Z65 is a versatile, high performance printer perfectly suited for small business, home office and individuals who demand the highest quality possible from a colour inkjet printer. With 4800 x 1200 dpi, the Lexmark Z65 easily outclasses its competition. The Lexmark Z65 delivers higher definition than ever achieved before delivering output so sharp and vivid, that photo quality prints and laser sharp text are possible, right out of the box. Accurate-Feed paper handling virtually eliminates paper jams while handling a wide range of paper from envelopes and transparencies to paper stocks
Feature-rich and built for productivity, HP scanners deliver picture-perfect results every time. You have a lot of choices. From compact photo scanners to high-end models that include innovative automation, you’re sure to find a scanner to match your individual needs. HP ScanJet digital flatbed 3570c scanner makes it easy to express your creativity and share the results – with 1200 dpi/48-bit color, a built-in illuminated adapter for 35 mm slides and negatives, plus four one-touch buttons including “e-mail”, “scan”, “copy”, and “HP Memories Disc Creator”. It is a superb solution for anyone looking for the flexibility to achieve photo-quality results from 35-mm slides, negatives, prints and even 3D objects, as well as the ability to share images by e-mail and create personal multimedia presentations with music.
For this solution for the bursar’s problem I have suggested to use the software Excel. It is a powerful spreadsheet, which fulfils our needs. It is reliable, known by many users, offers all the facilities we need as mentioned in the Specification part of the write-up above.
To allow the server to work, it is gong to need specific operating system. Windows NT4 is a more powerful operating system required to run a network, rather than using windows 98, or 95, and each Pc will have windows XP Pro. Office is the main software needed as it contains excel, word, outlook and PowerPoint. I am suggesting outlook because it is a very useful organiser. The bursar will be able to keep track all of his appointments etc.., Word enables memorandums to be sent to teachers and excel is the spreadsheet software which will contain and keep track of all the data the school has collected, in a quick and efficient way.
MS Office-Excel, Word, PowerPoint & Outlook
1 copy for each PC
Windows XP Pro
1 copy for each PC
1 copy for server
1 for one PC
Heath Mount School
Tel: 01438 856734
Equipment: Please specify
Photocopying estimate for year
Field trips/Visits etc
Any other requirements
I chose this particular layout in my data capture sheet because it makes it clear and professional. The bold text makes the important data stand out, and the dotted lines look neat, but also are there for a rough guideline on where to write down the information. Above I have used the schools logo, and down loaded from the web (http://dgl.microsoft.com/?CAG=1) different letters for the beginning of the schools name. This looks smart and proficient. I have also added the schools address, telephone number, and e-mail address. Most professional letters from companies or big businesses would look something like this, and I wanted to try and get this effect with my data capture sheet.
Data validation checks that the data of the right type. The computer can perform validation automatically whilst the data is being entered. There are four main validation techniques.
Range check: This checks that the data is within specified range e.g a person’s month of birth should be a number between 1 and 12
Presence Check: This makes sure that important information has actually been entered.
Check Digit: This checks that numerical data has been entered accurately. The final digit of a number is determined by a formula that uses all the previous digits. So if the numbers been entered incorrectly, the digit will be wrong.
Data type check: This checks that text hasn’t been put where numbers are needed, as an example.
Above you can see that where I have typed in the formula for cell d7 it has come up with #NAME? This shows me that the data I have used is not correct. Instead of typing =SUM(B7multiplyC7) I should have put =SUM(B7*C7). The spreadsheet recognises that “multiply” is incorrect, but if I used “*” then it will understand that I am asking it to multiply B7 by C7 and will calculate the data and give me a numerical answer.
Below you can see that I have corrected my mistake and replaced it with the correct formula and it has given me a correct answer.
I have also shown another type of mistake below. You can see that in C12 I have written in “one hundred and twenty” as text instead of numerically. The spreadsheet has spotted my mistake and come up with #VALUE! in cell D12.
Below you can see that I have corrected my mistake by writing 120 numerically, and the spreadsheet sees it as correct.
The reason why the above shows one with #NAME? and one with #VALUE! is because when the #NAME? error value occurs Microsoft Excel doesn’t recognize the text in a formula. When the #VALUE! occurs it means that the wrong type of argument or operand is used, or the Formula AutoCorrect feature cannot correct the formula.
This is a different process that the computer uses to ensure that all the data entered is correct and accurate. It checks that old data is the same as new data. There are two different techniques and processes of verification, which takes place.
* Proof reading- this is when somebody reads out aloud the data entered onto the system and then compares that data with the original data. Then if the person finds any different data they can easily edit and correct it.
* Double entry- this is when data is actually entered twice by two different people. The computer is then given the job to compare each one and find any differences of the two. Any errors found will be corrected which will make the spreadsheet more accurate as the bursar will know for sure that it is correcting mistakes.
There are a couple of problems with verification as double-entry is very time consuming and expensive, and proof reading doesn’t necessarily eliminate human error which also makes it time consuming.
In my spreadsheet all the numerical data was formatted as currency with two decimal points. The reason why I chose to use this formatting is because currency is more commonly used in spreadsheets and the decimal point makes it clearer to read. Most people use this sort of currency format for finance sheets such as this one with St Mary’s school, however the accounting format aligns the numbers and currency perfectly under one another making it clearer to read. Accounting is a more professional way of setting out numerical data, and does make is clearer to read, however for the business I am doing it is better to use currency.
Here is an example of currency formatting and accounting formatting:
Above is a screen dump showing how to format the cells on your spreadsheet. You can see that I have used currency, and 2 decimal places.
Obviously to determine my numerical data, I need to have some form of formula that I could use to calculate the numbers.
* The formula I used for adding three pieces of unique data would have been =SUM(B39,B26,B17) as an example. Here is a screen dump below to show this.
* To multiply data I would of done something like this =SUM(B4*C4), as an example. Here is a screen dump below to show this.
* The formula which I have used to add a group of unique numbers together is =SUM(D16:O16) as an example. Here is a screen dump below to show this.
* The formula below shows the random generator. You can see that I have typed in =RANDBETWEEN(10,10000) this will give me a random number between 10 and 10000. Every time I close the window, the number will change. Here it is 9,430, but as soon as I close the window the number will change to something else.
I have decided to print out my spreadsheet in landscape format. The reason why I have done this is because it makes it easier to see all the data, as the spreadsheet is formatted in a way that fits more data in landscape form. Below is a screen dump showing how I could have changed it to Portrait or Landscape. If I wanted to I could also adjust the magnification and try to put all the data on one page.
I have gone into Page set up and clicked on the “page” tab. below the subtitle “Orientation” it shows Portrait format or Landscape portrait. You can see that I have used Landscape.
I have formatted my cells like the screen dump below to make it clearer to read. The colour formatting that I have used makes it easier to pick out specific data, and does this by separating it out.
* I could have also used grid lines to make my data stand out more. Here is a screen dump below to show how I would have gone about doing this:
I have gone into Page set-up and clicked on the “sheet” tab. You can see just below the sub title “print” there is a tick box with the word Gridlines next to it. By clicking in the box, it shows up the gridlines once printed, making it look clearer. I have also got a tick in the tick box next to “Row and column headings” so that it portrays the cell numbers on the side, and their headings.
Below is a screen dump of what my Expenditure spreadsheet would look like once it has been printed. You can see that it has got all the cell headings, and numbers on the side.
* Finally using “comments,” (which I mentioned in specification) enables you to understand what each cell and its contents means but by looking professional at the same time. If the mouse is not resting on the comments it will hide them and only show a small red triangle when they are not needed, but if you wanted to print out your spreadsheet with the comments showing then by simply selecting “view” and then “comments” it will show all of them at the same time, pointing at the correct cell. To see how it’s done see fig 1, and to see the end result see fig 2 below fig 1.
It is always very useful to actually know if your spreadsheet is working otherwise it is a complete waste of time and money. I will know if it is working by using what’s known as the random generator. This will make the number random meaning it will change each time you close the document.
For example if I put in cell G13 =RANDBETWEEN(10,1000) the number will constantly change each time the window is closed but only in the borders of 10 and 1000. In this example below you can see that cell G13 has got a number 1,203. If my spreadsheet was working correctly then this number will change each time I close this document.
Once I closed this spreadsheet and opened it up again I found that my cell G13 had changed from 1,203 to 1,796. I am now satisfied that the Random Generator is working
I would also know if my spreadsheet was working by making sure that the calculations are all correct and change when needed to. We will be able to see this if the balances change accordingly. For example if we had a mass of new girls then the balances would change. Below is a screen dump of the original spreadsheet and another spreadsheet containing changes:
My solution is operated on a network, and to access the network and find the data on the spreadsheet you will need to follow these step-by-step instructions
1. Log on to the Heath mount’s school network. The I.C.T department will issue your username and password to you.
2. Part of the desktop you see is shown below.
This is a screen dump of a shortcut to the “Heath mount Finance Package” where you will find the spreadsheet containing the schools data.
3. Double left click on the Heath mount Finance package and this will launch the spreadsheet package.
Once you have successfully got into the spreadsheet you will find that in the bottom left hand corner of the spreadsheet there are 4 tabs each with their own title. These are different work sheets, which contain various data for the school. If you select each tab they will come up with different work sheets showing certain bits of data.
Money school Money school spends Money school gains or loses graphical representation
gains of balance
* To enter data into the Income worksheet you select cell B7 and enter the new fee for boarders. See fig below
* Above I have selected cell B7 and entered the new data for fees for boarders. I have typed 5525 and because the spreadsheet is set as currency it automatically changes the number to 5,525
* To enter new data for number of pupils, you need to select cell C7 and type in 230-see fig below. You can see that the cells containing fees for the winter term, Total # of students and annual total have been entered automatically saving you from having to do it.
* To insert comments is a very useful way of understanding data in each cell. Here is how to do it:
1. Select the cell in which you would like the comment to be in and then select insert comment. See figs below
Here you need to enter the data explaining the cell
* To insert columns you need to right click the column next to the place where you want the new column and select “Insert”. See figs 1 and 2 below showing how to do it, and the result
* To insert rows you need to right click on the number on the side to where you want the new row and select Insert. See fig 1 on how to do it and fig 2 with the result.
* It is always very useful when you know how to use a graph. This enables you to see the results of your spreadsheet in an easy way.
* Below is an example of how you can do this.
1. Firstly you need select “Insert chart” and a dialogue box will come up. See fig below
1. Above on the left hand side is a selection of different chart types that you could use and on the right is a selection of different chart sub-types.
2. Once you have selected your choice you must then click on “Next” where there will be more instructions on what to do.
3. Follow these instructions and you will get a chart that is suited to you on your screen.
* Below are three screen dumps of the tools that you will find in excel, which you can use to change your spreadsheet in any way. Here are some examples of what you could do.
If you want You can merge or You can change the
If you want to to make your centre the data colour of your font
Change the font, and font bold, Italic
The size Select arrows Or underlined
You can sort the data Here you can select
You can cut, copy and ascending or descending a graph
Paste your work
* To save your Spreadsheet you need to follow these instructions.
1. Select “File” and then “Save”
* To print your spreadsheet out you need to follow these instructions.
1. Select “File” and then “Print”
2. A dialogue box will appear on the screen asking what printer you want to use, the number of copies you want to print and which pages you want to print. See fig below
To evaluate I am fairly confident with my spreadsheet. I think it has worked well, and it seams to be checking and correcting any mistakes I made. I can see this when it comes up with errors like #VALUE! Or #NAME?. I like the way I have set out my design, and the colours I have used. This portrays the spreadsheet much clearer so that when looking for specific data it is easy to do. See fig below
I have also used lots of comments so that when you look back at the data on the spreadsheet the comments are there to explain what each cell means. I have liked the way I used the random generator, so that I know if my spreadsheet is working and also so it will give me random numbers so that my balance will change. I like the way I have set out my spreadsheet by having four different tabs each containing a different worksheet with dissimular data. See fig below.
Money school Money school spends Money school gains or loses graphical representation
gains of balance
All of the data wont actually fit onto one sheet any way but by using different sheets for specific data it makes it much clearer and more organized.
I set a time limit to complete my task, which was 2 weeks and I did it in about a week and a half, which I am pleased about because it didn’t take me as long as I thought. I didn’t waste any time completing my task but got the data that I needed quickly and efficiently.
I have also included three different spreadsheets only the data is slightly altered in two of them making the balances and the graph change. This helps me see that my spreadsheet is working.
I am pleased with the Excel programme in which I have used because it is very well known and training people to use it will hopefully not take too much time. It is an updated version so there is plenty of memory for all the data needed and for much more.
There are a few disadvantages with my spreadsheet and that is I didn’t include my “outings or Internet fees” in my expenditure as well as my Income. With both of these examples, the parents are paying for them, however the money for it is also going out in expenditure as the school pays for the Internet connections to the company etc… I could have also had more worksheets, which would separate my data out further into categories making it even more organized.
In general I am pleased with my spreadsheet. If I could go back and change a few minor details I would however they are not completely relevant.