+ Reply to Thread
Results 1 to 5 of 5

financial model

  1. 12-23-2008, 08:12 PM #1
    cassandra23 is offline
    Registered User
    Join Date
    12-23-2008
    Location
    oakville, Canada
    Posts
    7

    financial model

    Hi everyone - I'm stuck trying to write a formula for my financial model. If anyone can take a stab at a solution, it would be much appreciated.

    I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

    Details:
    Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

    - if under $25K, recognize in next month (month N+ 1)
    - $25K-100K, recognize in two equal parts in months N + 1 and N + 2
    - over $100K, recognize in three equal parts over 3 months
    N + 1, N + 2, N + 3 ...

    thanks
    Last edited by cassandra23; 12-24-2008 at 07:28 PM. Reason: i re-posted under a more accurate thread title. "IF" statement formula
    Reply With QuoteRegister To Reply
  2. 12-23-2008, 08:52 PM #2
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Maybe like this:
    Please Login or Register  to view this content.
    Edit: cassandra, please take a few minutes to read the forum rules about thread titles before posting again.
    Last edited by shg; 12-23-2008 at 08:58 PM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 12-23-2008, 11:03 PM #3
    cassandra23 is offline
    Registered User
    Join Date
    12-23-2008
    Location
    oakville, Canada
    Posts
    7
    shg, thanks for the prompt reply.

    Re: your code: it works, but only for deals less than $25,000, because the 25k is paid in full in month 1.

    But for deals in excess of $100,000, $33,333 must be distributed evenly over 3 months. So $33,333 is distributed into Month 1. which is good. But how can I ensure revenue is also distributed into cells C3 (month 2 for $33,333) and D3 (month 3 the final $33,333) ?

    and sorry for the double post. I was unable to change the thread title, in order to make it more descriptive, so i created a new thread.
    Reply With QuoteRegister To Reply
  4. 12-24-2008, 12:13 AM #4
    cassandra23 is offline
    Registered User
    Join Date
    12-23-2008
    Location
    oakville, Canada
    Posts
    7
    shg - disregard last post. I tweaked your formula and now it works beautifully.

    Code:
    H$80 * LOOKUP(H$80, {0,25000,100000}, {1,0.5,0.333333333333333})+G$80 * LOOKUP(G$80, {0,25000,100000}, {0,0.5,0.333333333333333})+F$80 * LOOKUP(F$80, {0,25000,100000}, {0,0,0.333333333333333})

    thanks!
    Reply With QuoteRegister To Reply
  5. 12-24-2008, 12:20 AM #5
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    If you're a happy camper, would you please mark the thread as Solved?

    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the words No prefix.
    Change to Solved
    Click Save
    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)

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