Valuation Note: Automated DCF Valuation Model Construction - Compliant and Secure Encrypted Digital Currency Exchange Platform

One year ago,

I organized a complete DCF valuation template based on the ideas of two investment banking valuation masters, JOSHUA ROSENBAUM and JOSHUA PEARL.

It’s no exaggeration to say,

this valuation model accurately predicted the trend of most listed companies over the past year.

Of course, the model is not万能,

the most important thing is that the assumptions within need to be tailored with your own judgments,

and then adjusted based on the company’s industry,

business, and other actual conditions.

1.0 Model can be found in the valuation notes: detailed explanation of the DCF valuation template construction

One issue with the 1.0 model is,

the data needs to be entered manually.

This is not very friendly for companies with a long historical period,

more importantly, each time you need to review financial reports,

find the accounts,

fill in the numbers,

which is time-consuming and laborious,

and prone to errors,

not to mention reviewing various companies.

Therefore,

I have updated to the 2.0 model,

also in Excel,

focusing on automation: just input the stock code,

and all data collection,

cleaning,

valuation calculations, and other steps are completed automatically.

Of course,

the parameters can be adjusted according to your subjective judgment,

which will not affect the automation #估值分析# #DCF#

Below is a rough explanation of how to use it:

1. Valuation main sheet

Here I use China Jushi as an example,

just replace the stock code with the desired one,

and all historical financial data will update automatically.

Two key formulas:

1. FCFF = EBIT×(1-t) + D&A - CapEX - ΔNWC
2. EBIT = Operating Revenue - Operating Costs - Taxes and Surcharges - Operating Expenses (selling expenses,

administrative expenses,

R&D expenses,

other income,

credit impairment losses,

bad debt losses,

inventory write-down losses)

Based on the above historical data,

the key indicators needed for forecasting are automatically calculated,

including revenue growth rate,

costs,

total operating expenses,

depreciation and amortization,

capital expenditure as a proportion of revenue,

and income tax rate.

These historical indicators can provide reference for subsequent forecast assumptions.

Additionally, the two highlighted sections also need to be set by yourself,

here I assume WACC is 12%,

perpetual growth rate 2.5%.

2. Net Working Capital Calculation Page NWC

In the cash flow formula above,

NWC is an important component,

so a separate sheet is created for detailed calculation.

The key formula here is: NWC = Current Assets - Current Liabilities = (Accounts receivable and notes + Inventory + Prepaid expenses and others) - (Accounts payable + Accrued liabilities + Other current liabilities)

Similarly,

based on historical data,

the key indicators needed to forecast NWC are automatically calculated,

including DSO,

DIH,

DPO,

and the proportion of prepaid expenses,

accrued liabilities,

and other current liabilities to revenue.

3. Assumptions page: Income Statement & Cash Flow Statement & Balance Sheet

The assumptions in these two major pages are the most subjective part of this model,

and also the most critical for prediction accuracy.

Of course,

if you don’t trust your own judgment,

you can fully use the average levels of the past few years as the standard for future forecasts.

There’s not much to say about these two pages,

the only difference is that they are divided into baseline,

optimistic,

and pessimistic scenarios,

which also makes sensitivity testing easier.

4. Valuation conclusion

After completing the above data,

return to the valuation main sheet to see the results.

For example, my cash flow forecast conclusion here is:

Additionally,

regarding the derivation of the conclusion,

two calculation results are provided: EV/EBITDA and implied EV.

Fully automated,

no need to modify anything (except exit multiples).

Finally,

add sensitivity analysis.

This is the framework of the DCF valuation model V2.0,

saving everyone the trouble of searching for financial reports,

entering data,

so you can focus on judging key indicators for the future.

I will continue to update smarter,

more convenient valuation models,

such as interactive interfaces,

OCR functions, etc.,

to solve database issues.

**$HTX $SLN $AVA **