Thanks for reading. I am in the process of modeling financials for 50+projects. I am stumped on my total financial roll-out. I have numerous tabs highlighting different subjects in the project, but I will only speak of the ones I believe will be important for this question. Here are some critical information to know:
I know its a combination of several formulas, I just have yet to find the right combination. I am over thinking it at this point and need your help. PLEASE HELP! I would greatly appreciate it.
- Overall
- This is an indicative model showing the financial potential of 50+ projects.
- In reality, all the projects will be unique, but we in the executive team has found an average in all data points - variables - needed in the project.
- This is an indicative model showing the financial potential of 50+ projects.
- Project Assumption worksheet
- All the calculations, iterative variables
- I have two Phases of projects lined up:
- X amount of projects will be 100% equity
- Y amount of projects will be split between equity and debt.
- This worksheet includes the amount of projects per phase
- Average project buildout duration is listed on this worksheet
- I have two Phases of projects lined up:
- All the calculations, iterative variables
- Project Load Demand worksheet
- The load growth parameters per phase that carry over some variables from the assumptions page.
- Project Financial Assumptions worksheet
- An input page specific to financial data.
- Important on this worksheet is the variables for the drawdown of funds from the CapEx per project phase per task per month over the buildout duration of the project (each month is different).
- Within each project there are 5 tasks, each taking a different amount of time and inside each of those tasks there is needed a different amount of drawdown funds on a specific date.
- Project Budget Rollout Details worksheet
- Shows in a calendar format the buildout for one project and the amount of funds need on a per month basis.
- Takes the inputed variable data from the Project Financial Assumptions per task and computes it into a month during the buildout.
- Shows in a calendar format the buildout for one project and the amount of funds need on a per month basis.
- Total Project Rollout Timeline worksheet
- This tab shows each project (1-50+) and its commencement date and commissioning date. We will not be commencing each project on day 1, instead we will roll them out in groups, scaling up over time.
- Ex:
- Projects 1-3
- Commencement date = 1/1/2017
- Commissioning date = EDATE(start date, buildout duration)
- Projects 4-7
- Commencement date = 4/1/2017
- Commissioning date = EDATE(start date, buildout duration)
- Projects 8-14
- Commencement date = 8/1/2017
- Commissioning date = EDATE(start date, buildout duration)
- etc.....
- Projects 1-3
- Ex:
- This worksheet also has a calendar of 6 years (anticipated amount of time to complete buildout of all 50+ projects) broken up by fiscal quarter and month. Each month has a date (computed by EOMONTH).
- This tab shows each project (1-50+) and its commencement date and commissioning date. We will not be commencing each project on day 1, instead we will roll them out in groups, scaling up over time.
- Total Budget Rollout worksheet
- This is where I am stumped!!!!
- I have this page current set up in a table:
- Dates in column headings spanning out 6 years
- Project # in rows going down 50+
- QUESTION:
- I want the cells inside this table to formulate according to the variables I have set forth on previous worksheets.
- Look at the Project # and the commencement date
- If the commencement date has occurred in the column heading for that specific project #, then it needs to do.....
- Throw out the amount needed for the drawdown by month through commissioning completion.
I know its a combination of several formulas, I just have yet to find the right combination. I am over thinking it at this point and need your help. PLEASE HELP! I would greatly appreciate it.
