Income Statement Considerations
Cash and profit are not the same thing, as we all know, and there are two key considerations we need to bear in mind when integrating an Income Statement model with a cash flow model:
1.The Matching Concept. We place sales and purchases in the period that they relate to. The timings of receipt and payment are not considered.
2.Non-cash costs are introduced into the Model.

However, with a well-constructed Input Data sheet and Assumptions sheet, it is relatively easy to build an Income Statement model linked to the Cash flow model.

Balance Sheet Considerations
The benefit of creating an integrated financial model including a Balance Sheet is that it must balance. When the Balance Sheet in your model balances, that gives you confidence that the Model as a whole works. It doesn’t mean that there are no errors in the Model, but it does mean that its three parts hang together.
At the same time, the Balance Sheet model contains elements which do not appear in the cash flow Model or the income statement – namely capital and long-term liabilities that existed before the periods covered by the Cash flow Model and Profit and Loss Account.
This means that the integrated financial Model requires an “opening” Balance Sheet that we can then feed the figures generated by the other models into. Thus, the integrated financial Model requires two Balance Sheets – an opening Balance Sheet which is then acted upon by the factors in the Model to produce a closing Balance Sheet.

Let’s bring it all together with an exercise

Lesson 13 Exercise Cash flow Model with Income Statement and Balance Sheet
Part 1: Pricing Model
Camberwick Green Care Home has 40 full-time residents. It is run by Gloria and Gaynor, who own 52% of the shares. The Local Authority own 10% and have a board representative. The remaining shares are owned by individuals in blocks of 5% to 10%.
The Care Home currently manages a net profit margin of 3% with extremely careful cost control. The shareholders wonder how long this can be sustained and would like to achieve a rate of 6%.
Gloria and Gaynor are considering adding a 10-room extension. This would be financed by a commercial loan.

Gloria and Gaynor would like a financial model that considers three pricing strategies:
1.Using the additional rooms for Local Authority funded residents. This would ensure 100% occupancy at the contract rate of $900 per week full board.
2.Marketing the new rooms to privately paying clients. Information from other care homes in the region suggests that a rate of $1200 per week would deliver approximately 75% occupancy, while a rate of $1100 per week could deliver 85% occupancy.
3.Making the new rooms available for respite care. The going rate for such short-stay care is $1500 per week, but occupancy is harder to forecast. The worst-case forecast is 50% occupancy, with the best-case being 70%.
Staff and associated costs for Option 1 are $700 per room. They are $900 per room for Option 2 and $1,100 per room for Option 3.
The operating costs of the extension would be $750 per week for energy and maintenance for each of the options. This includes interest on the loan needed to finance the project.

Question
You are required to prepare an Input Data sheet for the Camberwick Green Care Home and a Calculation Sheet to help them select the best option. No sensitivity analysis is required.

Pause the video here while you work on the Model

In this example, I have structured the Input Data and Calculation sheet slightly differently from the previous examples in order to give visibility to all the possible outcomes.
In the input data section, I’ve shown the level of occupancy at each fee rate, including the three possible occupancy levels for the third option – respite care.

The calculation sheet is straightforward and shows the weekly contribution generated by each option or variation on it. The Model shows that the Respite Care option generates the most contribution when at 60% or 70% occupancy. But if the respite care service only achieves 50% occupancy, then the other options can match its contribution or slightly improve on it.
Of course, the managers of the home need to take other factors into account, too – not least the opinions of the other stakeholders, including existing residents and staff.
At the same time, the contribution is not the only financial factor which might affect the decision. We will review the cash flow impacts of the options next.

Part 2: Cashflow Model
Camberwick Green Care Home have decided to move ahead with the respite care option and have asked for your help to prepare a cash flow model.
The following user-definable assumptions have been prepared

All of these assumptions can be changed by the user.

If you want to have a go at building the cash flow model for yourself, pause the video here.

If you are ready, let’s go to the spreadsheet model.

I’ve set up the assumptions area using the information provided for the respite care option. The fee rate is $1500 with staffing and associated costs $1100 per week per occupied room. On top of that, there are general overheads for the unit of $750 per head. The other assumptions are fairly standard for this sort of Model. I am using drop-down lists to let the user select the number of months credit period for credit sales and credit purchases, and the interest rate and size of loan can also be varied.

As we saw earlier, the level of occupancy could average between 50% and 70% per week, and I have a drop-down list to let the user select the level they want to model. At present, I am applying the user-selected level of occupancy across every period in the Model
I’ve been wondering whether it might be more realistic to use the RANDBETWEEN function to randomly choose the level of occupancy between 50% and 70% in each period and I’ll come back and try that once I’ve demonstrated the Model as it currently is.

The intermediate calculations section works out the relevant amounts for the Model including the credit sales cash sales split at the selected level of occupancy, as well as other operating costs and interest costs.

The Cash flow model itself uses the HLOOKUP formula I introduced previously to put the cash received from credit sales into the appropriate month.
Staff and associated costs and interest payments are assumed paid in the current month while operating costs are on credit.
As the Model currently stands, I have set it up for 60% occupancy with all income on credit and a one month delay in payment for credit sales and credit purchases. We see that a positive cash balance is achieved at the end of period 8, with a total positive cash balance of $55800 when all credit sales and purchases have been settled.

If I move occupancy up to 70% and assume that 75% of sales are on credit and 25% are cash, we see that the project achieves a positive cash balance in period 5 and a balance of $76600 by the end of the year. The owners of Camberwick Green Care home really need to achieve the maximum occupancy they can for the respite care project to be viable in cash flow terms.

Let’s go to the next tab and try it with the occupancy randomly varying between 50% and 70%.
Now the occupancy in each period is going to randomly vary between 50% and 70%. I’ll keep credit sales at 75% with a one month delay in receiving cash from credit sales and paying suppliers.

Now in the Intermediate Calculations tab, I have added a new row to calculate total sales with the RANDBETWEEN function to select a level of occupancy anywhere between 50% and 70% in each period.
The cash sales and credit sales then work off that data.
And I have had to calculate the staff and associated costs as a percentage of revenue as the occupancy can vary. The room rate is $1500, and the staffing and other costs per occupied room are $1100 – so those variable costs are 73.3 recurring per cent of the income.
Obviously, the cash flow sheet is going to keep recalculating, but when it is randomly selecting a level of occupancy in the range 50% to 70%, it pretty consistently generates a positive cash balance from month 3 and is producing a positive cash balance at the end of the forecast period between $160,000 and $180,000

I feel it is more realistic to suggest that bed occupancy for respite care would vary each month so this version of the cash flow Model would seem more realistic.

To finish this Model I have created an income statement and balance sheet in this tab. It links to the first cash flow Model for Camberwick Green Care Home rather than the one with randomly variable occupancy.
Open the spreadsheet that accompanies this course if you want to study it in more detail.