-
The cost of printing the programmes → £175
-
The hire of the public address system → £110
-
The overtime for the caretaker → £27.50
There will be other money coming in also, however. The school is receiving sponsorship and is renting out part of the property:
-
Sponsorship from Helmcroft Hall → £80
-
Rent from the ice cream van → £50
It is essential that I know the price of making the drinks being sold, so that I can accurately work out the actual profit being made. Luckily we were given this information also:
-
Hot drinks → £0.15
-
Cold drinks → £0.25
There are once again, two different programmes that I would be able to use for this task. They are Microsoft Excel and Microsoft Access. I have chosen Microsoft Excel, however, as my previous work with both of these programmes has shown me that Access could over complicate this task as it is intended for other uses, whereas Excel is specialised in this sort of work. I will be able to present the data given clearly in Excel, and I can make calculations using simple formula that I have learnt previously. I will also be able to alter the values in some of the cells and it will automatically update the totals, without me having to re-enter the formula. It will also make it easy to update as the formulas will already be present and the only things that will need altering are the prices for that year which is extremely easy and quick to do. There is a minimal risk of calculation errors, as the programme adds up totals etc. itself, without any input from the user except the formula and cell references. There are also shortcuts such as ‘drag and drop’ which moves the information in a certain cell or cells, to another one without having to retype or delete anything, making my work more efficient and quicker to complete.
I will also be able to look through the data given easily, and delete any that is irrelevant, making the chart look as simple as possible. This also gives me access to more background knowledge of the type of system that I am being asked to produce.
To make sure that the spreadsheet is easy to understand and clear to read, I will make a sample chart to accustom myself with the system.
Firstly, I will have to work out the total amount of money being spent on Sports day so I will then know the minimum amount that will have to be raised. I can then account for the sponsoring and any other money coming in other than from the refreshments etc. Then I can calculate the cost of the drinks to the school, and use the information given to me on the predicted sales in the varying weather conditions, hopefully resulting in 5 different total costs. This will show the minimum amount of money to be raised whilst ensuring the school makes a small profit.
I will then have to work out the most apt price for the cold drink while the hot drinks are 60p as long as it is no more than 60p, as previously mentioned. The amount of cold drinks purchased will obviously be depending on the weather as more people will be expected to buy cold drinks in hot weather etc, so I will vary the price because of this for each type of weather. I will do this by using the other information and seeing which cost stops the school losing money. Next, I will do the same but with the price of a hot drink being 55p.
Then, I will find the final cost of the drinks by choosing one of these possibilities by seeing which makes the smallest profit but does not enter minus numbers at all (i.e. lose money).
I will test the spreadsheet for warm weather using information given to us:
If 150 hot drinks sold @ 60p and 400 cold drinks sold @ 54p, the profit should be £1.
If hot drinks are sold @ 60p and cold drinks are sold @ 53p, there should be a profit of -£3 (i.e. a loss)
There will be several formulas which I will require the knowledge of to successfully complete this task. The cells are referred to by their position on the grid, e.g:
…this cell is B2.
To add the values in cells together, you do the following formula, assuming you want to add A1 and B3 together:
=SUM(A1+B3)
The formula will then be replaced with the total. To subtract values, the same formula can is used, but the + is replaced with -.
To calculate the sum of consecutive cells, a short cut can be used:
=SUM(A1:A5)
It is also possible to multiply and divide values. This can be done using the formulas above, but using the * symbol for multiplication and the / symbol for division instead of the + or : . The word SUM is not necessary for any other formulas apart from addition and subtraction.
I will also be required to convert the values to monetary figures, which is done by pressing the currency button situated in the top tool bar ()
Task 5: Implementation
The first sheet contains the information that was presented in the booklet. I typed in the fixed costs in and the fixed costs out and any other predetermined data. I then worked out the total of these to help with the charts I was to be producing later. I also calculated the total cost of the drinks by multiplying the amount of drinks predicted to be sold by the price to the school, and then therefore the total amount of money being spent on Sports Day. This gave me the total amount that needs to be raised to cover the cost. I accounted for all the types of weather. This first sheet can be used again next year to document and calculate the costs for use in the main system.
This is all the information that I needed before I started to create the system. Once I had completed this and had calculated all of the totals, I could begin to produce the necessary spreadsheets.
The second sheet contains the main system. I had previously experimented using different formulas etc. in a different form, but as most of it ended up being irrelevant I did not include it in the file. I did however include the finished product.
I made it so any negative figures were highlighted in red, by right clicking, choosing the format cells option and changing the cell classification to currency and clicking on the red highlighted minus number. I set out the main form in two sections. One assuming the price of the hot drinks is £0.55 and one assuming it was £0.60. I also calculated the totals of the hot drinks depending on the amount predicted to be sold. I then worked out the profit made on the drinks, and then deducted the fixed cost of the day to see what overall profit if any, was being made in that circumstance.
I decided to increase my price by £0.01 each time to compare the results and continued raising my price until it reached £0.60. I did the same to the cold drinks results as I did to the hot drinks results, I multiplied the price charged for the cold drinks by the total number of cold drinks sold for each type of day.
Task 5: Evaluation
I believe that my model price chart fits with the required criteria:
- It will have to show the minimum profit available to the school
- The price chosen using the chart much cover the total cost of Sports Day
- We must account for the money given by Helmcroft Hall and the ice cream van, and deduct it from the over all profit total.
- There must be 5 different spreadsheets, one for each weather condition with the prices altered accordingly.
- It must be easily updated for use in the following years
- The prices chosen must be payable (i.e. they need to be integers)
- The chart should ensure that no money is lost and not too much is gained.
- The price range of a hot drink must be 55p - 60p
- The cost of a cold drink must not exceed 60p
I worked out the suitable prices for both drinks and the profit made in each weather condition. I used cell references and formulae to calculation totals and made use of the function in Microsoft Excel that changes the totals automatically as soon as the cells referred to are altered. I ensured the model was easily updated, much helped by the aforementioned function. I have printed off two tables that I created, one assuming that the price of the hot drink is 60p and the other assuming it is 55p. I referred back to my performance criteria and made sure that the price of the cold drink did not exceed 60p at all on the table.
I believe that this task was successful, as it met all the requirements and the testing proved that the calculations were correct and that it is an efficient way to work out appropriate prices for each circumstance. The testing verified this, as it corresponded with the information supplied. I followed the plan in the design, so that I wasn’t confronted with any unforeseen problems and the two forms I was asked to print as proof have been.
