Related Posts
Bowl Leader
Additional Posts in Excel Genius
Anyone know what these badges are?
Would anyone be willing to teach me vlookup?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Honestly, if someone uses any of these metrics to forecast any kind of (corporate) finance metrics, I'll assume that person was either in a hurry or he/she doesn't understand financial modeling.
All of those mentioned are statistic, heuristic, and (somewhat) deterministic metrics to understand past performance, but they (all) implicitly assume that the variable that is being forecasted is not a byproduct of other metrics (in simple terms, it doesn't directly depends on something).
The way most financial companies (like banks, HF, investment teams) do it is identifying the main drivers for revenue (or for any financial metrics tbh), and forecast those metrics (which is mostly done by either reputable 3rd parties, or a sentiment of a given topic*.
By forecasting this (and, hopefully, backtesting), you can understand the relationships and better (more accurately) forecast a given company revenue/financial metrics. Just be aware that some lines of the P&L/ balance sheet/ Cashflow are way easier to forecast vs others (e.g., depreciation should be pretty accurate, COGS can be a pain - depending on the sector).
*Unfortunately, some consulting companies that don't really focus on financial modeling, tend to heavily rely on these inputs (and call it "expert inputs" for "cover our asses" reasons). This results in rather comical situations where they completely miss the forecast ("famous" ones are like McKinsey estimate of cellphones in the US of ~1 MM)
This was great advice.
If you are using those Excel functions to forecast and expect accuracy, the business must be an extremely simplistic endeavor (almost like a commodity), where supply/demand, seasonal fluctuation, and effort-output ratios are almost historically proven like clockwork.
If you are looking for best practice forecasting, you first look at in-hand contract work (contracts that are already in action, generating revenue but not yet fully recognized - this is your "base" for future revenue). Then, consult with your business drivers (BD personnel, Service/Regional Managers, high level PMs, etc.). These folks should be feeding you in-process opportunities with already existing clients and/or potential clients, also providing you with a % of likeliness to materialize based on where the opportunity-to-project workflow stands at that point in time. Leave out anything below 50% likeliness to materialize, and anything greater than 50% but less than 100% gets factored down proportionately to the likeliness % ($1M Opp. @ 75% Likeliness = $750K Forecasted Revenue - then spread these forecasted revenues across the periods in which this work is most likely to materialize based on opportunity and project scope deadlines). These figures should then be reviewed by appropriate high-level financial and operational managers for final sanity & fiscal period allocation checks.
The above-mentioned paragraph is where you can start flexing your Excel muscle honestly. But there is no plug and play formula. You must use a collection of formulas, functions, tables, etc. to reliably pull these data points together and crunch out useful and trustworthy metrics.
Overall, there is no perfect model... and the Excel functions mentioned by OP are really only good for looking backwards at what trends/growth have historically been. But the above summarized procedures I mentioned are all you can do. Anything beyond these processes just is not justifiable and most likely a result of top-down pressure to reach results promised to investors (which does happen lol).