5.Imput in Pounds only, as loose change will be used for Travel and Phone Calls
NON-FUNCTIONAL
1.Be easy and clear to use
2.Be personalized
3.Small enough to be emailed yet hold all the information we need
In addition it must be possible to easily change and set any information that we need.
As well as include all of the functions in the list, I will also include a few more that we may find useful e.g. A table to keep track of our progress on songs, what we need to practice etc.
The spreadsheet will use tables and macros to edit and change the amount of money and other details. Using "IF" equations I will show when the target has been reached and how much money is still needed. I will also use simple multiplication and division.
To input data into the system I will need the following data:
The amount of money being put into the account
The target amounts
The date that the money is being credited/withdrawn
All of this data will help me in many ways. Firstly, the financial page will help us keep track of our savings and savings. This will aid our attempts to buy better equipment. Secondly, the progress page, where we record how many practices we have had, what we have accomplished and a mark out of 10 for each song, will help us in practicing as we can see how we are doing and what we need to do.
We will have a money collection at the end of each month and input the data into the spreadsheet. We can also decide on how well each song is going and say this in the spreadsheet. The data for how our songs are going will be a mark out of 10, so I must think of a way to record this data easily. These will be our data sources.
The spreadsheet needs to know how much money is being put in and the target amount as it needs these amounts in its calculations, so it can display "Total amount" and "Amount needed".
In order to use the system, you will have to enter; the amount of money being credited/withdrawn (pounds only, this makes it easier to use), the date the money is/has been credited/withdrawn and use the UP and DOWN buttons to insert the amount of money to the total amount. In addition, the user must change the "Target Amounts" if necessary.
Design
System Flow Chart
The User Interface
A screenshot of the "Calculator" page is provided below. As you can see I have tried to make it as clear as possible, adding comments where possible so that it is easier to use.
As you can see, the spreadsheet has been designed to be user friendly and simple to use. To insert an amount of money into the "Total Amount Saved" You simply have to click on the "Increase" arrow Button with the corresponding amount (£1, £5 or £10) to increase by the amount you need. The spreadsheet then automatically shares the "Total Amount Of Money" evenly between the funds (e.g. as the Amps fund needs more money than the Bass fund the Amps fund gets more money in proportion to the other funds.), I will explain the formula later in the process section. When the target amount has been reached then the "Status" bar reads Complete. When the "Amount Still Needed" is red it means we have money to spare.
.
To make it easier to keep track of our spendings and savings on the next page we have included an "Amount of money Raised/Spent" column as shown below.
To use the Rating Bar, First you type in the Artist and Name of the song and then press the up and down buttons to change the rating out of 10. This can be changed as often as you want but you have to save for the Spreadsheet to remember.
Processing
Making increasing and decreasing buttons
Go into the "Forms Tool Bar" and select the "Spinner" options. Then select the area that you want the Spinner to be. Now the Spinner should have appeared in the area that you selected. Right click on the Spinner and press the option, now this Box should appear on the screen.
This kind of button is also used on the Rating Column on the second sheet using the same method, but changing the incremental change to 1 and the min to 0 and the max to 10, so it only shows a mark out of 10.
The Amount Raised Column
To make sure the system works I will get a member of the band to test that every aspect of the spreadsheet works. I will then personally check myself to see if there is anything that I missed.
I will go through every aspect of the spreadsheet and firstly, check that the computers processing is correct and then secondly, manually check the formula by reading through it. Hopefully this will get rid of any errors or bugs on my spreadsheet and improve the general quality of it. Also I will change any aspects that are hard to understand, annoying or just unhelpful.
As the amount of money was £122 the spreadsheet divided each Target amount by the Total Target Amount and multiplied it by the amount of money we have eg
1000/2750=0.3636rcing, 0.3636rcing * 122=£44.36
It then adds all of the values together in the column to produce the total, which is the same as the total amount of money (not the total target amount).
The spreadsheet then takes the amount raised and subtracts it from the Target amount to form the Amount Still Needed e.g. 1000-44,36=£966.64
The status bar then compares the amount raised with the Target Amount e.g.
IF(N11>=1000,"Compleat","Need More")
This is all correct so Paul gave me the thumbs up that it was all up and running.
I then did further testing to make sure that every aspect of the spreadsheet worked. This is what I found:
Increasing and decreasing buttons
Firstly, I manually checked that every increasing and decreasing button worked by clicking them and seeing if the box linked to the button increased or decreased by the
right amount. I then looked at the format control for each button and checked:
- The current value was correct.
- Minimum and Maximum values were correct e.g. 0 - Minimum
10 - Maximum
for mark out of 10 on the date list page, or that the "increase cash amount" buttons ( on the calculator page) Maximum value was as high as it could go (30000).
- That the incremental change was correct for every button (e.g. Incremental change 1 when increasing by 1, 5 when increasing by 5 etc).
- That the correct cell is linked to the button.
After checking all of the buttons, I found one error on the date list page, where one button was linked to the wrong cell, so I corrected it.
The Amount Raised Column
To check this section, I read through the formula for each fund (e.g. Amp, Mike etc) and made sure that the proportional change was correct. This can be checked easily by seeing if the fraction in the value in the cell over 2,750 (total target amount). I will then check to see if the value is multiplied by the right cell and finally check to see if the total of the column is the same as the amount put in.
After checking through all of this information, I found no errors or mistakes. I also used different amounts of money to input and then checked with a calculator manually to verify my data.
Status Bar
The status bar informs you when the target amount has been reached. This is easily checked as all I have to do is check that the right things are said and test this by inputting the target amount and making sure that the right phrase appears (e.g. complete or need more).
After testing this the only mistake I found was that I had misspellt complete, which I have now corrected.
Hyper Links
Finally I must check the hyper links on my spreadsheet (e.g. links to calculator.exe and Data List/Calculator page). This will be done manually and in the properties box.
All of the hyper links on my page worked correctly, opening all of the right files, programs etc.
General Use
The other members of the band have all been issued with a copy of the spreadsheet, and they have told me that it works perfectly on all of their computers, including the hyper link to calc.exe. This means that my presumption about this program being in the same files in all versions of windows was correct.
Evaluation
The spread has worked very well. When testing it showed invalid data when entered and correctly divided the money proportionally when the data was valid.
The whole band was highly impressed with the Spreadsheet, saying:
"This is exactly what we needed, it is easy and simple to use, giving us info as we go along. It is personalized yet is not too large in file size, the extra features are also very useful, such as the planner, which tells us when the next band practice is. We are also very impressed with the rating column as it gives us an insight to what we need to practice. It is even better than we hoped for, thanks Jim."
I am personally very pleased with the outcome of my spreadsheet, as I have been able to include all of the points that were raised in the Analysis. I also added other useful tools in, such as the Rating bar, which I am very pleased with.
User Requirements
1. Allow easy access to our financial information. - Completed - whole spreadsheet evidence.
2. Be easy and clear to use - Completed - Band could all use it and explanatory as it has comments to explain how to use.
3. Work out how much money we have and need - Completed - Calculator page evidence.
4. Automatically work out how much each fund has and show how much more we need until the target amount is reached - Completed - Amount Raised/Amount Still Needed and status columns evidence.
5. Personalized - Completed - Pictures evidence
6. Small enough to be e-mailed yet hold all the information we need - Completed - Only 50.5kb (Small enough to fit on a floppy disk or be e-mailed)
7. Proportionally divide the money that we have between different funds depending on how much is needed - Completed - Amount Raised column evidence
8. Imput in Pounds only, as loose change will be used for Travel and Phone Calls - Completed - All entries to nearest Pound, no pence.
I also think that there are ways in which I could improve my spreadsheet, I would like to make my spreadsheet automatically record the date and the amount of money credited/withdrawn. Another thing that I want to include are HyperLinks to Tabs for the individual (tabs are another word for music) so on the rating column you can click on the song name and the tab would open. I would also like to personalize it further by adding sounds and links to other files that may be useful. This could be included in a new version.
