BØA2042 Financial Modeling using Excel Assignment

Assignment Help Financial Management

Reference no: EM132474628

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):

End of year	2020	2021	2022	2023	2024	2025	2026
EBITDA (million USD)	13	13	13	13	14	14	14

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:

End of May	2020	2021	2022	2023	2024
Security 1	40	40	40	40	40
Security 2	0	30	50	70	90

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

Reference no: EM132474628

Questions Cloud

Discuss the different types of integration strategies : Discuss the different types of integration strategies that an organisation can undertake and support your relevant examples.

Why did you choose to pursue your education online : Question 1: Why did you choose to pursue your education online? Question 2: How has that decision helped to shape your life?

Discuss between natural law versus religious relativism : Discuss What is Religious Relativism and what some of its challenges? And What is Natural Law and what are some of its challenges?

What are various technologies employed by wireless devices : What are the various technologies employed by wireless devices to maximize their use of the available radio frequencies? Discuss methods used to secure 802.11.

BØA2042 Financial Modeling using Excel Assignment : BØA2042 Financial Modeling using Excel Assignment Help and Solution, Norwegian University of Science and Technology - Assessment Writing Service

Identify what social power is being used in the argument : Identify what social power is being used in the argument and how it is being used. Additionally, are there elements of ad hominem

Why the components you identify are necessary and important : Identify what you feel are necessary components of a strategic plan for desktop security. Explain why the components you identify are necessary and important.

Create a testing plan to manage the systems : Create a testing plan to manage the systems through QA standards. Include a testing plan for each part of the new service you've identified.

Why project was important for descartes to accomplish : Discuss and Evaluate the movie, The Matrix, in terms of the philosophical issues raised with (1) skepticism and (2) the mind-body problem.

User Account

All Pages