A discounted cash flow (**DCF**) is an analysis that values some type of asset over a period of time.

More simply put, it allows someone to understand how much future money is worth in today's terms.

Historically, this type of analysis has been done in traditional spreadsheet tools like Excel. While a reliable tool, financial analysts and accountants often encounter challenges when performing a DCF in Excel:

- Scalability Issues: Large datasets must be rolled up/aggregated to fit within Excel's ~1 million row limit, leading to a loss of crucial data granularity.
- Manual Updates: The process must be repeated periodically (e.g., monthly or quarterly) as new data needs to be incorporated into the original model, making it time-consuming.
- Lack of Governance: Excel lacks built-in governance features, making it difficult to prevent mistakes and ensure accuracy both proactively and retroactively.

**That is where Sigma comes in.**

With a familiar spreadsheet interface, Excel power users can seamlessly transition to Sigma and recreate their work in an intuitive format.

Our cloud-based architecture offers:

- Performance and Scalability: Handle datasets well beyond Excel's limits, leveraging the full compute power of your cloud data warehouse, maintaining crucial data granularity for more detailed insights.
- Automated Real-time Updates: Your DCF analysis automatically updates as new data flows into your cloud data warehouse, ensuring timely and accurate financial modeling.
- Enhanced Governance and Security: Sigma inherits all the governance features from your cloud data warehouse, including audit trails and version history, ensuring data accuracy and reducing risks associated with manual errors.

This QuickStart will provides an example of DCF, based on Sigma provided sample data, so users can build on their own.

Sigma users who are in a finance role or have interest in a discounted cash flow analysis.

- A computer with a current browser. It does not matter which browser you want to use.
- Access to your Sigma environment.
- Some familiarity with Sigma is assumed. Not all steps will be shown as the basics are assumed to be understood.

We will be using sample data provided by Sigma, which is included with all trial and production accounts.

Log into Sigma and Create a new workbook.

Click the + and select TABLE; search for hands and select the `PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA`

table:

For this table, we have data from 2020 through 2024.

We want to create part of a DCF for each year, for each of the store regions.

First, we will group by `Store Region`

and then create a second grouping on `Date`

:

Set the formula for `Date`

to:

```
DateTrunc("year", [Date])
```

This gives us a row for each `Store Region`

and `Year`

:

Filter on `Year of Date`

for the last three years only:

Add one more column as a `CALCULATION`

in the `Year of Date`

, setting its formula to:

```
Rank([Year of Date], "desc")
```

This ensures that the most recent year is always numbered as `1`

:

To start, we'll focus on three key components of the calculation:

- Carry Value (CV): represents the initial carrying value, which is essential for understanding the starting point of each period.
- Ending Carry Value (ECV): represents the value at the end of the period, before any reductions.
- Reduction: a change applied after establishing the beginning and ending carry values for the period.

The final step will be to calculate the actual Discounted Cash Flow (DCF) value.

Add three new `CALCULATION`

columns in the `Year of Date`

group called `CV 0`

, `ECV 0`

and `Reduction`

.

Since we're starting with the first year, there won't be any `CV 0`

initially. Therefore, there will only be an `ECV 0`

, and no `Reduction`

at this stage.

In the `ECV 0`

column, insert the following formula:

```
Subtotal(Sum([Price] * [Quantity]), "row_parent")
```

This calculation will give you the `ECV 0`

for each `Store Region`

, but keep in mind, this is only accurate for the first year.

The calculation needs to be dynamic, as in year two, we'll introduce a carrying value, which in turn means we'll have a `Reduction`

, resulting in an updated `ECV`

.

Here is where the challenge arises: the `ECV 0`

column will inform the `CV 0`

column, which then informs the `Reduction`

column, and in turn, influences the `ECV`

again.

This cycle moves down row by row for each year, but since Sigma operates on a columnar basis, we need to handle this differently.

We will use helper columns to simulate this recursive logic.

Let's first adjust `ECV 0`

to ensure it is correctly calculated only for the first year.

Change the formula in `ECV 0`

to:

```
If([Rank of Year of Date] = 1, Subtotal(Sum([Price] * [Quantity]), "row_parent"))
```

This gives us:

Now, let's transfer this calculation as our carrying value for year two.

In the `CV 0`

