Describe the business you will be designing the modeling system for and explain what they have asked you to create. Include details about how things are done now. Describe the limitations of the present system. Describe at least two examples “What if” types of question that your spreadsheet model would answer.
For this project I am going to be carrying out work for the Lawn Tennis Association (LTA) which is the Head of Tennis in the United Kingdom.
I have been given the role of organising a tennis tournament for them.
The tournament will be held at the Ash-way street garden indoor tennis courts. I have chosen this location because it has the security if it were to rain on the day.
I have chosen the date in the summer so the weather is more likely to be better and it is during the main tennis season.
Also the age group entering the tournament are going to be on holiday.
The Tournament I am going to create will be open for age groups of U12, U14 and U16. I will be finding the costs of balls, how much it will be to enter per event and if there will need to be a limit of players per event.
My biggest problem that I am going to encounter is that I am not going to know how many players are going to enter, so price the price of the entry fee will need to be explored.
One of the ways to solve my problem of player’s entry is to find out how many players have entered recent tournaments. I can then guestimate how many players might enter my tournament.
I will also be ringing various local tennis courts to find out quotes for renting their courts.
In my previous system I used a calculator which took a very long time. There was also the risk of human error.
Using a computer would save a lot of time and effort.
It would tell me how much to charge for the event if only a few competitors turned up.
It would also be easier to find out how much profit I will be making.
When I come to order food and drink or other products, if some stock didn’t arrive I could easily change the price of other products to make up for the loss.
B Analysis (3)
Before undertaking this project I need to find out the following:
Lucozade Sport Orange 12 x 500ml 7.39
Lucozade Sport Mixed Berry 12 x 500ml 7.39
Mars Bar 48 x sgl 10.65
Walkers Cheese & Onion 48 x 34.5g 7.75
Walkers Ready Salted 48 x 34.5g 7.75
Walkers Salt & Vinegar 48 x 34.5g 7.75
Walkers Prawn Cocktail 48 x 34.5g 7.75
Walkers Roast Chicken 48 x 34.5g 7.75
Walkers Beef & Onion 48 x 34.5g 7.75
Walkers Smokey Bacon 48 x 34.5g 7.75
Walkers Barbecue 48 x 34.5g 7.75
Uncle Ben’s Sweet & Sour 1 x 2.5kg 5.50
Boost Glucose 48 x std 12.99
OUTDOOR 4.50 3.50
INDOOR 16.00 11.00
WALK-ON (Indoor) 9.00 9.00
SLAZENGER AUSTRALIAN OPEN BALL
12 Dozen (4 ball can) = 2.50 per can.
* Manufacturer: Slazenger
* Range: SALE PRICE 90.00
A non variable cost is a cost that will never change for example in my tennis tournament the cost of balls will always stay the same. On the other hand a Variable is a cost that could change for example in my tennis tournament the entry fee will change depending on how many people enter the tournament.
Possible solutions to the problem:
Manual: Advantages of a manual system are:
* It does not cost very much in comparison to computers which are very expensive.
* You do not require specialist training. It’s easier to use a pen and paper, whereas a computer requires training before you can use it to its full potent ional.
* A manual system is a lot slower than a computer for the reason that you have to work out each sum one at a time. A computer can do it extremely quickly without the risk of human error that would come with manual system. (E.g. a calculator.)
A specialist Program is a piece of software that only does what you want it to do and when you require it. Unlike Microsoft programs which can be changed. Specialist Computer programmes are very expensive and it costs a lot of money to get a program specially made. If the system then goes wrong it would be very expensive to get the program fixed. Also required updates would cost a lot of money.
b) Using generic applications software:
The applications available to me are;
* Word processors
* Desk top publisher
* Presentation software
* Photographic editing software
* Internet Explorer
Spreadsheets are good for solving problems that involve handling numerical data and performing calculations. PowerPoint is good at dealing with multi media but not good at numerical data. Microsoft Word is good at handling texts. A desk top publishing piece of software for example Microsoft Publisher is good at making any form of letters, posters or flyers. Access is a data base package which is very good at managing large amounts of similar data for example, children in a school, or how much stock being kept in storage. It’s also good at searching and sorting through data.
Chosen method of solution:
I have chosen to use a generic application which is an Excel spreadsheet.
Software Type Choice:
A spreadsheet includes the following features which make it suitable in solving my problem:
1). My problem is based on calculating profit and loss, so using a spreadsheet which is very good at handling numeric data will be very effective at solving my problem.
3).Built in function like SUM which makes it easier to use are very good because it decreases the chance of human error.
4). Another is that a spreadsheet lets you validate data so that its entered, so that if a mistake is made and put in the recycle bin it would not allow it.
5). A good feature about spreadsheets is that it lets you protect cells so then users can not change formulas. It also allows you to set up passwords so that people who are not authorised can’t access the spreadsheet.
It lets you set cell formats like colouring cells and boards to make the system easier to understand (because if it is colour coordinated it takes less time to find what you’re looking for).
If you used a manual system like a calculator you would have a large calculation, whereas using a spreadsheet it lets you break down a large calculation in to lots of smaller parts.
C Specification 3
My solution for The Lawn Tennis Association will have to meet the following criteria:
* Able to hold a list of fixed costs, for example the price of food and drink at my tournament.
* Allow the user of the system to enter the number of players who have entered and the cost of entry.
* Able to work out if I am going to make a profit or a loss from the tournament this will be a great help to the user because it will save them a lot of time because they will not have to work the calculations out them selves.
* Able to show the user if the tournament is going to make a profit or a loss, the advantages of this would be that it would have a more friendly inter face and it would be easier and quicker to use and find if you made a profit or a loss.
* Will have text labels to make sure that it is easy understand.
* System inputs like the amount of players who have entered the tournament must be validated to ensure they are sensible, because if only two people entered it would not be a very successful tournament.
D Design of the ICT system 4
The flow chart above shows how my system works.
First of all it will load up from the hard drive. The user then types in the mark up they want but it must be between 20 and 80, otherwise it will take them back a step. They then enter the number of entries. This number must be a whole number, higher than 0. If they do not enter the correct number they will be taken back a stage until they enter correctly.
Once I have formatted the spreadsheet I will add the formulae I need for the calculations.
What needs calculating
Formula in words
Cell formula will be in
Price product will be sold at
Price brought + mark up percentage
Profit made from sale of 1 item
Price sold – price brought
Total profit made from item
Amount of times sold times profit made
Total profit made from all of the item added together
Add all the total profits made from each single item total to get one overall total
Profit made form one event
Amount of entries times cost of entry
Total profit made from events
Singles profit + doubles profit
Total profit made
Total profit of entries and sales – fixed costs
Is it overall profit or loss
If over 0 it says profit and if under it says loss
Before I start on this project I will need to decide what needs to be done and in what order:
1. Choose the best software for the task.
2. Create a flow diagram for the model.
3. Design the user interface (worksheet screens) for the model.
4. Work out the rule of the model and the formulae needed.
5. Decide what hardware and software will be needed to run the model.
6. Create the model.
7. Test the model using the sample data
8. Evaluate my model against the original specification (performance criteria)
9. Write the users guide
The LTA want me to suggest hardware required to run a model to manage all of the stock required for the tournament. I could either use a Laptop or a Desktop. I believe a Laptop would be more appropriate at the tournament as it is portable so easy to maneuver.
A Desktop would be better if I did not need to move it around, because it would be less likely to be stolen; however in this situation I will be using a laptop as I require the portability.
I will also need Microsoft Excel to support the model.
* Due to using a Laptop I will not require a Mouse or Keyboard as it will have those features built in to the machine.
* Processor. The minimum required would be a Pentium 2 with 133 megahertz or higher to be able to run Windows XP or Excel.
* Memory is required on the machine to store the project. The minimum memory requirements needed to run multiple windows is, 24mb of RAM plus additional 8mb of RAM for Excel (below Windows XP). 128mb of RAM plus additional 8mb of RAM for Excel. (For windows XP). If your RAM was not big enough the pc would be very slow and would struggle to run the program.
* To run Microsoft Excel, a minimum of 140mb of available hard disc space. An additional 115mb is required on the hard drive when the program is installed. If you did not have a hard drive you would not be able to store data on you computer or install new programs such as Excel or Windows XP.
* CD-Rom and USB drives. Theses are both required to load up saved information on either a disc or a USB pen drive. Without either of these the only way to retrieve information would be to download it off the internet.
* Display. The laptops display would need to be Super VGA,
(800 x 600) minimum 256 colors. Without a compatible screen you would not be able to view the appropriate programs required.
(ii) Software resources required (2)
Microsoft Excel is the only spreadsheet that I have available to me in school.
Excel has the ability to handle text for heading, text labels and numbers for the data.
It also allows me to use a formula which is very useful for calculating profit, loss and markup. It will allow me to input validation which is also helpful because it will put restrictions on inputs to the spreadsheet, so the user doesn’t have too little mark up.
Excel can have a Marco so that you can reuse the spreadsheet without manually deleting. Comment is another function which helps to make it easier to use and being able to format the cells is very useful for currency and stopping the user changing a cell.
(iii) Data collection, data capture and input (2)
Text labels are very important because without them the user would not be able to use the spreadsheet.
* They explain the spreadsheet to the user.
* They show important information on the spreadsheet and make it easier to use.
* The text labels I have added are expenditures, price brought etc you can see this below.
The next step I took in creating my spreadsheet was to add the numeric data I found when I was doing my research on the internet.
It includes the price that I brought the expenditures for. I did this because the system needs it to calculate the profit or loss. This is illustrated below.
Lastly all the comments were added to the spreadsheet to help the user find the right inputs to the system.
These can change all the time, so in my case the profit and loss and players and mark up. The comments will help the user enter around the right values. Below you can see this and how I did it.
(iv)Data verification and/or validation (3)
I have set up data validation because if data is entered incorrectly you will get back incorrect data message.
Data validation means that when you enter incorrect data there will be an error message and you will have to re-enter the data. For example if I were to enter 5.4 players entered my tournament this would be impossible so there would be an error message saying, please enter a whole number higher than 1.
Another data validation in my system is on the mark-up. A number between 20 and 80 must be entered. This is because if it was less than 20 you wouldn’t make enough profit and over 80 would be too high.
(v) Data and/or program structures formulas explain why (2)
My spreadsheet contains many important formulas:
1. Work out the price which my products will be sold at:
I take the original price I brought the product for then add the mark up which the user can change. I will need to know this to work out the profit.
2. Work out the profit:
This has been done by subtracting the trade price of the products, from the sale price. This is required so I know how much will be profit/loss.
I can then work out how much money I am going to make.
3. Multiplying the profit by the amount that is likely to be sold:
This just saves the users a lot of time.
4. Adding all of the products total profit together, leaving a total profit.
Working out the profit made by entries for the tournament will be done by multiplying the number of entries by the cost. Then adding both singles and doubles profit together to get an overall profit.
To work out if I make an overall profit or loss I will add the total profit from the food and drink, plus the total takings from player entries. Then take away the fixed cost of court hire. This will leave me with a figure. If it is above 0 then I have made a profit.
5. Tells me if I have made a profit/loss:
A box has been created to easily tell me if a profit or loss has been made. It makes it easier and quicker to see rather than having to deal with numbers and figures.
(vi)Output format (3)
To make the spread sheet more clear and professional I have used boarders and appealing colors.
By using color and clear boarders it has helped to separate the spreadsheet into obvious contrasting sections. This makes it easier and clearer to enter/change data.
The inputs have been put in bold and slightly bigger text size.
The title is in the biggest font to distinguish what the spreadsheet is for.
F Testing (4)
Test strategy – How you will work out the expected results.
My system will be tested for 50 entrants with a mark-up of 60%.