(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…), =FV(…), =PMT(…), =NPER(…), =RATE(…), =PRICE(…) or =YIELD(…) whenever applicable. Please show me the EXCEL functions that was used to help me better understand was equals what. Using Excel finance formulas

Case 3:

We find the data for a municipal bond issued by the Illinois state government.

The bond’s “last trade date” (i.e., settlement date) is June 05, 2019.

The bond’s “maturity date” is March 14, 2054.

The bond’s “coupon rate” is fixed as “5.000%” per year.

The bond’s coupon “payment frequency” is “semi-annual”.

The bond’s “last trade yield” (i.e., yield-to-maturity) is quoted as “4.280%” per year.    

(a) Based on the aforementioned settlement date, maturity date, coupon rate, coupon payment frequency and yield to maturity, what shall be the corresponding bond PRICE (relative to redemption par of 100)?

(b) Assumes that the Fed suddenly tightens its monetary policy now, causing interest rates to rise across financial markets. The aforementioned IL municipal bond’s yield-to-maturity also rises from 4.280% to “5.280%” per year. Will the bond PRICE go up or go down then? By how much?

(c) Assumes that the Fed suddenly loosens its monetary policy now, causing interest rates to rise across financial markets. The aforementioned IL municipal bond’s yield-to-maturity also drops from 4.280% to “3.280%” per year. Will the bond PRICE go up or go down then? By how much?

(d) Based on your answers to (b) and (c), is there a positive, negative or zero association between bond YIELD and its PRICE? (Hint: Positive association means “moving in the same direction”, negative association means “moving in the opposite directions”, while zero association means “one moves but the other does not get affected”.)

The aggregate-demand (AD), short-run aggregate supply (AS), and long-run aggregate-supply (AS_LR) schedules are as follows. The schedules show the GDP price deflator (P) versus real GDP (Q), with Q measured in trillions of constant dollars.

1. GRAPHS: Graph the AD, AS, and AS_LR curves in the same diagram. Be sure to label the curves and the axes. Indicate potential output (Q_f) on the Q axis.

2. SHORT-RUN AND LONG-RUN AGGREGATE SUPPLY, IN GENERAL: Explain the difference in shape between the AS and AS_LR curves in general.

Note: “in general” means not just for this economy but for any economy.

3. SHORT-RUN AND LONG-RUN EQUILIBRIUM, IN GENERAL: State the general conditions for short-run equilibrium and for long-run equilibrium. Which one implies the other?

Note: “general” means not just for this economy but for any economy.

4. SHORT-RUN EQUILIBRUM, THIS ECONOMY: What is the short-run equilibrium price level? Explain your answer. What is the short-run equilibrium Q? Explain your answer. Show this short-run equilibrium price and output on the graph. Suppose that P is initially at 90. This implies that there is either excess demand or excess supply of Q—which one? And what is the amount of this excess demand or excess supply? Then explain the process of eliminating the excess demand or supply, that is, the process to reach short-run equilibrium.

5. LONG-RUN EQUILIBRIUM, THIS ECONOMY: What is long-run equilibrium GDP (Q)? Explain your answer. Assuming that the AD curve does not shift, what is the long-run equilibrium price level (P)? Explain your answer. Show the long-run equilibrium price and output on the graph. Beginning at short‑run equilibrium, describe the process to long-run equilibrium.

Abbott Lamp Corporation manufactures Mountain Dew Lamps.   Abbott Lamp Corporation has the following historical cost behavior data:

Other 2018 estimated information is as follows:

Rent on production facility                 $100

Factory supervisor salary                    $100

Depreciation on factory equipment    $200

Rent on corporate headquarters          $300

Corporate HQ salaries                         $1,000

Total                                                    $1,700

Abbott Lamp also estimates the following with regard to direct materials and direct labor:

Direct Materials:

1 mountain can/mountain dew lamp @ $2/mountain dew can;

.25 pieces of paper/mountain dew lamp @$4/piece of paper;

Direct Labor

.2 dlh/mountain dew lamp @ $10/dlh

Abbott Lamp Corporation manufactures Mountain Dew Lamps.   Abbott Lamp Corporation has the following historical cost behavior data:

Other 2018 estimated information is as follows:

Rent on production facility                 $100

Factory supervisor salary                    $100

Depreciation on factory equipment    $200

Rent on corporate headquarters          $300

Corporate HQ salaries                         $1,000

Total                                                    $1,700

Abbott Lamp also estimates the following with regard to direct materials and direct labor:

Direct Materials:

1 mountain can/mountain dew lamp @ $2/mountain dew can;

.25 pieces of paper/mountain dew lamp @$4/piece of paper;

Direct Labor

.2 dlh/mountain dew lamp @ $10/dlh

1. A. Estimate the VARIABLE predetermined overhead rate using the high-low method. Using the high-low method, what does the y-intercept ‘mean’ in this case?

