Membership Fees Spreadsheet By Tania Velez Perez
Zosia Feedback:I think that you have formatted your cells well, as they have a bold border and everything is sectioned well. From your research you can check if there are any other costs you could use to breakeven which is closer to zero than £496. I think you could maybe change the positioning of the memberships box and place it above. Bea Feedback: Tania you have shown that you have sued all the right numbers in your outgoings and incomings and everything is aligned and in place. You could also include more excel techniques such as IF statements and a net cash flow which you have labelled in this case a profit/loss to show whether you have a surplus or deficit and use conditional formatting to show the colour. This will all improve the efficiency of your spread sheet including more formulae for things to change automatically and demonstrate your skills ion excel.
Example and Skills • I used 3D referencing to get the values from the another spreadsheet. • I used cell formatting to make the cells coloured, with black borders, to highlight each section of the spreadsheet and to change the numbers to cost. • I used absolute cell referencing to use the same values again without having to re type them by suing the $. • I used conditional formatting which means the colour I changed automatically depending on the result to show deficit or surplus. • I used IF statement to compare the outgoing s and income by having the word surplus or deficit appear .
Formulae Sheet • I used cell formatting to make the cells coloured, with black, bold, borders to highlight the different sections of the spreadsheet. • 3D referencing using values from costs sheet ( the other spreadsheet) • I used the absolute cell referencing when wanting to keep the same values without having to type them in again using the $ • I used the IF statement used to compare the income and outgoings . • I used Conditional formatting to colour the cells according to whether they were surplus or deficit without me having to change the colour of the cell.
Testing 1 First I must test my spread sheet to see it works before I can propose any values for the membership fees. This test will see if my formulae work in all the cells such as the IF statements and the total net cash flow changes. I will change all my memberships to zero as well as all the outgoings and incomings. The net cash flow shows to be £0 which has automatically changed from the values I have changed from the membership costs, outgoings and incomings. The conditional formatting and IF statements also show they are working by the words deficit from the values of 0 as the IF statements have been adjusted to anything above 0 a surplus . This shows my formulae for my spreadsheet is reliable and correct so I can get the expected answers
Testing 2 In this test I am also checking whether all my cells function from the formulae they have and the automatic changes they should be making. I will change the membership cost to a total of only £200 a year from the silver membership and I will leave the rest of the incomings and outgoings as zero. The net cash flow shows to be a deficit of -£60.00 which has automatically changed from the values I have changed from the membership costs. The conditional formatting and IF statements also show they are working by the words deficit from the negative values. This shows my formulae for my spreadsheet is reliable and correct so I can get the expected answers
Proposal 0 This proposal was 0 because there was no cost for Wildcare teens or kids. However the platinum, gold and silver memberships all had costs each of a £5 difference and platinum at £30 the maximum cost. The aim of the membership fees is to break even which means achieve a net cash flow of 0. The company doesn’t want to have a cash flow of deficit or surplus however the Net cash flow shows a deficit of -£64,854.00 and you would rather a surplus than a deficit so this proposal doesn’t work.
Proposal 1 This is proposal 1 and I have kept the same costs for the memberships of Platinum, Gold and Silver from proposal zero but now I have made the cost of Teens £8 and £4 for kids. This proposal does not work as there is a deficit of -£46,854.00 which means I must change the costs to breakeven.
Proposal 2 I have changed the costs of all the memberships excluding Platinum. I used goal seek to help get a net cash flow of 0 by changing the value of Platinum which became £33.48 This proposal shows the membership fess that make breakeven however £33.48 is not suitable as it is not something someone would normally pay and that makes the cost difficult and manageable prices.
Proposal 3 From proposal 2 I rounded the membership of platinum to £33.50 and kept the same costs for the other memberships the same from proposal 2. This is hopefully the closest numbers I can get to breakeven with suitable costs. This proposal shows the membership fess that make a surplus of £46 which is the closest I could get to breakeven with appropriate costs that are seen in other membership organisations fees.
Why choose Proposal 3? The third proposal shows that I put the price of platinum £ 33.50 which is a lot cheaper than examples such as platinum £78 for the organisation Born Free which means it is affordable for people as this organisation Born Free is also about the wildlife. I made gold cost £28 and silver cost £23.50 which is cheaper compared to Born Free organisation which has gold at the price of £39 and silver at £30. There are examples from the Blackpool zoo where the membership costs £33.50 which shows that having a membership at something 50p is also used by other organisations. The teen cost which I proposed at £7 is a lot of cheaper than other organisations compared to Great Ormond Street Hospital which costs £12 and the National Trust which cost £25. the kids memberships I proposed is also cheaper at £3.50. I think from looking at other membership fees my prices are affordable and will attracts more people just wildcare wants. Show hyperlinks to how I came up with this
Feedback Zosia: You have shown that you have made changes to your spreadsheet from your feedback. You have added colour to your spreadsheet so specific boxes stand out and also used conditional formatting on the added boxes for net cash flow. Bea: Well Done Tania because you have shown the different proposals you tried out before you came to your last choice as well as testing your spreadsheet to make sure you have the right formulae to make your spreadsheet work properly. You have also added a net cash flow and IF statements.