Forum Discussion

Antoine74's avatar
Antoine74
Copper Contributor
May 11, 2023

Disbursement scenarios

Dear Community, 

I am asking for help : 

- I am builing a business plan (BP) ;

- an important feature of this BP is a series of 5 investments that will cause cash outflows ;

- for each of these investments, outflows are spread over many months; 

- I would like to fine tune the investment schedule : for example, measure the impact of investing in June 2024 instead of January 2024, or of accelerating all 5 investments, etc.

--> What Excel function could I use to avoid retyping data for each test I am making ?

Thanks a lot in advance, 

Antoine

BI & Data Analysis
excel
Formulas and Functions

4 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 11, 2023

    Antoine74 

    I haven't got as far as NikolinoDE when it comes to scenario management.  You will clear need some steering data to set up your problem.  I would start by setting up a timeline and a Lambda function that distributes a given outflow along the timeline.

    Worksheet formula
= Outflowλ(start, occurrences, periodicity, amount, periodCounter#)

where the function 'Outflowλ' is defined by

= LAMBDA(start,occurrences,periodicity,amount,periodCounter,
    LET(
      outflow, SEQUENCE(occurrences, , start, periodicity),
      BYCOL(IF(periodCounter=outflow,amount), SUMλ)
    )
  )
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 11, 2023

    Antoine74 

    To avoid retyping data for each scenario you want to test, you can use the Data Table feature in Excel.

    Here's how you can set it up:

    1. Create a table that contains the inputs you want to test. For example, you could have a column for the investment amount, a column for the start date, and a column for the number of months over which the investment will be spread.
    2. Set up your cash outflow calculations in a separate table or range of cells. Use cell references to refer to the input values you want to test. For example, if your investment amount is in cell A1, your start date is in cell B1, and your investment period is in cell C1, you might have a formula like this: =-A1/C1 in cell D1, which calculates the monthly cash outflow.
    3. Select the range of cells that contains your input values (including the headers) and go to the "Data" tab in the Excel ribbon. Click on "What-If Analysis" and then select "Data Table".
    4. In the "Data Table" dialog box, enter the cell reference for the output of your cash outflow calculations (e.g. D1) in the "Row Input Cell" box. This tells Excel which cell to update with the output values for each scenario.
    5. Enter the cell references for the input values you want to test (e.g. A1:C1) in the "Column Input Cell" box. This tells Excel which cells to vary in order to test different scenarios.
    6. Click "OK" and Excel will generate a table that shows the output values for each combination of input values you specified.

     

    By using a data table, you can quickly test different scenarios by changing just a few input values, without having to manually update all of your cash outflow calculations.

     

    7.       Or…Using an IF statement without repeating a long formula (LET)

    Let's assume you have the following input values:

    Investment amount: $100,000

    Start date: June 1, 2024

    Investment period: 24 months

    You can define variables for these values using the LET function, like this:

    =LET(
    investment_amount, 100000,
    start_date, DATE(2024, 6, 1),
    investment_period, 24,
    monthly_outflow, -investment_amount/investment_period,
    monthly_dates, EOMONTH(start_date, 0)+1+SEQUENCE(investment_period,1,0,1),
    cash_outflow, IF(monthly_dates<=TODAY(), monthly_outflow, 0),
    cash_outflow
)

    Hope I could help you with these information / link.

     

Resources