Description

Chapter: 13 -Problem: 2 >> The Salida Salt Company is considering making a bid to supply the highway department with rock salt to drop on roads in the county during the winter. The contract will guarantee a minimum of 45,000 tons in each year, but the actual quantity may be above that amount if conditions warrant. Management believes that the actual quantity will average 60,000 tons per year. The firm will need an initial $
Answer Preview: ANSWER a. Here is a worksheet containing all the relevant information and the operating cash flow statement for each year: Year Sold Sales Revenue Variable Costs Fixed Costs Depreciation Taxes Operati…

, Chapter: 11 -Problem: 2 >> TRM Consulting Services currently has the following capital structure: New debt would mature on June 30, 2051, have a coupon rate of 7%, and would be sold for their par value of $1,000. The bonds pay interest semiannually, and flotation costs would be 2% of the selling price. The bonds would be issued on June 30, 2021. The preferred stock pays a $6 dividend each year and is currently valued at $7
Answer Preview: a. Here's a well-organized input area with all the data from the problem: Source Common Stock Preferred Stock Debt Book Value $25,000,000 $8,600,000 …

,

,

, Chapter: 8 -Problem: 3 >> Given the following set of cash flows: a. If your required rate of return is 7% per year, what is the present value of the above cash flows? Future value? b. Now, suppose that you are offered another investment that is identical, except that the cash flows are reversed (i.e., cash flow 1 is 15,000, cash flow 2 is 20,000, etc.). Is this worth more, or less, than the original investment? Why? c. If
Answer Preview: a. To calculate the present value (PV) of the cash flows, we need to discount each cash flow by the …

, Chapter: 7 -Problem: 3 >> The following is information for three local auto dealers: a. Using the information given in the above table, construct income statements for each company and the industry average. Assume that each company faces a tax rate of 25%. b. Calculate the break-even points and the degrees of operating, financial, and combined leverage for each company and the industry average. c. Compare the companies to
Answer Preview: ANSWER a. Income Statements: Company A: Amount Sales $650,000 Cost of Goods Sold $390,000 Gross Profit $260,000 Operating Expenses $180,000 Operating …

, Chapter: 13 -Problem: 3 >> Montrose Manufacturing is considering two potential investments. Each project will cost $115,000 and have an expected life of five years. The CFO has estimated the probability distributions for each project’s cash flows as shown in the following table: The company believes that the probability distributions apply to each year of the five years of the projects’ lives. Montrose Manufacturing uses t
Answer Preview: a. To calculate the expected cash flows, standard deviation, and coefficient of variation for each project, we'll use the provided probability distributions. For Project 1: Expected Cash Flows: (0 30 …

, Chapter: 2 -Problem: 1 >> Using the data presented below:  a. Recreate the income statement and balance sheet using formulas wherever possible. Each statement should be on a separate worksheet. Try to duplicate the formatting exactly. b. On another worksheet, create a statement of cash flows for 2020. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source on previous wor
Answer Preview: ANSWER a. Here are the recreated income statement and balance sheet using formulas: Income Statement: 2020 2019 Sales $412,500 $398,600 Cost of Goods $318,786 $315,300 Gross Profit $93,714 $83,300 Dep…

,

, Chapter: 12 -Problem: 1 >> You are considering two projects, A and B. Each project will cost $200,000, the WACC is 8.5%, and the projected cash flows are as follows: a. Calculate the payback period, discounted payback, NPV, PI, IRR, and MIRR. If A and B are mutually exclusive, which should be selected? b. Create an NPV profile chart for projects A and B. What is the exact crossover rate for these two projects?
Answer Preview: a. Payback period: Project A: 2 years; Project B: 3 years. Project A: Initial investment recovered i…

,

,

, Chapter: 1 -Problem: 4 >> Using the data for Johnson & Johnson (NYSE: JNJ), presented below: a. Calculate the ratio of each years’ data to the previous year for each of the above items for JNJ. For example, for the year 2018, the ratio for sales is $81,534/$76,481 5 1.06607. b. From your calculations in part a, calculate each year’s rate of growth. Using the example in part a, the ratio is 1.06607, so the percentage grow
Answer Preview: To calculate the ratios, growth rates, and average growth rates, we'll use the provided data for Johnson & Johnson (JNJ) as follows: Fiscal Year Sales EBIT Interest Expense Total Net Income Earnings p…

, Chapter: 9 -Problem: 3 >> Autonomous Vehicles, Inc. has some new products that it expects to lead to high growth in the near future. It has given analysts the following forecasts for the next three years: The firm’s debt has a current market value of $900,000 and it has $75,000 in marketable securities. There are 500,000 common shares outstanding. The expected tax rate is 25%, and the WACC is estimated to be 10%. a. Calcu
Answer Preview: a. To calculate the free cash flow for each of the next three years, we need to use the formula: Fre…