B. Using your answer from 1 above (using BOTH the VPDOH and the y-intercept), compute the breakeven point in terms of total # of mountain dew lamps that need to be sold to break even. Assume that the price per Mountain Dew Lamp is $20/mountain dew lamp and that capacity is NOT an issue.

C. Calculate the # of mountain dew lamps that must be sold in order to achieve a goal operating income of $10,000 (and still use your answer from part 1 above);

ACST201 Spreadsheet Project Task 3

Today is 1 August 2018. Jimmy is 30 years old today and he is considering purchasing 5,000 units of XYZ shares today (XYZ’s current share price is $20). Jimmy will use his own savings to cover 20% of the purchase cost (i.e., $20,000) and he is planning to borrow the remaining 80% of the purchase cost (i.e., $80,000) using a 5-year personal loan (it starts from 1 August 2018) from MQU Bank.

Jimmy now has two loan package to choose between.

• Package 1.

– Jimmy will make 60 monthly repayments at the beginning of each month over the following five years (from 1 August 2018 to 31 July 2023) with the first payment being made today. This loan needs to be fully repaid by the end of 5 years (i.e., when Jimmy is 35 years old.).

– This package has an annual fee of $200. The package fee is paid on 1 August of each year during the following five years period (from 1 August 2018 to 31 July 2023). The first one being paid today.

– The interest rate of this package is j12 = 10% p.a.

• Package 2.

– Jimmy will make 60 monthly repayments at the beginning of each month over the following five years with the first payment being made today. This loan needs to be fully repaid by the end of 5 years (i.e., when Jimmy is 35 years old.).

– Jimmy can have a one year interest-only-period at the beginning of the mortgage. Jimmy’s repayments will be interest-only1 for the first year (i.e., first 12 payments will be interest-only payments), followed by payments of principal plus interest for the following 4 years.

– This package has an annual fee of $400. The package fee is paid on 1 August of each year during the following five year period (from 1 August 2018 to 31 July 2023). The first one being paid today.

– The interest rate of this package is j12 = 12% p.a.

Jimmy also plans to sell all the XYZ shares in 5 years’ time (on 1 August 2023). He predicts that the XYZ share price will grow at a rate of y% p.a. Jimmy assumes that

y = the Australian 10-year Government Bond Yield for 2017 + 10%.

You need to use FactSet to find the Australian 10-year Government Bond Yield for 20172. Jimmy assumes that XYZ shares will pay a dividend on 1 January and 1 July of each year. Jimmy predicts that there are two potential outcomes for the dividend amount.

Outcome 1: the dividend amount is assumed to be $1 on 1 January 2019 and will increase at a rate of 5% per half-year.

Outcome 2: the dividend amount is assumed to be $3 on 1 January 2019 and will increase at a rate of 2% per half-year.

[12 marks]

– Calculate the loan repayment amount (excluding the annual fee) for each

month of package 1 and package 2.

– Use Goal Seek to find the net borrowing cost for package 1 and package 2 by including the annual fee (expressed as a rate p.a. compounded monthly).

– Use a bar or column chart to compare the loan repayment amount of package 1 and package 2 over the five-year loan period.

[8 marks]
– Calculate the share price on 1 August 2023.

2 For example, if the XYZ share price is 30 on 1 August 2018 and y is assumed be 15%, the XYZ share price will be 30 from 1 August 2018 to 31 July 2019 and will be 30 × (1 + 15%) from 1 August 2019 to 31 July 2020.

– Calculate the accumulated dividend value for outcomes 1 and 2. Assume a reinvestment rate of 0.5% per month.

– Calculate the holding period rate for outcome 1 and 2. Please refer to the week 6 lecture for the holding period rate calculation. You can consider that the initial investment cost is $100,000 and the interest payment of this investment is the dividend payments. Express your answer as an annual rate of compound interest.

c. [10 marks] The cash outflow of this investment has been analysed in part a and the cash outflow of this investment has been analysed in part b. It is noticed that there are four potential outcomes for Jimmy: loan package 1 with dividend outcome 1, loan package 1 with dividend outcome 2, loan package 2 with dividend outcome 1 and loan package 2 with dividend outcome 2.

– Find the net cash flow in each month from August 2018 to July August 2023 (inclusive) for these four potential outcomes.

– Calculate the present value of the net cash flow for these four potential outcomes. Assume that we use 5% p.a. to find the present value.

– Use a bar or column chart to compare the present value of net cash flows for the four potential outcomes.

100 tons of MSW is combusted per day at a W.T.E. facility.

Assume the MSW is 100% dry volatile solids with the following molar ratio: C₉H₂O₄

Calculate the total oxygen demand of this 100 tons of waste in whole standard cubic feet per minute.

Mr. Antony inherited the following securities on his uncle’s death:

Compute the current value of his uncle’s portfolio.