Microsoft Excel (see Figure 1) to help TUVs Bond Fund management consider the merits of different investment

Fantastic news! We've Found the answer you've been seeking!

Question:

Microsoft Excel (see Figure 1) to help TUV’s Bond Fund management consider the merits of different investment alternatives. “Given an estimate of the economy and given the two possible investment policies- high and low risks, what will TUV’s net profit be in year 2020, and what will the fund investment level and bank debt be at the end of year 2020?” Different possible economic estimates and risk levels are entered in cells C9 and C10:

Economic outlook (C9) has three values: G for good, N for neutral, R for recession.
Fund risk level (C10) has 2 values: H for high risk, S for safe policy.

To understand the operation in TUV, here is some background information on TUV.

In late 2019, Fund managers started the Fund with $100 million, which was financed by having $10 million borrowed from (or owed to) the bank and $90 million invested from Fund’s investors. The Fund managers invested in securities, and sold shares in the Fund, and the net income for year 2019 was $6 million. Thus the return on the initial investment was 6%, which is the “Return on This Year’s Investment” (ROI) in cell C18 - net income divided by total dollars invested at the start of year. At the end of year 2019, the Fund investment level was $106 million, which was reinvested in the following year. Managers of the Bond Fund charge the Fund 1% (cell C4) of the total dollars invested as management fee, which forms part of Fund managers’ salaries. However, this management fee is an expense to the Fund. Other Fund expenses include taxes, and losses when bond issuers default because of bankruptcy. Bond Fund managers gain money to invest in bonds in three ways:

Net income from previous years reinvested in new bonds.
Investors send in money for units of the Bond Fund; this is called “inflow factor”.
Bond Fund managers borrow money from the bank if total dollars in Bond Fund at the end of year fall below $100 million, which is the minimum fund investment dollars at the beginning of year 2020 (cell C6).

The Bond Fund’s total invested dollars could be reduced in two ways:

Investors cash out their units, and fund managers must liquidate some Fund investments to pay these investors. This is called the “liquidation factor”.
A bond issuer defaults, announcing it is not going to repay its debt. This means that the now-worthless investment must be written off as an expense in the Fund income statement.

Cash inflow factor (cell C15): The willingness of investors to buy more units of the Bond Fund depends on the economy and the Bond Fund’s prior year ROI. Potential investors watch closely the ROI as a performance measure of the Bond Fund. The better the economy and the better the prior year’s ROI, the higher the cash inflow. The table below shows the estimated rate of cash inflow in six possible situations:

	Good Economy	Neutral Economy	Recession Economy
Prior Year ROI > 0.05	10%	8%	8%
Prior Year ROI <= 0.05	8%	3%	3%

For example: Assume $100,000,000 is invested at the start of year. With a Good Economy and a prior year ROI of 0.06, the inflow rate is 10%, and new investment dollars (cell C35) would be $10,000,000

UAWN 2019 NA NA 2020 0.01 0.3 NA 100000000 2020 2019 NA G3 CONSTANTS FUND MANAGEMENT FEE PERCENTAGE 5 TAX RATE EXPECTED MIN.