Modeling Case Study - Balance Sheet Assets
- 02:50
Model the balance sheet of a company, using assumptions and formulas based on the income statement. Learn how to calculate the working capital items, such as accounts receivable, inventories, and other current assets, as well as the net PPE and financial assets.
Transcript
Once we've done the income statement, we can now move to the balance sheet and you can in some models, do the working capital items and then wire them onto the balance sheet. I prefer to model them on the balance sheet first because often you're not really sure what's going to be included in working capital. So I'm gonna go up and get my assumption for accounts receivable, either as a percentage of sales or receivable days. And in this case it's receivable days. So we'll take the day ratio divided by 365 and then multiply by the total revenue number. Because this is receivables. So I'm gonna go down to row 54 then I get the number. Now with my auto formatting, you can see that that's turned red because I've actually got a mixed hard number in that formula. The FE add in will do that. The FactSet add in will do that as well. So what you can do is you can just change the form color to black, or you can put in 365 into an assumption cell. Then I'm going to pull in inventories. And if I had an accounts receivable day ratio, I'm likely to have an inventory day ratio and inventory days. We link cost good sold, so divided by 365. And then I'm going to multiply that by cost of goods sold because in which it becomes cost of goods sold. And then when I hit enter, you'll notice it goes red, which I can fix, but it's also negative. So I need to multiply that by minus 1 and I need to change the font color black. And then I'll get the other current assets, which I'll get from the assumptions. And this is a percentage of total revenue again. So I'll go up to the total revenue line, pick that up, and you may think, well, why don't we just give the total revenue line a name? You could do that. But it's generally looked down on people prefer not to have range names and models because you have to check both the range name and the formulas net PP&E. That's going to come from our base calculation up above. I'll just pull that in. And then we've got financial assets. And financial assets probably will be flatlined.
So we'll just go up to the assumptions and see if we can find financial assets amount? Yes, we can do, and it is flatlined. There's a slight difference between the actual and projected year, but it's pretty minimal. And then we have total assets. So notice also I'm just doing one column. I'm not copping across each row. That is just a tremendous waste of keystrokes.