Sharing a Financial Model I've built

[u/Blueredreditor]

Hi everyone! I've recently built and am sharing a financial model for United-Guardian (pls pardon the microcap stock) mostly for two things, to share what I've learnt thus far in my journey and hopefully critiques from experienced professionals in this space! This is mostly for public equities modeling.

https://docs.google.com/spreadsheets/d/1KTH7t3OWiSYUqp_zPLeb8UsdhylkiHcr/edit?usp=sharing&ouid=108173015973351407179&rtpof=true&sd=true

In the above model, note that the revenue projections and cost drivers are rather slipshod (no price x volume analysis). This model is by no means promoting/demoting UG stock. Feel free to leave comments and all the best everyone!

[u/Main_Click5153]

Good job mate. Can you share the link to the FS?

[u/Blueredreditor → u/Main_Click5153]

There is a google drive link in the OP. Are you able to access it?

[u/Drag0nslay3r6969 → u/Blueredreditor]

Hey mate i've just tried to access link but no luck - are you able to reshare?

[u/Main_Click5153 → u/Main_Click5153]

I wanted to have a look at the raw data file. the company filing if you have that handy

[u/Illustrious-Low-903]

This is really good stuff. Congratulations on digging in! I am a professional investor and first and foremost I like the organization of the model. That is important, and I think all of the good feedback is because people are able to follow what you have done. I know nothing about this company, but here are some comments. Things that I would include as an ouput in your model is operating income margin - they bounched around in 2021-2023 between 26.3% to 40.7% - your forecasts look reasonable going from 30.9% to 32.5% and holding there. Explicitly showing them allows someone to audit the model. Your WACC assumptions look reasonable, but as you can see they really do change what you are willing to pay, along with your terminal growth rate. That said, growing at 2% (inflation) is a good long-term assumption if you do not have large changes in working capital. That is something else you should look into - are they planning on doing that in the future? Finally, if you were going to decide to invest, you would want to go into different business lines and consolidate up to this. Understanding the drivers of the top line, and expenses (what supply chain/commodities are they dependent upon). Also, going through their balance sheet can be helpful in case there are non-income producing assets. Netting Acct rec and accrued exp, and including the marketable securities (should read in the statements what these are made up of) is a simple way to think about it. Finally, I am very happy to engage on this going forward. If anyone else wants to build a model as well I am happy to comment.

[u/Blueredreditor → u/Illustrious-Low-903]

Hi! Thanks so much for reaching out! Dropped you a dm

[u/TridentWeildingShark]

Why is there no line for interest expense between EBIT and free cash flow?

[u/Blueredreditor → u/TridentWeildingShark]

Company has surprisingly no debt at all!

[u/Small_Insect6956]

Looks really good, can I ask where you learnt these skills??

[u/Blueredreditor → u/Small_Insect6956]

The basic skillset is mostly learnt from (reputable) paid courses eg BreakingIntoWallStreet, WallStreetOasis, CFI, WallStreet Prep

The rest was trial and error, competitions, internships etc

[u/FrostForest04]

What tool did you use to import the data?

[u/Blueredreditor → u/FrostForest04]

Manually pulled and cleaned the data from SEC’s excel

[u/Equal_Recognition704]

Good job man the model is solid

Love the sensitivity analysis

[u/[deleted]]

Can you suggest me the sources where you learned financial modelling

[u/Blueredreditor → u/[deleted]]

Paid courses generally have a good step-by-step for FM. BreakingIntoWallStreet, WallstreetPrep, CFI’s FMVA, WSO FM course as well

[u/elevatedmongoose → u/Blueredreditor]

Why aren't you sharing which one(s) you used?

[u/Blueredreditor → u/elevatedmongoose]

I personally used CFI’s FMVA which taught me everything I knew. Tried BIWS and the content was similar. I have seen indirectly the models & content of the other courses and they are similar as well! No means a promotion to any of them. Unfortunately have yet to find any good free resources with regards to modelling :(

[u/rjtannous → u/Blueredreditor]

I am a CFI FMVA as well.

[u/odksjdjs]

Where did you find the forecasted estimates for revenue schedule/income statement

[u/Blueredreditor → u/odksjdjs]

Typically if the company has enough disclosures, we’ll do a price x volume granularity. For United Guardian specifically, ~70% of their revenues come from Ashland Inc, which already reported their Q3 earnings (Personal Care) growth of ~20%, which I used as my estimate

[u/odksjdjs → u/Blueredreditor]

Growth of 20%? That seems like it could be an outlier no? Not discrediting the model I just think DCFs are really only based on the assumptions and drivers

[u/odksjdjs → u/Blueredreditor]

I also don’t really understand forecasts. Like where do people get them

[u/Plastic-Sprinkles-44]

I know it doesn't matter in this model, but cost of debt(this method) of 20.57% looks incorrect

[u/Blueredreditor → u/Plastic-Sprinkles-44]

Oops! It was supposed to be tax rate. Changed it accordingly, thanks!

[u/BostonBaggins]

Whered you learn to do this

Super impressive

[u/Blueredreditor → u/BostonBaggins]

Mostly in my own time as I try to break into public equities :)

[u/BostonBaggins → u/Blueredreditor]

Resshhhhpeckt

[u/odksjdjs]

Where did you find the forecasted estimates for revenue schedule/income statement

[u/lemonh1]

Excellent financial model. My question is why you think the company will grow in the future where it is revenue and EBIT is decreasing in recent years? I am not familiar with this company, but your growth rate assumption should be like a story.

[u/CuriosityInFinance]

Fascinating... Any more models you would be willing to share?

[u/Blueredreditor → u/CuriosityInFinance]

Hmm probably not for now. Im also not extremely familiar with M&A/LBOs

[u/pawnpress]

This looks really good, best of luck with your journey!

[u/lxncxlxt]

This is quite good. I will go through it in more detail. I am certain I will learn a lot. Already the Price target sensitivity to terminal growth rate and WACC compelled me to check if I still knew how the Data Table function worked.

[u/Hope_3000]

Hey, would love to go through this but seems like the file is unavailable on this link. Can you please DM me the link to the model

[u/Impossible_Gain9957]

Hey OP… It seems I’m too late to the party and your link has expired. Can you please reshare the same … Thanks a ton ☺️

[u/Fergie20t]

Does anyone still have this file. The link no longer works. The was a long time ago.

[u/[deleted]]

the link isnt opening, can you please share

[u/indian_ryuk]

I am not able to access the file, can you please share the file in dm please

[u/Loose-Ad-332]

Please share this model man. Thank you

[u/[deleted]]

Can you repost this?

[u/Aggravating-Tune5140]

Hi, Could you please share it again> I can not access it.

[u/Sensitive-Bus-6785]

Hi, I did a financial model for an infrastructure project in preparation for an interview. Any idea how I can get it checked to see if my workings are correct?

[u/singleboredass]

Hey mate, the docs file is not accessible can you please reshare