A couple of months ago my friends and me formed a band called “MISK”. As we are new to the field of accountancy, we are finding it hard to keep track of our spendings and savings. We also find it hard and time consuming to evenly share out the money between different funds (we have different funds for different things e.g. Drums, Amps etc and they all need different amounts of money) and ideally we need to share out the money proportionally between each fund.
So we have all decided that we need something to help us easily manage our finances.I am therefore going to design and make a spreadsheet that will help us keep track and organize any of the little money that we are able to save, and how we should spend it. As our needs and funds will be continuously changing, a spreadsheet is the best solution as it is able to change and react when information is inserted and is able to perform complex equations automatically.
A lot of data is to be stored, so a database could be used, but a spreadsheet package is better because complicated calculations will be performed on some of the input data and this can be done on a spreadsheet but not on a database. Another advantage of using a spreadsheet is that I am also able to show tables and other information like information that the other members of the band need to know. I will also be able to email them this sheet and keep them posted on how our money is doing.My spreadsheet must be easy to use and understand, as other people other than myself will be using it, so I will put information on the page that explains how to use it and how it works and make it as user friendly as possible.The actual spreadsheet package I will use is ‘Microsoft Excel 2000’ because it is available to me at school and home and has all the facilities required to produce the spreadsheet needed.
The project will be written up at home on ‘Microsoft Word’.I will be using the school computers (PCs from Compaq) and my home computer which is a Gateway Pentium3. The other members of my band also have compatible versions of Microsoft Excel so this is another good reason to use it.
AnalysisI have talked to the rest of the band and we have produced a list of the features that we want the spreadsheet to have and be able to do:FUNCTIONAL1.Allow easy access to our financial information2.Workout how much money we have and need3.Automaticly work out how much each fund has and show how much more we need until the target amount is reached4.
Proportionally divide the money that we have between different funds depending on how much is needed5.Imput in Pounds only, as loose change will be used for Travel and Phone CallsNON-FUNCTIONAL1.Be easy and clear to use2.Be personalized3.Small enough to be emailed yet hold all the information we needIn 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 accountThe target amountsThe date that the money is being credited/withdrawnAll 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.DesignSystem Flow ChartThe User InterfaceA 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.ProcessingMaking increasing and decreasing buttonsGo 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 ColumnTo 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 eg1000/2750=0.3636rcing, 0.3636rcing * 122=ï¿½44.36It 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.64The 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 buttonsFirstly, 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 theright 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 – Minimum10 – Maximumfor 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 ColumnTo 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 BarThe 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 LinksFinally 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 UseThe 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.EvaluationThe 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 Requirements1.
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 evidence6.
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 evidence8. 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.