BØA2042 - Financial Modeling using Excel - Norwegian University of Science and Technology

Assignment 1:

You have the opportunity to acquire a company in the ICT sector with the following forecast for the earnings before interest, tax, depreciation and amortization (EBITDA):

The acquisition value of this company will be discussed below.

For tax purposes, the acquisition costs will be depreciated with 20 % annually according to the declining balance method.
Net income is subject to taxation with a tax rate of 30 %.

It is expected that the ICT company will be sold in the end of 2026. Assume that the sales value corresponds to the book value.
The acquisition of the firm today (beginning of 2020) will be equity-financed with at least 50 %. The required return on equity is assumed to be 14 %.

Furthermore, your bank offers you to use any combination of the following loans:

• Constant amortization mortgage (CAM): interest rate of 6 %, maturity 15 years, maximum USD 30 million,
• Constant payment mortgage (CPM): interest rate of 6 %, maturity 15 years, maximum USD 30 million,
• Credit line: interest rate of 8 %, maximum USD 5 million.
• Excessive cash flow will be deposited on a savings account with an interest rate of 2.5 %.

Question (1) Assume acquisition costs of USD 55 million. Optimize financing and determine your future wealth in the end of 2026. Should you proceed with the acquisition?

Question (2) What is the highest price that you will pay for the firm if you assume the financial structure obtained in (a)?

Question (3) What is the highest price that you will pay if financing is simultaneously optimized at this highest price?

Question (4) Assume that the company can be sold for USD 20 million in the end of 2026. Again, optimize financing and find out if the acquisition at USD 55 million will be favorable or not.

Assignment 2:

Suppose that you have an equity of 100,000 NOK (Norwegian kroner). In the end of May 2020 you want to invest this amount into two securities, each of them can be acquired for 100 NOK per unit at this point of time. Assume that each of these securities is perfectly divisible or multipliable.

Once invested, you have to keep these securities until they expire in the end of May 2024. The streams of cash flows generated per 100 NOK invested into the securities are given as follows:

For additional financing your bank will provide you with at most 100,000 NOK of a constant payment loan (CPL, annuity loan) with an interest rate (????) of 8 % and a maturity (????) of 5 years. In addition, you can draw at most 50,000 NOK on a credit line with an interest rate of 10 %. Idle cash can be deposited on a banking account which does not pay any interest.
Your tasks are the following:

(a) Suppose that you want to maximize your wealth in the end of May 2024: Find the optimal amount of money to be invested into the two securities. determine the optimal financing of this investment.
(b) Now assume that the payments from the securities are not given with certainty. For simplicity assume that all cash flows are uniformly distributed with an interval (spread) of 20.

Question (1). Generate 20 possible future scenarios for each security.

Question (2). Maximize your expected wealth in the end of May 2024 considering that the uncertainty in the returns affects both credit line and bank account. Think about an appropriate structure of your Excel-sheet that makes this task easier.

Question (3). Determine expected return and the return's standard deviation.

Question (4). Generate a risk-return diagram for different equity ratios.

Attachment:- Financial Modeling Using Excel.rar