Assignment Prompt & Instructions
[>Accounting homework help](https://www.sweetstudy.com/fields/business-finance-accounting)
Do the uploaded assignment below
– 2 years ago
**files (1)**
– ## cf_final_exam2.docx
**## cf_final_exam2.docx**
**Remove or Replace: Header Is Not Doc Title**

## Final Exam
1. Why are forecasting and budgeting essential practices for businesses, governmental agencies, and individuals, and how do these processes help in planning for the amount, sources, and uses of cash?
2. Explain the relationship between microeconomics and macroeconomics. How do individual decisions in microeconomics, such as consumer spending and business production, affect the larger economy? Provide examples of how changes in macroeconomic factors like inflation or unemployment can influence everyday decisions made by households and firms.
3. You purchased a house on January 1, 2022, for
**$300,000**. You made a down payment of
**15%** of the house’s value, and the remaining
**85%** was financed through a
**25-year loan** with an annual percentage rate (APR) of
**4.5%**, compounded monthly. Monthly payments are to begin on January 1, 2022. You want to calculate the amount of your
**monthly mortgage payment**.
**Answer:** The formula to calculate the monthly payment for a mortgage is:
**Excel:**
1.
**Excel:** Open Excel.
1. In cell
**A1**, type Loan Amount.
1. In cell
**B1**, type 255000.
1. In cell
**A2**, type Annual Interest Rate.
1. In cell
**B2**, type 4.5%.
1. In cell
**A3**, type Number of Years.
1. In cell
**B3**, type 25.
1. In cell
**A4**, type Monthly Payment.
Now, use the
**PMT** function to calculate the monthly payment:
1. In cell
**B4**, type the formula:
scss
Copy code
=PMT(B2/12, B3*12, -B1)
Explanation of the formula:
· B2/12 is the monthly interest rate (4.5% / 12 months).
· B3*12 is the total number of monthly payments (25 years × 12 months).
· -B1 is the loan amount ($255,000). It's negative because it represents an outgoing payment.
1. Press
**Enter**.
Excel will calculate the monthly payment, which should display:
4.
**USA Widgets Inc.** issues preferred stock that pays an annual dividend of
**$4.50 per share**. The dividends are expected to grow each year at a rate of
**0.25%** (a growing perpetuity). The required rate of return for this type of stock is
**3.5%**. What is the approximate price at which this preferred stock will most likely sell today?
**Answer:**
**Excel:** Open Excel.
1. In cell
**A1**, type Dividend Today.
1. In cell
**B1**, type 4.50.
1. In cell
**A2**, type Growth Rate.
1. In cell
**B2**, type 0.25%.
1. In cell
**A3**, type Required Rate of Return.
1. In cell
**B3**, type 3.5%.
1. In cell
**A4**, type Price Today.
Now, use the formula for the price of a growing perpetuity:
1. In cell
**B4**, type the formula:
scss
Copy code
=B1 * (1 + B2) / (B3 – B2)
Explanation of the formula:
· B1 is the dividend today ($4.50).
· B2 is the growth rate (0.25%).
· B3 is the required rate of return (3.5%).
1. Press
**Enter**.
Excel will calculate the stock price, which should display:
5. A
**$2,000** bond from
**Expo Corp.** has a coupon rate of
**6%**, pays interest semiannually, and matures in
**eight years**. If the yield to maturity (YTM) is
**8%**, what is the bond’s value today?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Face Value.
1. In cell
**B1**, type 2000.
1. In cell
**A2**, type Coupon Payment.
1. In cell
**B2**, type 60.
1. In cell
**A3**, type Number of Periods.
1. In cell
**B3**, type 16.
1. In cell
**A4**, type YTM (Per Period).
1. In cell
**B4**, type 4%.
Now, calculate the bond value using the
**PV** function for the coupon payments and the face value.
For the coupon payments: 10. In cell
**A5**, type PV of Coupons. 11. In cell
**B5**, type the formula:
scss
Copy code
=PV(B4, B3, -B2, 0)
For the face value: 12. In cell
**A6**, type PV of Face Value. 13. In cell
**B6**, type the formula:
scss
Copy code
=PV(B4, B3, 0, -B1)
Finally, calculate the total bond value: 14. In cell
**A7**, type Bond Value. 15. In cell
**B7**, type the formula:
Copy code
=B5 + B6
Excel will calculate the bond's value, which should display:
6.
**Ocean Blue Ltd.** has issued preferred stock (cumulative) with a par value of
**$150.00** and an annual dividend rate of
**6.5%**. The preferred stock is currently selling for
**$50.00** per share. What is the yield or return on this preferred stock?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Par Value.
1. In cell
**B1**, type 150.
1. In cell
**A2**, type Dividend Rate.
1. In cell
**B2**, type 6.5%.
1. In cell
**A3**, type Current Price.
1. In cell
**B3**, type 50.
**Calculate the Annual Dividend:**
1. In cell
**A4**, type Annual Dividend.
1. In cell
**B4**, type the formula:
=B1*B2
1. Press
**Enter**. This will calculate the annual dividend as:
**Calculate the Yield:**
1. In cell
**A5**, type Yield or Return.
1. In cell
**B5**, type the formula:
=B4/B3
1. Press
**Enter**. The result will be:
**7.** You purchase
**150 shares** of
**ABC Corp** at
**$350 per share**. After
**four months**, you sell the shares for
**$365 per share**, and you receive a dividend of
**$1.25 per share** during that time. What is your total dollar return on the investment, including both capital gains and dividend income?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Number of Shares.
1. In cell
**B1**, type 150.
1. In cell
**A2**, type Purchase Price per Share.
1. In cell
**B2**, type 350.
1. In cell
**A3**, type Selling Price per Share.
1. In cell
**B3**, type 365.
1. In cell
**A4**, type Dividend per Share.
1. In cell
**B4**, type 1.25.
**Step 1: Calculate the Capital Gain**
1. In cell
**A5**, type Capital Gain.
1. In cell
**B5**, type the formula:
scss
Copy code
=(B3 – B2) * B1
Press
**Enter**. This will give you the capital gain of:.
**Step 2: Calculate the Dividend Income**
1. In cell
**A6**, type Dividend Income.
1. In cell
**B6**, type the formula:
Copy code
=B4 * B1
Press
**Enter**. This will give you the dividend income of:
**Step 3: Calculate the Total Dollar Return**
1. In cell
**A7**, type Total Dollar Return.
1. In cell
**B7**, type the formula:
Copy code
=B5 + B6
Press
**Enter**. This will give you the total dollar return of:
8. How does the
**standard deviation of returns** in a portfolio change as the number of stocks within the portfolio increases? Explain the role of diversification in reducing portfolio risk and whether there is a limit to the benefits of adding more stocks to the portfolio.
9. You are considering investing in a new project that requires an initial investment of
**$50,000**. The project is expected to generate cash flows of
**$15,000** at the end of each of the next
**5 years**. The required rate of return (discount rate) for this project is
**10%**. What is the
**Net Present Value (NPV)** of this investment, and should you proceed with the project?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Year.
1. In cells
**A2**
, type the years
**1**,
**2**,
**3**,
**4**, and
**5** (for the 5 years of cash flows).
1. In cell
**B1**, type Cash Flow.
1. In cells
**B2**
, enter the cash flows for each year, which is
**15,000** for each year.
1. In cell
**B7**, type Initial Investment.
1. In cell
**C7**, enter the
**initial investment** as
**-50,000** (negative because it's an outflow).
1. In cell
**A8**, type Discount Rate.
1. In cell
**B8**, enter the discount rate, which is
**10%** (0.10).
**Step 1: Calculate the NPV using the NPV function:**
1. In cell
**A9**, type NPV.
1. In cell
**B9**, type the formula:
scss
Copy code
=NPV(B8, B2:B6) + C7
·
**B8** is the discount rate (10%),
·
**B2**
are the cash flows for years 1 to 5,
·
**C7** is the initial investment (-$50,000).
1. Press
**Enter**.
Excel will calculate the NPV and display the result, which should be:.
**10. John** owns five stocks:
**United Healthcare**,
**Pfizer**,
**Johnson & Johnson**,
**CVS Health**, and
**Merck**.
**Sara** owns five stocks:
**Microsoft**,
**Coca-Cola**,
**Netflix**,
**Amazon**, and
**Nike**. Based on the industries these companies operate in, who has a more diversified portfolio, and why is diversification important for reducing risk in an investment strategy?
1
1
## image1.emf
**## cf_final_exam2.docx**
**Remove or Replace: Header Is Not Doc Title**

## Final Exam
1. Why are forecasting and budgeting essential practices for businesses, governmental agencies, and individuals, and how do these processes help in planning for the amount, sources, and uses of cash?
2. Explain the relationship between microeconomics and macroeconomics. How do individual decisions in microeconomics, such as consumer spending and business production, affect the larger economy? Provide examples of how changes in macroeconomic factors like inflation or unemployment can influence everyday decisions made by households and firms.
3. You purchased a house on January 1, 2022, for
**$300,000**. You made a down payment of
**15%** of the house’s value, and the remaining
**85%** was financed through a
**25-year loan** with an annual percentage rate (APR) of
**4.5%**, compounded monthly. Monthly payments are to begin on January 1, 2022. You want to calculate the amount of your
**monthly mortgage payment**.
**Answer:** The formula to calculate the monthly payment for a mortgage is:
**Excel:**
1.
**Excel:** Open Excel.
1. In cell
**A1**, type Loan Amount.
1. In cell
**B1**, type 255000.
1. In cell
**A2**, type Annual Interest Rate.
1. In cell
**B2**, type 4.5%.
1. In cell
**A3**, type Number of Years.
1. In cell
**B3**, type 25.
1. In cell
**A4**, type Monthly Payment.
Now, use the
**PMT** function to calculate the monthly payment:
1. In cell
**B4**, type the formula:
scss
Copy code
=PMT(B2/12, B3*12, -B1)
Explanation of the formula:
· B2/12 is the monthly interest rate (4.5% / 12 months).
· B3*12 is the total number of monthly payments (25 years × 12 months).
· -B1 is the loan amount ($255,000). It's negative because it represents an outgoing payment.
1. Press
**Enter**.
Excel will calculate the monthly payment, which should display:
4.
**USA Widgets Inc.** issues preferred stock that pays an annual dividend of
**$4.50 per share**. The dividends are expected to grow each year at a rate of
**0.25%** (a growing perpetuity). The required rate of return for this type of stock is
**3.5%**. What is the approximate price at which this preferred stock will most likely sell today?
**Answer:**
**Excel:** Open Excel.
1. In cell
**A1**, type Dividend Today.
1. In cell
**B1**, type 4.50.
1. In cell
**A2**, type Growth Rate.
1. In cell
**B2**, type 0.25%.
1. In cell
**A3**, type Required Rate of Return.
1. In cell
**B3**, type 3.5%.
1. In cell
**A4**, type Price Today.
Now, use the formula for the price of a growing perpetuity:
1. In cell
**B4**, type the formula:
scss
Copy code
=B1 * (1 + B2) / (B3 – B2)
Explanation of the formula:
· B1 is the dividend today ($4.50).
· B2 is the growth rate (0.25%).
· B3 is the required rate of return (3.5%).
1. Press
**Enter**.
Excel will calculate the stock price, which should display:
5. A
**$2,000** bond from
**Expo Corp.** has a coupon rate of
**6%**, pays interest semiannually, and matures in
**eight years**. If the yield to maturity (YTM) is
**8%**, what is the bond’s value today?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Face Value.
1. In cell
**B1**, type 2000.
1. In cell
**A2**, type Coupon Payment.
1. In cell
**B2**, type 60.
1. In cell
**A3**, type Number of Periods.
1. In cell
**B3**, type 16.
1. In cell
**A4**, type YTM (Per Period).
1. In cell
**B4**, type 4%.
Now, calculate the bond value using the
**PV** function for the coupon payments and the face value.
For the coupon payments: 10. In cell
**A5**, type PV of Coupons. 11. In cell
**B5**, type the formula:
scss
Copy code
=PV(B4, B3, -B2, 0)
For the face value: 12. In cell
**A6**, type PV of Face Value. 13. In cell
**B6**, type the formula:
scss
Copy code
=PV(B4, B3, 0, -B1)
Finally, calculate the total bond value: 14. In cell
**A7**, type Bond Value. 15. In cell
**B7**, type the formula:
Copy code
=B5 + B6
Excel will calculate the bond's value, which should display:
6.
**Ocean Blue Ltd.** has issued preferred stock (cumulative) with a par value of
**$150.00** and an annual dividend rate of
**6.5%**. The preferred stock is currently selling for
**$50.00** per share. What is the yield or return on this preferred stock?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Par Value.
1. In cell
**B1**, type 150.
1. In cell
**A2**, type Dividend Rate.
1. In cell
**B2**, type 6.5%.
1. In cell
**A3**, type Current Price.
1. In cell
**B3**, type 50.
**Calculate the Annual Dividend:**
1. In cell
**A4**, type Annual Dividend.
1. In cell
**B4**, type the formula:
=B1*B2
1. Press
**Enter**. This will calculate the annual dividend as:
**Calculate the Yield:**
1. In cell
**A5**, type Yield or Return.
1. In cell
**B5**, type the formula:
=B4/B3
1. Press
**Enter**. The result will be:
**7.** You purchase
**150 shares** of
**ABC Corp** at
**$350 per share**. After
**four months**, you sell the shares for
**$365 per share**, and you receive a dividend of
**$1.25 per share** during that time. What is your total dollar return on the investment, including both capital gains and dividend income?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Number of Shares.
1. In cell
**B1**, type 150.
1. In cell
**A2**, type Purchase Price per Share.
1. In cell
**B2**, type 350.
1. In cell
**A3**, type Selling Price per Share.
1. In cell
**B3**, type 365.
1. In cell
**A4**, type Dividend per Share.
1. In cell
**B4**, type 1.25.
**Step 1: Calculate the Capital Gain**
1. In cell
**A5**, type Capital Gain.
1. In cell
**B5**, type the formula:
scss
Copy code
=(B3 – B2) * B1
Press
**Enter**. This will give you the capital gain of:.
**Step 2: Calculate the Dividend Income**
1. In cell
**A6**, type Dividend Income.
1. In cell
**B6**, type the formula:
Copy code
=B4 * B1
Press
**Enter**. This will give you the dividend income of:
**Step 3: Calculate the Total Dollar Return**
1. In cell
**A7**, type Total Dollar Return.
1. In cell
**B7**, type the formula:
Copy code
=B5 + B6
Press
**Enter**. This will give you the total dollar return of:
8. How does the
**standard deviation of returns** in a portfolio change as the number of stocks within the portfolio increases? Explain the role of diversification in reducing portfolio risk and whether there is a limit to the benefits of adding more stocks to the portfolio.
9. You are considering investing in a new project that requires an initial investment of
**$50,000**. The project is expected to generate cash flows of
**$15,000** at the end of each of the next
**5 years**. The required rate of return (discount rate) for this project is
**10%**. What is the
**Net Present Value (NPV)** of this investment, and should you proceed with the project?
**Answer:**
**Excel:**
1. Open Excel.
1. In cell
**A1**, type Year.
1. In cells
**A2**
, type the years
**1**,
**2**,
**3**,
**4**, and
**5** (for the 5 years of cash flows).
1. In cell
**B1**, type Cash Flow.
1. In cells
**B2**
, enter the cash flows for each year, which is
**15,000** for each year.
1. In cell
**B7**, type Initial Investment.
1. In cell
**C7**, enter the
**initial investment** as
**-50,000** (negative because it's an outflow).
1. In cell
**A8**, type Discount Rate.
1. In cell
**B8**, enter the discount rate, which is
**10%** (0.10).
**Step 1: Calculate the NPV using the NPV function:**
1. In cell
**A9**, type NPV.
1. In cell
**B9**, type the formula:
scss
Copy code
=NPV(B8, B2:B6) + C7
·
**B8** is the discount rate (10%),
·
**B2**
are the cash flows for years 1 to 5,
·
**C7** is the initial investment (-$50,000).
1. Press
**Enter**.
Excel will calculate the NPV and display the result, which should be:.
**10. John** owns five stocks:
**United Healthcare**,
**Pfizer**,
**Johnson & Johnson**,
**CVS Health**, and
**Merck**.
**Sara** owns five stocks:
**Microsoft**,
**Coca-Cola**,
**Netflix**,
**Amazon**, and
**Nike**. Based on the industries these companies operate in, who has a more diversified portfolio, and why is diversification important for reducing risk in an investment strategy?
1
1
## image1.emf