The spreadsheet is used to calculate the bottom line of a hypothetical European fund management firm, which has $$20$ billion of Assets under Management $($AuM$)$ in a variety of unlisted funds. For the real estate fund management industry, $2007$ was a normal, probably even a good year $($boom$$ year$).$ Our hypothetical Fund Manager produced a profit of $$30$ million.

$2008$ was a $$bad$$ or $$bust$$ year, when lots of things went wrong for the real estate industry. Use the spreadsheet provided to calculate what happens to the bottom line of a fund management firm when the real estate cycle goes from a $$boom$$ to a $$bust$$ year. Consider and solve the spreadsheet for two different scenarios:

Scenario A: The industry downturn catches the firm$$s management completely by surprise. There is no time for management to counteract the negative market effects. In this scenario we want to see how the swing from good to bad year impacts the bottom line without the company having time to make $$adjustments$.$ In other words, we want to find the $$pure$$ profitability impact of the good to bad year swing. Create, save, and label your spreadsheet Scenario A and comment on why you made your changes $($in a separate Word document also labelled Scenario A$).$

Scenario B: In this case management suspects at the end of the good year that times will probably not last. It has time and foresight to prepare for the downturn and make certain adjustments to weather the storm. Use the spreadsheet provided, fill it out $($see below$),$ label and save it as Scenario B$.$ Justify the adjustments you have made $($to protect the firm$$s profitability$)$ in a separate Word document again labeled as Scenario B$.$

The spread sheet allows you to enter the following $5$ metrics $($in the right$-$most column labelled $\%$ Change$)$:

AuM $$ What will happen to the firm$$s AuM in the bust year? Will valuation levels stay the same, rise or fall? If you believe valuations will drop by $20\%,$ enter $-20.$ Realize that valuation levels are not controlled by the firm. The $$market$($in the form of independent appraisers$)$ will dictate the value of the firm$$s AuM

Employees $$ The firm$$s AuM of $$20$ billion $($in the good$$ year$),$ are managed by $350$ employees. Make changes only Scenario B$.$

Employee Compensation $-$ The figure of $$220,000$ per annum is the average for all employees, i$.$e$.$ from recent hires to the top executives of the firm. It consists of the total of all compensation parts $($Base $+$ Benefits $+$ Bonus$).$ Make changes only in Scenario B and keep in mind that the fund management firm works in a private and competitive environment, where employees are free to leave and join competing fund managers.

Acquisition Volume $$ Before entering a value in the field $($in either Scenario A or B$),$ reflect about what happens in the marketplace when the real estate cycle turns in the wrong direction. Is the firm completely free to make adjustments or is their ability limited by what is going on $$out there$?$

Disposition Volume $$ Your entries under both Acquisition and Disposition Volume may be the same in Scenario A and B$...$then again, they may not. You will need to justify your actions $/$ entries in both cases.

For the last two entries $($Acq$./$Disp$.$ Volume$)$ enter $-20($or $+20)$ if you think annual acquisition $/$ disposition volumes will decline$/$increase by $20\%.$

When entered properly in the $\%$ Change$$ column the top part of your spreadsheet will look something like this:

$[$Hint: Try not to $$steer$$ profitability in one direction or another, such a finagling a breakeven year or even keeping the boom year profit of $$30,000,000$ intact. Rather $$let the chips fall where they may$.]$

This case study requires you to submit $4$ documents, $2$ each per scenario $($one Excel spreadsheet and one Word document$).$