You are in consulting business. You are hired by a small real estate company to help them manage their liquidity budgeting (or in other words to figure out the best timing of their expenses to minimize the extra cash needed). They want you to build for them a liquidity budgeting tool (an Excel model) that they will use to analyze and manage their working capital. The tool must contain enough flexibility to enable the future users to play with the model and conduct different analyses. Among others, the company requests a user-friendly and sophisticated input part where a manager can change not only the amount of money for a particular expense, but also the timing of this expense, e.g. in which month to fix something, they also want to have sensitivity analysis, scenario tool and automatic breakeven analysis, so it can be easily repeated for different scenarios. Overall, the model must be able to help analyzing the liquidity, see how much extra money is required, what happens if something goes not as planned, whether it makes sense to delay some costs etc. Your contract includes building the model and performing the detailed liquidity analysis for the next year.

CASE DETAILS

The real estate company operates three rental apartments:

APARTMENT 1. One tenant has been living there and expected to live the whole next year. Revenues: rent per month 345€/month + water 20€/person/month + electricity paid by the tenant. The costs include the following. Condominium (vastike) 122€/month (fixed monthly expense regardless of whether an apartment is rented or not). Apartment house company loan repayment (rahoitusvastike) 43€/month. Water payment 20€/person/month. Expected a change of refrigerator to a new one in March, cost 700€.

APARTMENT 2. Unrented, expected to be rented in the third month from the start of the year to a young couple at 440€/month including water. Condominium 202€ per month. Water 20€/month/person.

APARTMENT 3. (for three tenants) Under renovation. The renovation is expected to take four months more with overall cost 4000€ evenly distributed. After that, it can be rented for 600€/month + water 17€/person/month. Condominium 121€/month. Water payment 17€/person/month. Apartment house company loan repayment 44€/month.

In addition to the apartments, the company has the following costs. Banking costs approx. 10€/ month, office miscellaneous costs approx. 55€/month, book-keeping cost payable in March 300€, previous years taxes payable in October 440€. Miscellaneous costs at the end of every quarter are estimated at 100€. Insurance payment in October 200€. And, belated pay-back of old rents from an old tenant (270€) is expected in June.

By the end of the current year, the company balance is 2300€ (the balance at the start of the planning or starting liquidity).

1. LIQUIDITY BUDGETING TOOL The main goal is to calculate how much the company owners must insert money into the company (they will insert money in full 1000 euros, meaning 1000, 2000, 3000 etc.…) so that the liquidity always stays positive. The tool must include liquidity budgeting for one year (12 months) starting from January and running until December. The model is created for the company so that its managers will use it. Therefore, it should be user-friendly. Make sure that all inputs (information described in the case) can be changed (both values and months) and it will be reflected in the calculation.

for the month inputs create a drop-down lists (use Excel tool Data Validation) with all months spelled with letters (January, February…), so the user does not need to type it every time. In this case, however, the calculation becomes a bit tricky. IF function will definitely be useful. You also might want to create a table that associates months titles with their numbers and then to use lookup functions, this will be handy to accommodate durations and delays specified in a number of months.

make sure you enable the user to specify the total amount and the number of months for the renovation of the apartment 3, and make your calculation automatically reflecting the changes in those inputs. We will need that later for the scenario analysis.

for miscellaneous costs (every quarter) the function MOD might be useful to automatically reflect user’s input in the calculation.

Required

Build graphics that would display (i) liquidity before inserting money, (ii) inserted money, (iii) resulted liquidity (check that it is above zero all the time).

Design and organize the output part of the model. 

How many months in a row the enterprise has a negative balance and unable to pay the bills without extra money? 

How many months in a row its liquidity must be financed in order to avoid the problem?