Hello, My name is Liam
Thank you in advance to anyone who takes the time from their busy lives to assist me here today; it's greatly appreciated.
I'm trying to create an excel document that will be used for budgeting purposes (i am aware that future planning is not always precise, but that is not my problem at this point in time). This specific document is focused on cell phone service for a family over many years (4 adults and 4 children, but the children are not all born). The family plan starts out budgeting for 8 lines at a total of $300 per month for 65 years. It's easy to figure out yearly totals and long grand totals (simple addition). Where I'm running into problems is that in reality i won't be starting out with 8 lines, but rather 4 (4 adults). In 10 years i will be adding a 5th line for today's cost of $20.00. Throughout the entire life term of this budget i will be adding lines at different times until i get to a total of 8 at one time, but then after a period of time i will begin to decrease the lines back down to 4. Each additional line beyond 4 will cost $20.00 per.
An easy way to understand this is to pretend that I start out with 4 adults and 1 baby in a household in 2015 (only need 4 lines, because the baby is...well a baby and doesn't need a cell phone, lol). The child is 1 year old and i want to supply a cell phone to the child on his/her 10th birthday which will happen in 2025 (2025 = 5 lines). Throughout that decade other children will be born at different times, each of which will receive a cell phone on his/her 10th birthday as well (dates unknown).
However that $20.00 per line as mentioned above is in today's cost. If in 10 years i decide to add a 5th line the cost may have increased beyond $20.00 due to inflation. One can't forget to factor in the tax rate either, which in this case is currently 16%. I want the option to change the tax rate at any time throughout the years. I am also only budgeting for inflation rises every 5, 7, or 10 years ( I have not decided yet), so this means that every 5 years inflation rates would tact on 3%.
Example: inflation % = 3%: 2015 = 300 per month. 2020 = (300* Inflation %) + 300 = 309. 2025 = (309*3%)+309 = 318.27. I want the freedom to plug in an inflation rate at any time and have the following rows in excel adjust correctly.
My main goal is to know how much (according to the ability of this spread sheet) it will cost me to supply cell phone service to X amount of people over X amount of time and at any given time. I'd like to know the monthly and yearly prices for each year. Every line that is added or subtracted will be done in the beginning of the year, so not to complicate things any further.
Example: If in 2015 i start out with 4 lines and i will need to add one the addition will occur in 2016, not midway through 2015.
It's quite easy to say 8 lines at $300 per month * a tax rate of 16% = $348.12 per month, which equals $4177.44 per yer. 65 years without inflation or changes in taxes ~ $272,000, but the reality is it will be far less than that because i won't be carrying 8 lines every year for 65 years and inflation will occur along with tax rates that will fluctuate over time.
I'm attaching the excel document below, please feel free to adjust anything and everything you believe needs changed. I am open to all suggestions and options if you have anything to offer. Please feel free to ask me any questions needed to solve my dilemma. Thank you so very much for helping me with this situation! May each of you have a blessed afternoon.
Liam
Bookmarks