,

,

, Chapter: 1 -Problem: 3 >> Repeat Problem 2 using the data below for Kroger Co (NYSE: KR). However, this time you should create a copy of your worksheet to use as a template. Replace the data for Oracle with that of Kroger. a. Which company’s sales were growing faster over this period? Which one had the faster net income growth? b. Which company was more profitable in 2019? Which was more profitable if you take a longer vi
Answer Preview: To solve this problem, let's compare the sales growth and net income growth for Kroger Co and Oracle Corporation using the provided data. Here is the …

, Chapter: 12 -Problem: 2 >> The Ocean City water park is considering the purchase of a new log flume ride. The cost to purchase the equipment is $8,000,000, and it will cost an additional $475,000 to have it installed. The equipment has an expected life of 6 years, and it will be depreciated using a MACRS 7-year class life. Management expects to run about 150 rides per day, with each ride averaging 35 riders. The season will
Answer Preview: ANSWER To calculate the initial outlay, annual after-tax cash flow for each year, and the terminal cash flow, we need to consider the relevant costs a…

,

,

, Chapter: 8 -Problem: 6 >> Congratulations! You have just won the State Lottery. The lottery prize was advertised as an annuitized $350 million paid out in 30 equal annual payments beginning immediately. The annual payment is determined by dividing the advertised prize by the number of payments. You now have up to 60 days to determine whether to take the cash prize or the annuity.a. If you were to choose the annuitized priz
Answer Preview: a. If you were to choose the annuitized prize, you would receive $350 million divided by 30 equal an…

,

,

,

,

,

, Chapter: 9 -Problem: 2 >> As a broker at Churnem & Burnem Securities, you recommend stocks to your clients. After gathering data on Furniture Factory, you have found that its dividend has been growing at a rate of 4% per year to the current (D0) $1.35 per share. The stock is now selling for $24.50 per share, and you believe that an appropriate rate of return for this stock is 11% per year.a. If you expect that the dividend
Answer Preview: a. Calculation of highest price to purchase the stock: PO = D1/(r-g) Where, PO = Purchase price of the stock. D1 = Expected or future dividend per share. r = Rate of return of stock. g = Dividend grow…

, Chapter: 8 -Problem: 1 >> You have decided to purchase a house for $400,000 and are evaluating your options for the mortgage. Assume that your down payment will be 20% of the purchase price, payments will be made monthly, and the first payment will be made one month from today.a. What is the mortgage amount?b. If you select the 30-year mortgage, the interest rate will be 5% annually. What is the monthly payment?c. Suppose
Answer Preview: a. The mortgage amount can be calculated as the purchase price minus the down payment: Mortgage amou…

,

