+ Reply to Thread
Results 1 to 6 of 6

Confused on which formula to use for long term budgeting

  1. #1
    Registered User
    Join Date
    01-13-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    14

    Confused on which formula to use for long term budgeting

    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
    Attached Files Attached Files
    Last edited by hotzpacho; 01-13-2015 at 12:57 PM.

  2. #2
    Registered User
    Join Date
    01-13-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    14

    Re: Confused on which formula to use for long term budgeting

    anyone have anything to offer?

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Confused on which formula to use for long term budgeting

    Is this what is required.
    In G7, then drag down
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-13-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    14

    Re: Confused on which formula to use for long term budgeting

    Thank you, Columns C-I seem to be perfect. I would assume i wouldn't need K-S?

  5. #5
    Registered User
    Join Date
    01-13-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    14

    Re: Confused on which formula to use for long term budgeting

    is there anyway to take the E Column (Lines) and have it show X amount of Lines for each Row and allow me the ability to adjust any specific row at any time and thus having every consecutive row follow the same?

    Example: Lets Say cell E6 has the value 8. I want every Cell from E7:E70 to have the same value. Now if in year 2020 i decide to reduce from 8 lines to 6 lines and insert in cell E11 the value of 6 I want every consecutive cell from E12:E70 to follow with the value of 6. Now continuing with that, if at year 2030 or cell E21 i want to add a 7th line i would insert the value of 7 in cell E21 and each cell from E22:E70 would automatically show 7 lines in each cell.

    I want the ability to increase and decrease the "lines" column at any point in time while having every consecutive year indicating the same value until i change the "lines" column.

    Is this possible?

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Confused on which formula to use for long term budgeting

    In E7 use. then drag down.
    =E6
    Change values in the desired row manually.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Filling in a long-term table from a single, changing source
    By Drayde in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2014, 12:38 PM
  2. Calculating short term and long term moving average in VBA
    By ixthus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-10-2013, 02:40 AM
  3. Dealing with a long-term measurement data
    By Excelmania013 in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 08-06-2013, 04:54 AM
  4. Long term lurker!!
    By Craig.Selby in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-10-2012, 09:37 PM
  5. [SOLVED] Long term: Add Symbolic Computation and new math Plot Functions
    By Robert Bigdowski in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2006, 04:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1