Download
1 / 34
E N D
In today’s lesson I started to create my membership fees spreadsheet on Microsoft Edexcel. I used Microsoft Edexcel as it was the most suited for creating the membership fees spreadsheet. I t would be a quick, efficient and easy way to create the spreadsheet. The DIDA WildCare website said that they need to set ‘membership fees for next year.’ They also told us that as having the adult membership fees ‘there will be two new options for younger members.’; WildCare Teens-13-19 year olds and WildCare Kids-under 13s. • The DIDA website states that the fact that the WildCare Trust is a charity and so the aim is to break even, not to make a profit. They point out that the problems if the membership fees are too high is that some members may not renew their membership and so there will be fewer new members.
The DIDA WildCare website provided us with the data that we would need in order to create a cash flow forecast spreadsheet. The website stated that we should develop the spreadsheet to ‘show income and outgoings for each month of next year, to allow you to try out different values for the new membership options and to allow you to try out different increases in fees for existing membership options.’ This is the data provided from the DIDA WildCare website and it shows all of the costs that WildCare pay and how much income they gain and their membership prices.
Using the data provided I had entered the cost of the memberships and how much the WildCare Trust had. I entered in the formula ‘=SUM’ and the multiplied the number by the costs so the formula was ‘=SUM b5*c5’ (*=multiply). Once I clicked enter the total was calculated. I knew that this same formula would work for the other 4 membership cells so I simply highlighted and dragged down so that Excel knew that I wanted to apply the same formula to the remaining cells and so once I had done this it calculated all the prices made. To work out the total I once again type in ‘=SUM’ then highlighted all of the total cells so the formula was ‘=SUM(d5:d9)’ then Excel calculated the total.
Secondly, after I had totalled up the price of all the memberships that were to be given out in January in cell d10 I had to copy this price into cell b14 which wanted the price of the memberships income. The website had already stated that memberships are only collected in January so I knew that the price was only required for the January column. In order the easily copy the price from cell d10 to b14 and ensure that no mistakes are made, in cell b14 I simply typed in =D10 so that Excel knew that whatever number was in cell d10 was to be the exact same in cell b14. I then highlighted the cell and dragged it across the rest of the months and Excel automatically entered £0.00 into the remaining 11 cells because this income was only paid in January at the start of the year.
The next heading was Research grants and like Memberships it was only paid in January. Once I clicked on the cell I entered = and then went onto the costs spreadsheet that was provided by the DIDA WildCare website. I clicked on the research grants price and the formula was ‘=costs!B21’. After I clicked enter the number came up but it wasn’t in currency form so I had to do absolute cell referencing and to do this I clicked on the Number tab and changed the format of the cell to ‘currency’. To ensure that I didn't have this problem recurring I changed all of the cells formats to currency. Once this had changed I highlighted the January cell and dragged it across so that Excel would enter the remaining prices, all being £0.00 of course since research grants are only paid in January.
Adoptions was the next heading and unlike the previous two it was paid monthly as opposed to once a year. To add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the Adoptions price and the formula was now ‘=Costs!B24’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$24’. Once I clicked enter Excel had entered the adoptions price into all 12 cells since the adoptions fee was paid monthly by all members.
For the final Income section the heading was other so if the WildCare Trust got income from any other source then how much would be what goes under this title. However on the costs spreadsheet that the DIDA WildCare provided there was no price for others so presumably they get no source of income from anything other that what they’ve stated so I entered in £0.00 and then highlighted the cell across and Excel automatically entered in the rest of the prices into the spreadsheet.
In order to work out if the WildCare Trust were in Surplus or Deficit, for both the income and outgoings I need to work out the total for each month. Firstly I worked out the total income for January. I entered the formula ‘=SUM’ and then highlight all of the sources of income and their prices in January and dragged it down so the formula was ‘=SUM(B14:B17)’. Once I entered this formula Excel calculated what the total income was. Then I highlighted the rest of the months as they all required the same formula to be entered so once I entered this formula Excel calculated all of the total incomes for each month.
I then had to calculate each of the total income for each of the different sources of income. To do this I had to do the exact same formula so I entered ‘=SUM’ and then highlighted the entire row from B14 to M14 and then the formula was ‘=SUM(B14:M14)’ and once I clicked enter Excel had calculated the total income for the Membership income source. I then highlighted the entire column as all of the cells required the same formula and Excel calculated all of the income prices for each month and each income source.
After I had done the income headings and added them up I had to do the outgoing headings. The first of which was salaries and this outgoing was monthly paid so to add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the Salaries price and the formula was now ‘=Costs!B10’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$10’. Once I clicked enter Excel had entered the salaries price into all 12 cells since the salaries fee was paid monthly by all members.
The second was administration costs and this outgoing was monthly paid so to add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the administration costs price and the formula was now ‘=Costs!B11’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$11’. Once I clicked enter Excel had entered the administration costs price into all 12 cells since the salaries fee was paid monthly by all members.
The third was rent and this outgoing was monthly paid so to add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the rent price and the formula was now ‘=Costs!B12’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$12’. Once I clicked enter Excel had entered the rent price into all 12 cells since the rent fee was paid monthly by all members. The website however, told us that rent was only paid in January, April, July and October so for the other months I simply changed them to £0.00 as no rent was paid in these months.
The fourth was utilities and this outgoing was monthly paid so to add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the utilities price and the formula was now ‘=Costs!B13’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$13’. Once I clicked enter Excel had entered the utilities price into all 12 cells since the utilities fee was paid monthly by all members.
The fifth was food and this outgoing was monthly paid so to add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the food price and the formula was now ‘=Costs!B14’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$14’. Once I clicked enter Excel had entered the food price into all 12 cells since the food fee was paid monthly by all members.
The sixth was veterinary costs and this outgoing was monthly paid so to add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the veterinary costs price and the formula was now ‘=Costs!B15’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$15’. Once I clicked enter Excel had entered the veterinary costs price into all 12 cells since the veterinary costs fee was paid monthly by all members.
The seventh was publicity and this outgoing was monthly paid so to add the prices to the spreadsheet I had to enter = and then go onto the costs spreadsheet, provided by the DIDA WildCare website. I then selected the publicity price and the formula was now ‘=Costs!B16’ Once I clicked enter it copied it into the January cell. However I needed this price in all of the month cells so I had to do 3D referencing so I clicked on the formula just after the exclamation mark and pressed the ‘F4’ button and the new formula was ‘=Costs!$B$16’. Once I clicked enter Excel had entered the publicity price into all 12 cells since the publicity was paid monthly by all members.
For the final outgoing section the heading was other so if the WildCare made any other from any other source then how much would be what goes under this title. However on the costs spreadsheet that the DIDA WildCare provided there was no price for others so presumably they get pay for no other things than what they’ve stated so I entered in £0.00 and then highlighted the cell across and Excel automatically entered in the rest of the prices into the spreadsheet.
In order to work out if the WildCare Trust were in Surplus or Deficit, for both the income and outgoings I need to work out the total for each month. Firstly I worked out the total income for January. I entered the formula ‘=SUM’ and then highlight all of the outgoing prices for January and dragged it down so the formula was ‘=SUM(B20:B27)’. Once I entered this formula Excel calculated what the total outgoings was. Then I highlighted the rest of the months as they all required the same formula to be entered so once I entered this formula Excel calculated all of the total outgoings for each month.
I then had to calculate each of the total outgoings for each of the things that the WildCare Trust pay for. To do this I had to do the exact same formula so I entered ‘=SUM’ and then highlighted the entire row from B20 to M24 and then the formula was ‘=SUM(B24:M24)’ and once I clicked enter Excel had calculated the total outgoings for the salaries outgoing row. I then highlighted the entire column as all of the cells required the same formula and Excel calculated all of the outgoing prices for each month and each outgoing source.
‘Net cash flow is the movement of money into or out of a business, project, or financial product. It is usually measured during a specified, finite period of time.’-Wiki After working out the WildCare Trust’s total income and outgoings I then had to work out the Net Cash Flow. To do this I simply had to type in ‘=‘ and then click on the total income cell and take this away from the total outgoings so the new formula was ‘=B18-B28’. Excel then calculated what the net cash flow was and I then dragged the cell across so that Excel knew that I wanted to apply the same formula to the remaining 11 month cells.
‘Surplus is an amount of something left over when requirements have been met; an excess of production or supply over demand.’-Wiki ‘Deficit an excess of expenditure or liabilities over income or assets in a given period.’-Wiki I then wanted to see if WildCare was in surplus or deficit for each month and to do this I simply had to use an IF statement. I had already worked out the net cash flow so all I had to type is was ‘=IF(B18>B28,”Surplus”,”Deficit”). This merely means that if cell B18 (total income) has a higher price than cell B28 (total outgoings) then there is a surplus but if the opposite occurs then there is a deficit. Excel then automatically knew whether the prices totalled up to create either a surplus or deficit. I highlighted the remaining 11 cells and they all changed to deficit as the net cash flow was in negative figures. I also done conditional formatting in these cells so that if there was a surplus it would be easy to tell as the cell would go green and if there was a deficit the cell would automatically change to a red colour…
To do conditional formatting I clicked on the ‘conditional formatting’ tab and then I went on to ‘Highlight Cells Rules’, I then clicked on ‘Text that contains’ and I entered ‘Surplus’ into the box so that it would format the cells that I intended it to and I then costumed the colour so that It would change to green for surplus and red for deficit. Once I had entered this I checked the conditional formatting rules manager to see if the rule and been applied and whether it worked or not.
To complete the Cash flow spread sheet and to see specifically how much the charity was in surplus or deficit I had to calculate what the opening and closing balance was for each month. To do this I simply had to do a short formula. For January the WildCare Trust started off with an opening balance of £0.00. So I simply entered in 0 and then to work out the closing cash balance I had to do the formula ‘=B29 (Net cash flow) + B31 (opening cash balance). Excel then calculated what the closing cash balance was. For February's opening cash balance I had to type in the closing cash balance of January as that was the opening cash balance of the next month. I then dragged the cells across so that Excel knew that I wanted to apply the same formula to the remaining 11 month cells.
Once I had completed the spreadsheet I had to test it so ensure that it Excel had worked properly and added up all of the amounts correctly. To do this I changed the income of other from £0.00 to £10.00. Using my calculator I checked to see what the new prices in the spreadsheet should be. By changing these amounts I’d expect the total income price to change from £382,350.00 to £382, 360.00, the net cash flow to change from £324,949.00 to £324, 959.00 and the closing cash balance to change to the same price as the net cash flow price leaving the company still in Surplus. I also added up the outgoings for April to ensure that Excel had added them up correctly and I obtained the exact same price as Excel so therefore I knew that the values and were all correct.
The WildCare DIDA website then said: So I investigated the membership fees of different charity organisations and looked at what prices they charge their members and I then picked 3 different charities to look at and apply to my own cash flow spreadsheet. The three organisations that I chose were WWF, RSPCA and Marwell Wildlife. I then used the prices of these organisations and applied them into my spreadsheet to try reduce the deficit so that the Wild Care Trust would break-even, considering that that was one of it’s charity aims.
The first charity I looked at was the RSPCA and it clearly stated that a year's membership would cost £10 and £13 if the member lived overseas. This researched helped me to shape what my membership prices would be. I also learnt that this particular charity has a teenage committee which allows teenagers to shape the Young RSPCA pages.
The second charity I researched was the WWF charity and I discovered that they charge a membership fee by as little as £3 and before you actually join up and become a member they give you lots of information on ‘did you know facts’ and they also give you an insight into what kind of work the charity has completed in the past. Not only do they give you all of this information but they also tell you what you will receive by becoming a member.
The third charity that I researched was the Marwell Wildlife charity and just like Wild Care and the other charities it also allows you to adopt animals and become a member. However, the membership is annual and the costs of the memberships are relatively high with the adult membership price being £56 and the child membership card being £36.
Proposal 1: Once I had researched the different membership prices I started to design 3 proposals to try and reduce the deficit and breakeven and have an overall Net Cash flow of £0. For my first proposal I simply entered in the same prices as the RSPCA for the teens and then I deducted £2 off of the teens price for the Kids price so it was £8. This did reduce the deficit but a lot however the WildCare Trust was still in thousands of pounds of deficit so I knew that these prices had to be changed so that deficit was reduced further. Therefore I created a second proposal.
Proposal 2- Goal Seek: For my second proposal I decided to use ‘Goal Seek’ the aim of this type of cell referencing was to try and get the net cash flow at the end of the year closets to the value of £0 as possible since the organisation was a charity and its aim was to break even. To do this I had to go onto goal seek and then select the net cash flow firstly. Once I had done this I set the value to 0 and changed the teens membership price cell to allow goal seek to work and then Excel told me that goal seek had found a solution.
Proposal 2-Goal Seek: After I had completed ‘Goal Seek’ Excel told me that goal seek had found a solution to creating the net cash value as £0 by changing the WildCare Teens membership price. However, it had changed the price to £35.24. The first of two problems with this price is that the .24p is rather inconvenient and it is not the norm when it comes to membership prices, no organisation would charge their members .24p. Secondly, the fact that the WildCare Teen Membership price is higher than the Platinum price just isn’t reasonable and once again you just simply can not charge teens higher than your top membership price. Due to these inconveniences I created a third and final proposal.
Final Proposal: For my final proposal I simply added in my own, reasonable prices to get the net cash flow to a reasonable price and preferably not below 0. However, I was unable to do that as I managed create a deficit of -£104. however, this was a huge improvement from the thousands of pounds of deficit they were originally in. I tried to use goal seek but once again it continuously created unreasonable and inconvenient prices so I found these prices to be the best and most realistic for members to pay. Therefore this was my final proposal.
