How to build a Dynamic Financial Statement Model

Replies to This Discussion

Permalink Reply by dalermatt on September 17, 2009 at 1:31pm

Toby,

A wonderful model and a terrific template for further modeling attempts. I'd love to have this embossed on a white board!

A couple questions (being new to this forum I apologize in advance for not hitting the marks for which this discussion is aimed):

1) In your opinion is it beneficial to develop budgets using models built on this type of framework? At my most recent Fortune 500 posting the annual budget is developed at incredible expense (easily several million dollars all in) but seemingly has little to do with, ahem, reality. Rather than have every manager in the company build the budget line by line it seems that a model could provide a sufficiently "accurate" budget at a fraction of the cost, with departments contributing a little finishing work at the end of the process.

2) What are some options for incorporating headcount and labor costs in the model? I realize that the labor costs themselves fall under the operating expense or cost of sales categories, but they do have some common elements (labor costs will rise a certain percentage each year as will benefit costs; overall headcount needs to be below a certain level as do labor costs).

Again, thanks for your excellent chart.

Dale Matt

▶ Reply to This

Permalink Reply by Paul Zaman on September 18, 2009 at 12:25am

Cool.

Some accounts learnt how to do activity based costing. Cost pools, cost drivers which determines total costs. Effectively total cost pool divided by a cost driver (physical activity) creates the unit cost/driver activity.

Business, Investment and Valuation modelling is the same principle. Activity Based Valuation.
Everything must be decomposed to the physical activity. You can only forecast physicals. You can not forecast Dollars!

Well done excellent framework.

Paul at www.qualvin.com for investment management and investment deal enrichment in Asia!

▶ Reply to This

Permalink Reply by Juan Peralta on October 25, 2009 at 2:02pm

Hi Toby,

I am working on a model that includes inventory. You mentioned that you extracted that from the model. Can you provide a diagram that includes it?
Also, in the model I am working with, the call center provides a lot of the service for this particular business (Is it classified as cost of sales?), the customer receives an equipment (so there is inventory when they decide to buy it) and sometimes it is leased to them (I figure this would be a capital expenditure?).

I have spent a lot of time analyzing your model and think it is very clever and clear.

Thanks,
JP

▶ Reply to This

Permalink Reply by Juan Peralta on October 26, 2009 at 7:52am

Hi Toby,

Question:
1) I do not see any links going into Debt or $FromTheInvestors(Equity Module?). How do I trigger the beginning of a new loan and its amortization, for the purchase of a specific equipment, where I am getting specific terms of financing for such equipment? and how do I differentiate it from the access to a business line of credit?

As a follow up question. I am taking a look at the volume section and based on a Key Driver, it is determined that we need to purchase "X" number of Equipment. So, traditionally I would compute it as:

~~ # of Equipment Needed: 10
~~ Total Cost of Purchase for all units: $100
~~ % of Total Cost Financed by seller "Notes Payable": 70%
~~ Equipment Notes Payable: $70
~~ Amount Coming from Companies Cash Account, (and any deficiency coming Cash to be obtained from More Investors Shares?) $30

Thank you,
JP

▶ Reply to This

Permalink Reply by SZ on November 12, 2009 at 6:28am

Thank you Toby for this great illustration.

▶ Reply to This

Permalink Reply by Songkit Sripinyo on December 5, 2009 at 8:22am

Thanks a lot.

▶ Reply to This

Permalink Reply by Juan Peralta on December 14, 2009 at 4:01pm

I see the COGS module. Does you approach show an inventory balance as part of the current assets in the balance sheet? JP

▶ Reply to This

Permalink Reply by hendry yonathan on January 27, 2010 at 9:26pm

wohooow.... its kinda cool... thanks Toby.... =)

▶ Reply to This

Permalink Reply by Andre French on January 28, 2010 at 6:29pm

Toby,
Thank you for sharing this with all of us!
I'm currently taking a management science course that routinely uses excel to build basic models to solve problems.
The professor is a huge proponent of writing down a flow chart (aka. influence chart, to some) to create a coherent framework of the factors that influence the objective sought. He advocates to do this way before before opening excel. This way the modeler can greatly lower the odds of creating a mess in excel.
After all, the outputs of a module and the applicably of these results are only as a good the logic you have based your model upon. In other words, garbage in --> garbage out.
Thanks again Toby!

▶ Reply to This

Permalink Reply by Kunal Maheshwari on February 23, 2010 at 1:04am

intresting

▶ Reply to This

Permalink Reply by Hitesh Dubey on February 23, 2010 at 6:22am

Thanks a lot Toby !!!
i am an amateur trying my hands at financial statement modeling. this thing clears away a lot of confusion and possible errors.

:)

▶ Reply to This

• ‹ Previous
• 1
• 2
• 3
• 4
• Next ›
• Page