+ Reply to Thread
Results 1 to 13 of 13

Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

  1. 03-14-2024, 03:54 AM #1
    PolyglotLisa
    PolyglotLisa is offline
    Registered User
    Join Date
    03-14-2024
    Location
    Minnesota, USA
    MS-Off Ver
    365
    Posts
    6

    Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Hello,

    I'm working on a financial model. I'm using a model set up tab where I'm setting up information in tables and in my Income Statement tab (for example) I'm using a series of Dynamic Arrays in order to make the model fungible. Examples:
    =SEQUENCE(1,sModelTerm,1,1) in order to adjust the number of periods in the model and =DATE(YEAR(sStartDate),MONTH(sStartDate)+N4#,0) to set the first operating year and then the following 5 full years in the model.

    Where I'm running into trouble is when I'm looking at onboarding customers.

    Sample Table.png

    In my table I have an onboarding date. I tried using SUMIFS to match on the Tier Level and the first onboarding date, converted to a month-year format (4-2024 as an example) because I couldn't get between 2 dates to work..

    =SUMIFS(tblCustomers[[#All],[Est Member Volume]],tblCustomers[[#All],[Pricing Tier]],'Income Statement'!$C23,tblCustomers[[#All],[On-Board Month/Year]],'Income Statement'!N$10#)
    This formula sums the membership 1 time in the precise month/year in which the onboarding date matches the N10 value.

    What I really want to happen is for the formula to return the SUM of the membership that matches the Tier and the Date criteria for the 1st month the customer onboards and then every month after that (through their offboarding date), sum up the membership and apply a growth factor.

    I've tried AND() around the onboard/offboard dates. I tried SUMPRODUCT(Criteria*Criteria*Criteria). I haven't even tried to apply the growth rate because I can't get the baseline formula to work.

    Any ideas?
    Attached Files Attached Files
    Last edited by PolyglotLisa; 03-14-2024 at 04:21 AM.
    Reply With QuoteRegister To Reply
  2. 03-14-2024, 03:54 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,898

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-14-2024, 04:21 AM #3
    PolyglotLisa
    PolyglotLisa is offline
    Registered User
    Join Date
    03-14-2024
    Location
    Minnesota, USA
    MS-Off Ver
    365
    Posts
    6

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    I added a sample. Thanks for the note.
    Reply With QuoteRegister To Reply
  4. 03-17-2024, 08:53 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Although this isn't a dynamic array it yields the expected results with the exception of cells BX19:BZ19
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    An issue that I foresee is a case where there are multiple members in the same tier and not all on-board dates are the same.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 03-17-2024, 11:31 PM #5
    PolyglotLisa
    PolyglotLisa is offline
    Registered User
    Join Date
    03-14-2024
    Location
    Minnesota, USA
    MS-Off Ver
    365
    Posts
    6

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Thank you very much! I am going to try this in my model and see how it goes. I very much appreciate your help.
    Reply With QuoteRegister To Reply
  6. 03-17-2024, 11:32 PM #6
    PolyglotLisa
    PolyglotLisa is offline
    Registered User
    Join Date
    03-14-2024
    Location
    Minnesota, USA
    MS-Off Ver
    365
    Posts
    6

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Ah... my thank you didn't go directly to you. Sorry about that! Thank you very much!
    Reply With QuoteRegister To Reply
  7. 03-18-2024, 03:56 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,898

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Whom are you addressing in post #6? Your thanks in post #5 were addressing the post immediately above (post #4), weren't they? Is there / has there been some confusion here?

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Reply With QuoteRegister To Reply
  8. 03-18-2024, 06:24 AM #8
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,060

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    If you need a spill formula for "First Day of Month" in cell G11, you could use the formula:
    =EOMONTH(DATE(YEAR(sStartDate),MONTH(sStartDate)+G4#,0),-1)+1

    If you are asking for is annual net growth, then modifying JeteMc's formula and using dates in row 5 instead of row 11, with spilling:
    G19=IFERROR(SUMIFS(tblCustomers[Est Member Volume],tblCustomers[Pricing Tier],$C19,tblCustomers[On-Board Date],"<="&G$5#,tblCustomers[Off-Board Date],">="&G$5#)*((1+AVERAGEIFS('Model Set Up'!$G$12:$G$16,'Model Set Up'!$B$12:$B$16,$C19))^(DATEDIF(MINIFS(tblCustomers[On-Board Date],tblCustomers[Pricing Tier],$C19),G$5#,"y"))),"")
    Reply With QuoteRegister To Reply
  9. 03-18-2024, 06:28 AM #9
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,060

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Revised formula:
    =IFERROR(SUMIFS(tblCustomers[Est Member Volume],tblCustomers[Pricing Tier],$C19,tblCustomers[On-Board Date],"<="&G$5#,tblCustomers[Off-Board Date],">="&G$5#)*((1+AVERAGEIFS(tblCustomers[Net Growth],tblCustomers[Pricing Tier],$C19))^(DATEDIF(MINIFS(tblCustomers[On-Board Date],tblCustomers[Pricing Tier],$C19),G$5#,"y"))),"")
    Reply With QuoteRegister To Reply
  10. 03-18-2024, 06:35 AM #10
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,060

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    One more revision required to take care of Tier 1 Members with 2 different Off-Board Dates:
    =IFERROR(SUMIFS(tblCustomers[Est Member Volume],tblCustomers[Pricing Tier],$C19,tblCustomers[On-Board Date],"<="&G$5#,tblCustomers[Off-Board Date],">="&G$5#)*((1+AVERAGEIFS(tblCustomers[Net Growth],tblCustomers[Pricing Tier],$C19,tblCustomers[On-Board Date],"<="&G$5#,tblCustomers[Off-Board Date],">="&G$5#))^(DATEDIF(MINIFS(tblCustomers[On-Board Date],tblCustomers[Pricing Tier],$C19),G$5#,"y"))),"")
    Reply With QuoteRegister To Reply
  11. 03-18-2024, 11:06 AM #11
    PolyglotLisa
    PolyglotLisa is offline
    Registered User
    Join Date
    03-14-2024
    Location
    Minnesota, USA
    MS-Off Ver
    365
    Posts
    6

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Quote Originally Posted by AliGW View Post
    Whom are you addressing in post #6? Your thanks in post #5 were addressing the post immediately above (post #4), weren't they? Is there / has there been some confusion here?

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    I'm still learning how this forum works and I couldn't find a way to delete my second post. Thanks.
    Reply With QuoteRegister To Reply
  12. 03-18-2024, 11:07 AM #12
    PolyglotLisa
    PolyglotLisa is offline
    Registered User
    Join Date
    03-14-2024
    Location
    Minnesota, USA
    MS-Off Ver
    365
    Posts
    6

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Quote Originally Posted by josephteh View Post
    One more revision required to take care of Tier 1 Members with 2 different Off-Board Dates:
    =IFERROR(SUMIFS(tblCustomers[Est Member Volume],tblCustomers[Pricing Tier],$C19,tblCustomers[On-Board Date],"<="&G$5#,tblCustomers[Off-Board Date],">="&G$5#)*((1+AVERAGEIFS(tblCustomers[Net Growth],tblCustomers[Pricing Tier],$C19,tblCustomers[On-Board Date],"<="&G$5#,tblCustomers[Off-Board Date],">="&G$5#))^(DATEDIF(MINIFS(tblCustomers[On-Board Date],tblCustomers[Pricing Tier],$C19),G$5#,"y"))),"")
    Very helpful - thank you very much!
    Reply With QuoteRegister To Reply
  13. 03-18-2024, 11:24 AM #13
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,060

    Re: Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting

    Thanks for the Rep! Credit to JeteMc for coming up with the formula.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. financial functions / formulas to reduce costs on financial credit instruments
    By vba_php in forum Excel Formulas & Functions
    Replies: 32
    Last Post: 11-18-2020, 03:53 PM
  2. Financial Modeling Help Needed
    By UrbanFox88 in forum Excel General
    Replies: 1
    Last Post: 10-16-2019, 11:41 AM
  3. Financial Modeling Tips
    By Curious2 in forum Excel General
    Replies: 2
    Last Post: 02-25-2015, 05:23 PM
  4. Optimization modeling on financial forecasting
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 01-22-2015, 01:59 AM
  5. Business Plans, Forecasting, Financial Modeling, Visual Analysis Tools, Decision Support
    By jdstudebaker in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-23-2012, 10:34 AM
  6. Financial modeling help for internship
    By decafdave in forum Excel General
    Replies: 3
    Last Post: 06-24-2009, 06:17 AM
  7. Excel Solver background in forecasting modeling
    By RL in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2005, 06:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1