column, insert:

```
If([Rank of Year of Date] > 1, Lag([ECV 0], 1, Null))
```

This gives us (after sorting the `Year of Date`

column to show the most recent date first):

Next, define the `Reduction`

column with the following formula:

```
If([Rank of Year of Date] = 1, Null, Sum([Quantity] * [Price]))
```

This gives us:

Now, we'll create additional helper columns to handle the recursive logic.

Add a new column in `CALCULATIONS`

named `CV 1`

; set its formula to:

```
If([Rank of Year of Date] = 2, Lag([ECV 0], 1, Null) - [Reduction], Null)
```

Add a new column named `ECV 1`

; set its formula to:

```
If([Rank of Year of Date] = 2, [CV 1] - [Reduction], Null)
```

Add a new column named `CV 2`

; set its formula to:

```
If([Rank of Year of Date] = 3, Lag([ECV 1], 1, Null) - [Reduction], Null)
```

Add a new column named `ECV 2`

; set its formula to:

```
If([Rank of Year of Date] = 3, [CV 2] - [Reduction], Null)
```

This gives us:

Now that we've created all the necessary columns for carrying values (CV) and ending carry values (ECV) across multiple years, let's consolidate them into a single `Final CV`

and `Final EV`

column to represent the complete discounted cash flow analysis.

Create two new columns in `CALCULATIONS`

and rename them to `Final CV`

and `Final ECV`

.

Use the the following formulas for each:

**For Final CV:**

```
Coalesce([CV 0], [CV 1], [CV 2])
```

This formula will select the first non-null carrying value from the available years. It effectively gathers the carrying value for each row across all years in one column.

**For Final ECV:**

```
If(IsNotNull(Coalesce([CV 0], [CV 1], [CV 2])), Coalesce([ECV 0], [ECV 1], [ECV 2]), Null)
```

Similarly, this formula picks the first non-null ending value from all available EV columns, consolidating them into a single `Final ECV`

column.

This gives us:

Hide all other `CV`

, `ECV`

and detail columns, now that the values are consolidated into `Final CV`

and `Final EV`

.

This step will make your data cleaner and more user-friendly, ensuring the focus remains on the final results.

Unhide the `Reduction`

column; we want to see that too.

This gives us:

We can also replace the `null`

values with a blank space, to clean up the table more.

Select the `Reduction`

, `Final VC`

and `Final ECV`

columns (shift + click on each column), then open one of the column menus and select `Format`

> `Custom`

:

In the `Display null as`

, just add one space (blank text) and click `Apply`

:

This gives us:

Now we'll calculate the discounted cash flow, which is the ultimate goal of this analysis.

Click the `+`

to add a new `Control`

element called at the top of your Sigma workbook.

Rename the control to `Discount Rate (WACC)`

This control will allow you to input different discount rates interactively, giving you the flexibility to test various scenarios.

Set the control's configuration as shown.

Enter a percentage value that represents the discount rate (for example: .10). This rate will be applied to your DCF calculations:

The `Discount Rate`

is used to adjust future cash flows back to their present value. The higher the discount rate, the lower the present value, reflecting the time value of money.

Add a new column in `CALCULATIONS`

and rename this column to `DCF`

.

Enter the following formula into the DCF column:

```
[Final ECV] / Power((1 + [Discount]), Number([Rank of Year of Date]))
```

This formula calculates the present value of the future cash flow (Final EV) by applying the discount rate over the specified number of years.

The `Power`

function adjusts the discount rate over the given year, which correctly discounts future values to the present.

The DCF column now represents the value of your asset, project, or investment discounted back to today's terms, using the specified discount rate.

The table now looks like this:

The `DCF`

values now reflect a properly discounted cash flow analysis over time, with larger discounting applied to years further into the future.

At this point, we should give the table a more appropriate name, so that it is clear to user.

Rename the table to `Multi-Year DCF Analysis for Store Regions`

and `Publish`

the workbook.

Save the workbook as `Discounted Cash Flow QuickStart`

:

In this QuickStart, we covered how to create a discounted cash flow (**DCF**) in Sigma, using sample data.

**Additional Resource Links**

Blog

Community

Help Center

QuickStarts

Be sure to check out all the latest developments at Sigma's First Friday Feature page!