Building a cash flow budget in Excel is an important skill. This task is a typical one for accountants, and Excel can make cash flow budgeting simple and effective.
- [Instructor] It's time to jump in and start getting our hands dirty in Excel. We're going to start with something simple. Using Excel to create a cash budget. I'm in the 01_03 Begin Excel exercise file. Now as you can see in this exercise file we have a small business cash flow projection for ABC Company. And we've done this for the period January 2018 through December 2018.
Now, below we have our areas for cash receipts, this is all the incoming cash flow, and we also have the area for cash paid out, all of the cash outflows. Once we've taken in all of the cash we're going to take in for a particular month and paid out all of the cash we're going to pay out. We come down below and look at the total amount of cash paid out versus cash paid in and the cash on hand at the end of each month.
Now this type of cash flow projection is critical for any business that's out there right? It's not enough to make an accounting profit, we also need to be generating positive cash flow over time lest we run into various capital constraints. Now, in our role as accountants, we might be interested in going through and making some projections on that. I've added numbers for the first few months here, but let's just add a few more. Let's pretend that in May we take in $600 in cash sales and I'll fill in a few random numbers here for the other figures below.
And so we come up with total cash receipts of 623. That increases our total cash available to 1295 at the end of the month. We'll also have cash that's paid out over time. So again adding some random numbers here, we can see that these figures would sum up to 174. If we come down below then, that's the subtotal for the amount of cash that we spend in May.
And our total cash after considering our previous balance, our new cash inflows and our cash outflows comes to $1121. We're using a variety of different formulas in Excel to do this, but Excel offers a very neat and clean way to go through and assess those cash flows. The other nice thing about Excel, is that if we're trying to make projections we could do that very easily through the end of the year for example.
If we think that our cash expenses will stay the same from May through December, we can simply drag and drop across so that our outflows are consistent during that time period. Now you're ready to go through and start identifying and isolating the main parts of the cash budget and putting them into practice for any businesses you deal with.
- List the most common tasks and approaches faced by accountants in their daily lives.
- Determine whether transactions can be exported from Quickbooks to Excel or not.
- Outline the structure that the SUMIF function uses.
- Explain how to measure EBITDA.
- Identify at what level the General Ledger tracks expenses.
- Break down how to construct a business valuation using a cash flow analysis.