Taking my first crack at doing a model from the inside, and I've already been tasked with coming up with the best practices for my finance department.
Any general rec's from someone who has been doing this for a while (especially compared to a buy/sell-side background)? Specifically, how would you set up a model to incorporate the whole company's finances? Relatively small company, but the spreadsheet they have is already unwieldy. One person wants lots of linked spreadsheets (for each operating unit), the other wants everything in one.
How many tabs/file size would this spreadsheet be?
I haven't worked in corporate dev before but from my experience Excel is much happier/useful when you do as much as you can in the same file. Doing across files loses some functionality. Others may have gotten around this issue though.
Tabs for each division, product line (however you separate it out), flowing through to an operating model (first tab).
Yeah, I would say have tabs for the divisions / regions / products and then have roll up tabs. Probably the best way to go about it. Uniformity is key for these types of models so it's better to take a lot of time up front planning it out so that the layout is intuitive and dynamic than to rush through it and then you are left with a mess of a model for your group to use.
For starters, I would recommend keeping it simple at first and have a control / assumption sheet, and then it's kind of up to you to determine whether you want your standalone model housed all in one page or if you prefer for each statement to have their own tab (this can be somewhat useful if the quantity of data necessary to get the specific line items is vast (i.e. you will have a consolidated IS, BS, and CF).
I typically prefer to have everything on one tab in my standalone, and then separate tabs for DCFs, LBOs, and SOTPs. There are many ways to build your model so that it is clean and intuitive but the most important thing is that you build it in such a way that YOU understand it and can manipulate it as you wish. This will be critical if you are going to train others to use it and if you will have to speak to it.
I would also recommend password protecting the sheet so that anyone can open a read-only copy, but they cannot edit the "Master" file unless they know the password. This ensures that you have ultimate control over content / maintenance of the model.
Feel free to ask follow up questions if there are things you want clarified, or things you are curious about.
xoxo
Those are all really good points for a finance model. My question to the OP would be what type of model are you trying to build out? Is it for valuation / consolidation? or is it an operating model? If it is the former than I couldn't agree more with LL. However, if you are trying to make an operating model for department wide use and simple model like that probably won't suffice. Operating models I've built in the past have tabs with different types of metrics, financial statements by division, more robust working capital calculations/sheets, etc etc etc. Basically, extremely granular. Also, in some of my experiences they model was designed to link to accountings monthly close workbooks so then you could track projected vs. actual. These internal models are not about valuation.
So, with that said, I would recommend getting to the bottom of what you are trying to build and ask a lot of questions about the desired output before you dive in.
You will still want the ability to roll everything up so that you have a valuation. It really isn't that much different... When I was in banking we received company operating models all the time and we typically tweaked / rebuilt them using the above best practices. For outputs / variance analysis I strongly recommend using Indirects and maintaining separate files. These operating models can get pretty big and you don't want to keep adding tons and tons of data into the same workbook because it slows everything down.
xoxo
It's an operating model. Currently, I don't know, 30 tabs? It's madness. I'm at a "mature" startup that used this model for cash management, but it has morphed into an unwieldy catch-all for analysis. It clearly has been cobbled together as needs have arisen, so I have been tasked with cleaning it up.
I'm kinda envisioning what L&L is talking about. I'm trying to instill some discipline in management, though, to force them to state how they want to manage the company. I'm guessing I need to model by operating plant, rolling up into product lines and operating costs (because those are managed separately at this stage of the company), then into consolidated financials. There is already a main assumptions/scenario tab, but there are other assumptions scattered around in other tabs.
The main question in my mind right now is do I consolidate all operating department forecasts/details in my model, or can I just draw the key info and if I need to answer questions, go back to them? I'm trying to figure out how to streamline the work that goes into it on my end - all of the assumptions and forecasts should come from other people. Linking is always dicey, but I don't want to get slammed with entering actuals every month.
ke18sb: how do you create those models so they are still manageable?
surely anything that can be linked can also be handled by a fairly straightforward macro? unless the source files are not in a stable format...
freemarketeer what is the size (megabytes) of the original excel file that you are taking over?
This should help others visualize how unwieldy it is.
Hmm, I don't actually know. But I did find another 15 tabs that are hidden...
The big problem is that it was built as an ad hoc model for one request, then it became the basis for near-term financial projections. And it very clearly looks like it has gone through several transitions.
I've actually sort of lucked out - there was a new model built specifically to model the operations, and I've co-opted it to build out for our next forecast. It's ugly, but it is functional. Really, the biggest problem is just that the company has a wickedly complex financial structure. While I will be able to streamline the model, there's only so much to do.
Reprehenderit eum odit minus repellat inventore. Dolorem eaque ipsam sequi ex voluptatibus laboriosam. Et et sed labore eum.
Fugit omnis itaque sapiente maiores perferendis vitae. Voluptas voluptate et officiis nesciunt quod.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...