I am looking to create a spreadsheet that does the following: I give it initial revenue, initial fixed expenses, initial growing expenses, growth rate for revenue and growth expenses, number of years, and future value inflation rate.
What I need from it is to calculate total profits or losses adjusting for future value each year. The way you adjust for future value is simple, you just multiply the current value in a given year by the growth rate(let’s say 1.04) to the power of the number of years remaining minus 1.
So, for a 10 year formula, your first year would be
Initial Revenue-Fixed and Growth Expenses=First Year’s P&L (let’s call it F)
We then need to take F and multiply it by the growth rate for Future value, in this case 1.04 to the power of 10-1.
In case I’m confusing, here’s an example.
- Revenue: 105
- Fixed Expenses: 50
- Growth Expenses: 50
-
Year 1 Profits: 5
-
Future Value of Year 1 Profits: 5*1.04^9=7.1166
-
Year 2 Revenue then increases by 1.03(it is a different growth rate for future value and revenue changes). So Year 2 Revenue is 108.15
-
Year 2 Fixed Expenses: 50
-
Year 2 Growth Expenses: 50*1.03=51.5
-
Profits: 6.65
-
Future Value of Profits: 6.65*1.04^8=9.1009
And then we have to do this for 8 more years, obviously with no future value impact in the final year. You can see why doing this by hand is painstaking when you want to run multiple scenarios or look far into the future. Is there any way to automate this process on excel or google sheets?
I tried creating a list of the base variables and trying to find a formula that does all this at once, but everything I could think of required me updating the formula multiple times for each small change.
For example:
- A1. Revenue
- B1. Fixed Expenses
- C1. Growth Expenses
- D1. A1-B1-C1 AKA year 1 profits
My problem is I have no idea how to automate the subsequent years without just manually doing it, like placing A2 as A11.03 and A3 as A21.03. The problem there is, I want to be able to run multiple scenarios, so going down for every year in each scenario will fill up the sheet fast and take forever to fill in.