, Chapter: 8 -Problem: 4 >> Your five-year-old daughter has just announced that she would like to attend college. The College Board has reported that the average cost of tuition, room, board, and other expenses at public four-year colleges is $21,370 in the 2018–2019 academic year. The cost has risen 2.8% over the last year. You believe that you can earn a rate of 8% on investments to meet this goal.a. If costs continue to r
Answer Preview: a. To calculate the cost of tuition for the first year in 13 years, we can use the formula for compound interest: Future Value = Present Value * (1 + …

, Chapter: 9 -Problem: 1 >> Using the Yahoo! Finance Web site (https://finance.yahoo.com) get the current price and five-year dividend history for 3M Company. To gather this data, enter the ticker symbol (MMM) in the search box at the top of the page, and select 3m Company from the list. Record the current price from this page. Now, click on the Historical Data link. To get a table of previous dividends select Dividends Only
Answer Preview: a. To calculate the percentage change in dividends for each quarter and then the compound quarterly growth rate using the Geomean function: Obtain the …

, Chapter: 3 -Problem: 2 >> A computer problem at Mike Owjai Finance has resulted in incomplete financial statements. Management of the company has asked you to see if you can fill in the missing data. a. Recreate the financial statements as shown using formulas with the ratios given below to fill in the cells with the word “Ratio.” Use the Round function to round each of these answers to the nearest $1. b. Complete the fi
Answer Preview: Here are the completed financial statements for Mike Owjai Finance for the year ended Decem…

,

, Chapter: 14 -Problem: 5 >> You are considering investing in the following securities and have developed the probability distributions for their returns over the next year. a. Calculate the expected return and standard deviation of each security. b. Create a variance/covariance matrix for the four securities. An example of how to create a formula that uses probabilities instead of historical (equally weighted) data. c. Usin
Answer Preview: ANSWER To calculate the expected return and standard deviation of each security, we'll use the provided probability distributions. Let's start with th…

, Chapter: 10 -Problem: 1 >> You are considering an investment in the bonds of the Front Range Electric Company. The bonds pay interest semiannually, will mature in 15 years, and have a coupon rate of 6% on a face value of $1,000. Currently, the bonds are selling for $920.a. If your required return is 6.80% for bonds in this risk class, what is the highest price you would be willing to pay?b. What is the current yield of thes
Answer Preview: ANSWER a. To calculate the highest price you would be willing to pay, you need to find the present value of the bond's future cash flows using your required return. The bond pays semiannual interest f…

,

, Chapter: 8 -Problem: 5 >> You have decided to invest in a small commercial office building that has one tenant. The tenant has a lease that calls for annual rent payments of $20,000 per year for the next three years. However, after that lease expires you expect to be able to increase the rent by 4% per year for the next seven years. You plan to sell the building for $350,000 ten years from now.a. Create a table showing the
Answer Preview: a. The projected cash flows for this investment can be represented in a table as follows: Year Cash Flow 1 $20,000 2 $20,000 3 $20,000 4 $20,800 5 $21…

,

,

,

, Chapter: 7 -Problem: 1 >> Miss Ellaneous Bakery is considering the addition of a new line of pies to its product offerings. It is expected that each pie will sell for $17 and the variable costs per pie will be $11. Total fixed operating costs are expected to be $25,000. Miss Ellaneous faces a marginal tax rate of 25%, will have interest expense associated with this line of $3,500, and expects to sell about 4,500 pies in th
Answer Preview: a. Income Statement for the Pie Line's First Year: Revenue: Number of pies sold: 4,500 Selling price per pie: $17 Total revenue: 4,500 * $17 = $76,500 …

,

, Chapter: 10 -Problem: 3 >> In early July 2019, the U.S. Treasury issued three T-Bills, and they had issued a one-year T-Bill on June 20, 2019: a. Calculate the bank discount rate for each security using the formula given in the chapter and using the Disc function. b. Calculate the bond equivalent yield for each security using the formula given in the chapter and using the YieldDisc function. c. Find the most recent T-Bill
Answer Preview: a. To calculate the bank discount rate, use the formula: Bank Discount Rate = (Discount / Face Value) * (360 / Days to Maturity) For the T-Bill issued on 7/9/2019 with a maturity date of 8/6/2019: Bank Discount Rate = ((100 - 99 828111) / 100) * (360 / 28) For the 13-week T-Bill issued on 7/5/2019 with a maturity date of 10/3/2019: Bank Discount Rate = ((100 - 99 463750) / 100) * (360 / 91) For the 26-week T-Bill issued on 7/5/2019 with a maturity date of 1/2/2020: Bank Discount Rate = ((100 - 98 796778) / 100) * (360 / 182) b. To calculate the bond equivalent yield, use the formula: Bond Equivalent Yield = ((Face Value …

, Chapter: 4 -Problem: 4 >> You were recently hired to improve the financial condition of Idaho Springs Hardware, a small chain of three hardware stores in Colorado. On your first day the owner, Chuck Vitaska, told you that the biggest problem facing the firm has been periodic unexpected cash shortages that have made it necessary for him to delay wage payments to his employees. Having recently received a degree in finance, y
Answer Preview: a. To create a cash budget for Idaho Springs Hardware for June to December, we need to consider the sales, collections, inventory purchases, operating expenses, taxes, and loan payments. Here is the c…

, Chapter: 11 -Problem: 1 >> Chen Chocolate Company’s EPS in 2020 was $1.80, and in 2015 it was $1.25. The company’s payout ratio is 60%, and the stock is currently valued at $37.75. Flotation costs for new equity will be 12%. Net income in 2021 is expected to be $20 million.The company’s investment banker estimates that it could sell 15-year semiannual bonds with a coupon rate of 6.5%. The face value would be $1,000 and the
Answer Preview: a. To calculate the EPS growth rate, we can use the following formula: EPS Growth Rate = (EPS - EPS) / EPS where EPS is the EPS in the base year (2015) and EPS is the EPS in the subsequent year (2020)…

, Chapter: 12 -Problem: 5 >> The Chief Financial Officer of Eaton Medical Devices has determined that the firm’s capital investment budget will be $5,000,000 for the upcoming year. Unfortunately, this amount is not sufficient to cover all of the positive NPV projects available to the firm. You have been asked to choose which investments should be made. a. Using the Solver, determine which of the above projects should be incl
Answer Preview: a. To determine which projects should be included in the budget to maximize shareholder wealth, you can use Solver in Excel or a similar optimization …

, Chapter: 14 -Problem: 1 >> You are considering an investment in the stock market and have identified two potential stocks (XYZ and ABC) to purchase. The historical returns for the past five years are shown in the table below.  a. Calculate the average return and standard deviation of returns for each stock over the past five years. Which stock would you prefer to own? Would everyone make the same choice? b. Calculate the c
Answer Preview: a. To calculate the average return and standard deviation of returns for each stock over the past five years, we can use the following formulas: Average Return (AR) = (Sum of Returns) / (Number of Yea…

, Chapter: 6 -Problem: 5 >> Using the Forecast.ETS.Seasonality function, calculate the frequency of seasonality, if any, for AMZN, TGT, UNH, BMY, and JNJ.a. Do you get same result for each company? Why, or why not?
Answer Preview: The Forecast ETS Seasonality function is typically used in time series analysis to detect and estima…

,

, Chapter: 11 -Problem: 3 >> Suppose that TRM Consulting Services has discussed its need for capital with its investment bankers. The bankers have estimated that TRM can raise new funds in the capital markets under the following conditions: a. Using the information from the previous problem, calculate each of the breakpoints, including the break-point due to retained earnings. b. Create a chart of TRM’s marginal WACC curve u
Answer Preview: To calculate each of the breakpoints, including the break-point due to retained earnings, we need to identify the points at which the weighted average …

, Chapter: 4 -Problem: 1 >> Durango Mountain Bike Tours’ ending cash balance as of December 31, 2020 was $10,000. Its expected cash collections and payments for the next six months are given in the following table. a. Calculate the firm’s expected ending cash balance for each month. b. Assuming that the firm must maintain an ending cash balance of at least $12,000, how much must they borrow during each month? c. If the firm
Answer Preview: a. Expected Ending Cash Balance for Each Month: Month Cash Collections Cash Payments Net Cash Flow B…

,

, Chapter: 13 -Problem: 1 >> Lemon, a scooter rental company, is currently evaluating two mutually exclusive investments. After doing a scenario analysis and applying probabilities to each scenario, it has determined that the investments have the following distributions around the expected NPVs. Several members of the management team have suggested that Project A should be selected because it has a higher potential NPV. Othe
Answer Preview: a. To calculate the expected NPV for each project, we multiply the probabilities by the respective NPVs and sum the results: Expected NPV for Project …

, Chapter: 4 -Problem: 3 >> Camp and Fevurly Financial Planning have forecasted revenues for the first six months of 2021, as shown in the following table. The firm collects 70% of its sales immediately, 29% one month after the sale, and 1% are written off as bad debts two months after the sale. The firm assumes that wages and benefits paid to clerical personnel will be $9,125 per month while commissions to sales associate
Answer Preview: a. Cash Budget for January to June 2021: Month Revenue Cash Receipts Cash Disbursements Net Cash Flo…

,

, Chapter: 9 -Problem: 1 >> Bob’s Rawhide Company has a dividend payout ratio of 45%. Next year it will earn $0.75 per share and have a return on equity of 12%. The shareholders’ required return is 8%.a. Calculate the company’s growth rate of EPS.b. Using the earnings model, what is the value of the stock?c. Construct a data table that shows how the growth rate and value of the stock will change if the ROE ranges between 10%
Answer Preview: a. To calculate the company's growth rate of EPS, we can use the formula: Growth rate = Retention ratio Return on equity Given: Dividend payout ratio …

, Chapter: 12 -Problem: 4 >> Chicago Turkey is considering a new turkey farm to service its western region stores. The stores currently require 650,000 turkeys per year, and they are purchased from various local turkey farms for an average price of $8 per bird. The managers believe that their new farm would lower the cost per bird to $7, while maintaining the average selling price of $12 per bird. However, due to the centrali
Answer Preview: a. To calculate the initial outlay, we need to consider the costs associated with purchasing the land, constructing the buildings, purchasing equipmen…

, Chapter: 12 -Problem: 3 >> Arroy Snackfoods is considering replacing a five-year-old machine that originally cost $75,000. It was being depreciated using straight-line to an expected salvage value of zero over its original 10-year life, and could now be sold for $40,000. The replacement machine would cost $215,000 and have a five year expected life. It would be depreciated using the MACRS 5-year class life. The expected sal
Answer Preview: a. To analyze the replacement project, we need to calculate the operating cash flows, payback period…

,

, Chapter: 2 -Problem: 2 >> Using the data from the previous problem: Data from previous 1. Using the data presented below:  a. Create a common-size income statement for 2019 and 2020. This statement should be created on a separate worksheet with all formulas linked directly to the income statement. b. Create a common-size balance sheet for 2019 and 2020. This statement should be created on a separate worksheet with all f
Answer Preview: ANSWER To create common-size financial statements, we need to express each line item as a percentage of a common base. Let's start by creating a common-size income statement for 2019 and 2020. We'll a…

Additional Information

12 Reviews for Financial Analysis With Microsoft Excel Textbook Questions And Answers