Close the skill gap with the Financial Modeling & Valuation Analyst (FMVA)™ Certification >> Enroll today and save!

Dynamic Dates, Sum, Average and Scenarios

Use advanced Excel formulas to perform powerful and dynamic financial analysis

Dynamic Dates, Sum, Average and Scenarios

For financial analysts in investment banking, equity research, FP&A and corporate development, it is advantageous to learn advanced Excel skills because it makes you stand out from your competitors. In this article, we will go through some of the best industry practices that will help you dramatically speed up financing modeling and perform a powerful and dynamic analysis. This will also help you increase the use of keyboard over the mouse to save time in financial analysis in the long run. Lean how to create dynamic dates, sums, averages, and scenarios in Excel.

 

Dynamic dates and time periods with IF statements

When building financial models, you always start with setting the start and end dates.  Instead of inserting the dates manually, you can use the IF statements to make it dynamic.

 

1. Set the start and end date for your model. Change font to the blue color to show that they are hard-coded numbers.

 

Dynamic Dates - Example 1

 

2. Calculate the number of periods using formula =YEAR(C2)-YEAR(C1)+1. You need to add one period because the beginning and ending periods are included in the count.

 

Dynamic Dates - Example 1A

 

 

3. Set up the dynamic dates across row 5 using formulas. In cell B5, insert start date =YEAR($C$1). Press F4 to anchor the cell reference.

 

Dynamic Dates - Example 1b

 

4. In cell B6, type formula =IF(B5<YEAR($C$2),B5+1,””) to insert dynamic date. Hold SHIFT and right arrow then press CTRL + R to fill down the cells. It will fill the cells to the right until the end date.

 

Dynamic Dates - Example 1c

 

5. You can now easily modify the dates without manually going through each of the cells. For example, if you change the end date to 12/31/2030, the cells will automatically fill to the right until it reaches 2030.

 

Dynamic Dates - Example 1d

 

6. Press SHIFT + CTRL + right arrow to select the entire date section and press CTRL + 1 to open up the Format Cells window. Change fill to dark blue and font to white color, then press OK.  Press CTRL + B to bold font and increase the size to 12.  You now have a header where all your financial analysis flows below.

 

Dynamic Dates - Example 1e

 

Dynamic totals and averages with the OFFSET function

We will now input some data and start building the model.  You can copy data from financial statements and paste the value by pressing ALT + E + S and V for values.  Use ALT + H + F + C to change font to blue color.  In this set of data, the start year is 2017 and the end year is 2021.

 

7. Press ALT + I + C twice to insert two columns to the left of 2017 data. We will use them as total and average columns.  At the top, insert Total and Average in cell G1 and G2, and put 3 beside both cells.  These are the number of periods we would like to include in the calculations.  For models with longer periods of historic data, you may want to use only a few periods to calculate your total and average.

 

Dynamic Totals - Example 1

 

8. Now we’ll use the OFFSET function to set up the total column. In cell B8, type =SUM(D8:OFFSET(D8,0,$H$1-1)).  The OFFSET function allows us to select range of data with reference to the amount of period we would like to include which we input in cell H1.  We need to subtract 1 from the number of columns because we only want to include two more columns (i.e. column D to F).  You can change the hard-coded number of total periods to change the total value.

 

Dynamic Totals - SUM

 

9. The formula for the average is similar, except the cell reference used in the number of periods is H2: =AVERAGE(D8:OFFSET(D8,0,$H$2-1)). Press SHFIT + down arrow and CTRL + D to fill down the cells.

 

Dynamic Totals - AVERAGE

 

Scenarios with the VLOOKUP and CHOOSE functions

Next, we will set up some scenarios.  In cell B1 to B3, set three scenario names: upside, target and downside cases.

 

10. In cell A1, press ALT + A + V + V (A for Data, V for Data Validation, and V for Validation). We will create a drop-down list for the scenarios. Choose List and select cell B1 to B3 as the source.  Press OK.

 

VLOOKUP Scenario 1

 

11. Press CTRL + 1 to format the drop-down list so we can easily locate it. Format the font to bold and change cell color.

 

VLOOKUP Scenario 1a

 

12. Number the three scenario cases in cells C1 to C3. In cell A2, use the VLOOKUP function to match the scenario name in the drop-down list to the case number. Enter the formula =VLOOKUP(A1,B1:C3,2,FALSE).

 

VLOOKUP Scenario 1b

 

13. Now, we’ll insert some rows below revenue to show the three scenarios. At cell A9, press ALT + I + R four times to insert four new rows. Name them Upside Revenue, Target Revenue, and Downside Revenue.  We will input some numbers for each of the scenarios in cells E8 to I10.

 

VLOOKUP Scenario 1c

 

14. Row 13 is our live revenue numbers. In cell E13, use the CHOOSE function =CHOOSE($A$2,E9,E10,E11).  The function allows us to create a dynamic revenue line which chooses the right option according to the scenario selected. Change font to black, press SHIFT + right arrow then CTRL + R to copy formulas to the right.

 

CHOOSE Function - Example 1

 

15. Fill out the cells B10 to C11 by pressing CTRL + D. Copy and paste formulas from cell B11 and C11 to B13 and C13.

 

CHOOSE Function - Example 1a

 

16. Now, we want to link the total and average label to the number of periods indicated in cell I1 and I2. In cell B6, enter =I1&”-Yr Total” so the total label displays “3-Yr Total” where the number is dynamic. For the average label, enter =I2&”-Yr Avg”.

 

CHOOSE Function - Example 1b

 

17. Finally, do a bit of formatting to the model. Change gross profits, earnings before tax and net income to formulas so they are all linked up. Your final model might look something like this:

 

CHOOSE Function - Example 1c

 

Summary of key formulas

  • IF formula for building a dynamic date: =IF(“previous year”<YEAR(“end date”), “previous year”+1, “”) 
  • OFFSET formula for calculating dynamic total: =SUM(“first data cell” : OFFSET(“first data cell”, 0, “number of periods”-1)) 
  • OFFSET formula for calculating dynamic average: =AVERAGE(“first data cell” : OFFSET(“first data cell”, 0, “number of periods”-1)) 
  • VLOOKUP formula for looking up scenarios: =VLOOKUP(“value to look up”, “table range”, “column to look up”, FALSE) 
  • CHOOSE formula for choosing an option based on the scenario: =CHOOSE(“case number”, “option 1”, “option 2”, “option 3”)

 

Other Resources

CFI is the official provider of the global Financial Modeling & Valuation Analyst (FMVA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional resources below will be useful:

  • Advanced Excel Formulas
  • Financial Modeling Best Practices
  • List of Excel Functions
  • Excel for Financial Modeling

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!