Please build the following model in your shared Google Sheet that I shared with you, and...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Please build the following model in your shared Google Sheet that I shared with you, and go through the steps below. MODEL DETAILS BOND MODEL Your company wants to issue 30 year fixed bonds in the debt markets to raise money. Your model has the following two outputs: • Price of the bonds you are issuing How much money will be raised Your model requires the following inputs: • number of bonds issued principal per bond ● • coupon rate Yield-to-Maturity Assume the bonds pay coupons annually. STOCK MODEL Your company is looking to raise money by issuing shares of stock. You build a model with the following output: Price of 1 share of stock • Total money raised from stock issuance Your model has the following inputs: Dividend at year 0 Dividend Growth rate during years 1-10 2nd Stage Terminal dividend growth rate Expected return • Total shares of stock ● ● • ● STEP 2: Build Model Please build the model so that it is user friendly, represents all cash flows on a timeline, and directly solves the quiz questions (see rubric below). If you are unable to build the model, you may answer the selection "watch video" on the first quiz question, which will show you a step-by-step video (you will lose 3 points if you select "watch video"). If you do not need to watch the video, then select "do not watch video" and you will not get any points deducted from the quiz. BOND MODEL If you have built the model correctly, you will have a total amount raised of $1,098,908 given the following inputs for a 30 year, fixed bond, paid annually: • number of bonds issued = 2,000 principal per bond = $1,000 • coupon rate = 2.4% Yield-to-Maturity = 5.5% STOCK MODEL If you have built the model correctly, you will have a total amount raised of $19,872,064 given the following inputs: • Dividend at year 0 = $3.55 • Dividend Growth rate during years 1-10 = 5.5% • 2nd Stage Terminal dividend growth rate = 3.5% Expected return =8.9% • Total shares of stock = 250,000 Inputs: Number of bonds issued Principal per bond Coupon rate Yield-to-Maturity Coupon Payment Principal Total Cashflows Bond Price Total Money Raised YEARS 2,000 1,000 2.40% 5.50% OOOO 1 24 0 24 2 3 4 5 6 Please build the following model in your shared Google Sheet that I shared with you, and go through the steps below. MODEL DETAILS BOND MODEL Your company wants to issue 30 year fixed bonds in the debt markets to raise money. Your model has the following two outputs: • Price of the bonds you are issuing How much money will be raised Your model requires the following inputs: • number of bonds issued principal per bond ● • coupon rate Yield-to-Maturity Assume the bonds pay coupons annually. STOCK MODEL Your company is looking to raise money by issuing shares of stock. You build a model with the following output: Price of 1 share of stock • Total money raised from stock issuance Your model has the following inputs: Dividend at year 0 Dividend Growth rate during years 1-10 2nd Stage Terminal dividend growth rate Expected return • Total shares of stock ● ● • ● STEP 2: Build Model Please build the model so that it is user friendly, represents all cash flows on a timeline, and directly solves the quiz questions (see rubric below). If you are unable to build the model, you may answer the selection "watch video" on the first quiz question, which will show you a step-by-step video (you will lose 3 points if you select "watch video"). If you do not need to watch the video, then select "do not watch video" and you will not get any points deducted from the quiz. BOND MODEL If you have built the model correctly, you will have a total amount raised of $1,098,908 given the following inputs for a 30 year, fixed bond, paid annually: • number of bonds issued = 2,000 principal per bond = $1,000 • coupon rate = 2.4% Yield-to-Maturity = 5.5% STOCK MODEL If you have built the model correctly, you will have a total amount raised of $19,872,064 given the following inputs: • Dividend at year 0 = $3.55 • Dividend Growth rate during years 1-10 = 5.5% • 2nd Stage Terminal dividend growth rate = 3.5% Expected return =8.9% • Total shares of stock = 250,000 Inputs: Number of bonds issued Principal per bond Coupon rate Yield-to-Maturity Coupon Payment Principal Total Cashflows Bond Price Total Money Raised YEARS 2,000 1,000 2.40% 5.50% OOOO 1 24 0 24 2 3 4 5 6
Expert Answer:
Answer rating: 100% (QA)
It appears youre looking to build a financial model for bonds and stocks to determine how much money will be raised by issuing them Unfortunately the image of the Google Sheet provided seems incomplet... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these finance questions
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
On January 1, 2019, Chiz Company acquired equipment to be used in its manufacturing operations. The equipment has an estimated useful life of 10 years and an estimated residual value of P50,000. The...
-
Explain why stockholders equity is increased by revenues and decreased by expenses.
-
True or False? A recursive function must always contain both a base case and a general case, although the base case may be empty.
-
Tyler Companys ASC 932-235 disclosures included the following information: REQUIRED: Using the information for Tyler Company in problems 13, 14, and 15 and in this problem: a. Compute the value of...
-
Kasey Hartman is the controller for Wholemart Company, which has numerous long- term investments in debt securities. Wholemarts investments are mainly in 5-year bonds. Hartman is preparing its year-...
-
2. What is the computational complexity of the following function Mystery1? int Mystery1(int n) { int sum = 0; for (int i=0; i
-
Refer to Fig. 4.40. 8,0 in Semicircular hatch 10 in 26 in 20-in radius Ethylene glycol sg = 1.10 10 in Tank is symmetrical 30
-
Calculate the following: Mixing capacity is doughnuts per day Total extrusion time (setup + run time) per batch Extrusion capacity Use the information below and in the following table to answer...
-
PMR (based on Colin Drury, Management Accounting for Business) A division of PMR plc operates a small private aircraft that carries passengers and small parcels for other divisions. In the year ended...
-
What is the most effective way to position and execute an e marketing strategy for social media?
-
Review the Learning Resources on legal and criminal approaches to substance use and on drug treatment courts. Use the National Drug Court Resource Center site to explore how drug treatment courts...
-
XYZ is a retailer and sells 149,000 units per year. It purchases from a single supplier. Fixed costs per order are $930 and carrying cost is $7 per unit. How many units should XYZ purchase per order?...
-
Code of ethics You launched your own project management consulting firm 10 years after graduating from Lambton College and working in the private sector. Months after opening your business you...
-
Consider a two-period binomial tree model with So = 4, u = 2, d =, (i.e., S = uSo and st = d.So) and take the one-period simple interest rate r = ($1 of today becomes $ after one period.) so that...
-
Suppose the market is semistrong form efficient. Can you expect to earn excess returns if you make trades based on? a. Your brokers information about record earnings for a stock? b. Rumors about a...
-
How much of each of the following prizes or awards is taxable? a. Cheline received a $50,000 gift bag at the Oscars in 2012. b. Jon received a gold watch worth $350 for 25 years of service to his...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family. The Incisors own a rental beach house in Hawaii. The beach house was rented for the full year during 2012...
-
Marty is a sales consultant. Marty incurs the following expenses related to entertainment of his clients in 2012: Dues to a country club......................................................$4,500...
-
A surface with \(N_{0}\) adsorption centers has \(N\left(\leq N_{0}ight)\) gas molecules adsorbed on it. Show that the chemical potential of the adsorbed molecules is given by \[ \mu=k T \ln...
-
Assuming that the latent heat of vaporization of water \(L_{\mathrm{V}}=2260 \mathrm{~kJ} / \mathrm{kg}\) is independent of temperature and the specific volume of the liquid phase is negligible...
-
Define a quantity \(J\) as \[ J=E-N \mu=T S-P V \] Show that for a system in the grand canonical ensemble \[ \overline{(\Delta J)^{2}}=k T^{2} C_{V}+\left\{\left(\frac{\partial U}{\partial...
Study smarter with the SolutionInn App