Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics
7th Edition
ISBN: 9781285418681
Author: Cliff Ragsdale
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 3, Problem 47QP
The CFO for Eagle Beach Wear and Gift Shop is in the process of planning for the company’s cash flows for the next 6 months. The following table summarizes the expected accounts receivables and planned payments for each of these months (in $100,000s).
The company currently has a beginning cash balance of $40,000 and desires to maintain a balance of at least $25,000 in cash at the end of each month. To accomplish this, the company has a number of ways of obtaining short-term funds:
- 1. Delay Payments. In any month, the company’s suppliers permit it to delay any or all payments for 1 month. However, for this consideration, the company forfeits a 2% discount that normally applies when payments are made on time. (Loss of this 2% discount is, in effect, a financing cost.)
- 2. Borrow Against Accounts Receivables. In any month, the company’s bank will loan it up to 75% of the accounts receivable balances due that month. These loans must be repaid in the following month and incur an interest charge of 1.5%
- 3. Short-Term Loan. At the beginning of January, the company’s bank will also give it a 6-month loan to be repaid in a lump sum at the end of June. Interest on this loan is 1% per month and is payable at the end of each month.
Assume the company earns 0.5% interest each month on cash held at the beginning of the month.
Create a spreadsheet model the company can use to determine the least costly cash management plan (i.e., minimal net financing costs) for this 6-month period. What is the optimal solution?
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
McGwire Aerospace expects to have net cash flow of $12 million. The company forecasts that its operating costs excluding depreciation and amortization will equal 75 percent of the company’s sales. Depreciation and amortization expenses are expected to be $5 million and the company has no interest expense. All of McGwire’s sales will be collected in cash, costs other than depreciation and amortization will be paid in cash during the year, and the company’s tax rate is 40 percent. What is the company’s expected sales?
Mr. King invested $30,000 in the First Eagle fund in year 2004. He continued to invest in the same fund from 2005 to 201 las in the following schedule:
Year 2005
$30,000
Y ear 2006
$50,000
Year 2007
$50,000
Year 2008
$150,000
Year 2011
$100,000
All the dividends distributed by the company from time to time were reinvested in the same fund. In year 2012 Mr. King received a report showing a total balance of $548,360 in his account. What is the internal Rate of return of Mr. King's investments in the mutual fund? Answer: 7% (Note: cash flows in 2009 and 2010 are 0.)
Show work in Excel
Kualoa Corporation is trying to assess the risk of two possible capital budgeting proposals. The company has developed pessimistic, most likely, and optimistic estimates of the annual cash inflows. They are given in the following table. The firm's cost of capital is 10 percent. What is the range of the annual cash inflows for Project A?
Chapter 3 Solutions
Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics
Ch. 3 - Prob. 1QPCh. 3 - Prob. 2QPCh. 3 - Prob. 3QPCh. 3 - Prob. 4QPCh. 3 - Prob. 5QPCh. 3 - Prob. 6QPCh. 3 - Prob. 7QPCh. 3 - Prob. 8QPCh. 3 - Prob. 9QPCh. 3 - Prob. 10QP
Ch. 3 - Prob. 11QPCh. 3 - Prob. 12QPCh. 3 - Prob. 13QPCh. 3 - Prob. 14QPCh. 3 - Prob. 15QPCh. 3 - Prob. 16QPCh. 3 - Tuckered Outfitters plans to market a custom brand...Ch. 3 - Prob. 18QPCh. 3 - Prob. 19QPCh. 3 - Prob. 20QPCh. 3 - Prob. 21QPCh. 3 - Prob. 22QPCh. 3 - Prob. 23QPCh. 3 - Prob. 24QPCh. 3 - Prob. 25QPCh. 3 - A manufacturer of prefabricated homes has decided...Ch. 3 - Prob. 27QPCh. 3 - Prob. 28QPCh. 3 - Prob. 29QPCh. 3 - Prob. 30QPCh. 3 - Prob. 31QPCh. 3 - Prob. 32QPCh. 3 - Prob. 33QPCh. 3 - Prob. 34QPCh. 3 - Prob. 35QPCh. 3 - Prob. 36QPCh. 3 - Prob. 37QPCh. 3 - Prob. 38QPCh. 3 - Prob. 39QPCh. 3 - Prob. 40QPCh. 3 - Prob. 41QPCh. 3 - Prob. 42QPCh. 3 - Prob. 43QPCh. 3 - A natural gas trading company wants to develop an...Ch. 3 - Prob. 45QPCh. 3 - Prob. 46QPCh. 3 - The CFO for Eagle Beach Wear and Gift Shop is in...Ch. 3 - Prob. 48QPCh. 3 - Prob. 1.1CCh. 3 - Prob. 1.2CCh. 3 - Prob. 1.3CCh. 3 - Prob. 1.4CCh. 3 - Prob. 2.1CCh. 3 - Prob. 2.2CCh. 3 - Prob. 2.3CCh. 3 - Prob. 2.4CCh. 3 - Prob. 2.5CCh. 3 - Kelly Jones is a financial analyst for Wolverine...
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, management and related others by exploring similar questions and additional content below.Similar questions
- 19 of Vision Company purchased treasury stock with a cost of $16,000 during 2017. During the year, the company paid dividends of $20,000 and issued bonds payable for proceeds of $860,000. Cash flows from financing activities for 2017 total Select one: a. $840,000 net cash inflow b. $856,000 net cash inflow c. $860,000 net cash outflow d. $824,000 net cash inflowarrow_forwardn September 2012, a 260,000 square foot Costco anchored shopping center was acquired in San Mateo, California for $36 million with a $26 million fully amortizing first mortgage loan from Union Bank. By 2018, the property had substantially appreciated in value and was worth $72 million and the owner wanted to access part or all of the increased equity value while paying little or no current taxes. The owner might: Refinance the property with a larger loan All these answers are correct Sell the property under an installment sale contract Trade the property for a property whose sale price is more than $54 million under Section 1031 of the Internal Revenue Codearrow_forwardMono Industries has a project with the following projected cash flows: Initial Cost, Year 0: P200,000 Cash flow year one: P25,000 Cash flow year two: P75,000 Cash flow year three: P150,000 Cash flow year four: P150,000 Using a 10% discount rate for this project and the NPV model, should this project be accepted or rejected?arrow_forward
- Monica Britt has enjoyed sailing small boats since she was 7 years old, when her mother started sailing with her. Today, Monica is considering the possibility of starting a company to produce small sailboats for the recreational market. Unlike other mass-produced sailboats, however, these boats will be made specifically for children between the ages of 10 and 15. The boats will be of the highest quality and extremely stable, and the sail size will be reduced to prevent problems of capsizing. Her basic decision is whether to build a large manufacturing facility, a small manufacturing facility, or no facility at all. With a favourable market, Monica can expect to make R90,000 from the large facility or R60,000 from the smaller facility. If the market is unfavourable, however, Monica estimates that she would lose R30,000 with a large facility and she would lose only R20,000 with the small facility. Because of the expense involved in developing the initial moulds and acquiring the…arrow_forwardMonica Britt has enjoyed sailing small boats since she was 7 years old, when her mother started sailing with her. Today, Monica is considering the possibility of starting a company to produce small sailboats for the recreational market. Unlike other mass-produced sailboats, however, these boats will be made specifically for children between the ages of 10 and 15. The boats will be of the highest quality and extremely stable, and the sail size will be reduced to prevent problems of capsizing. Her basic decision is whether to build a large manufacturing facility, a small manufacturing facility, or no facility at all. With a favourable market, Monica can expect to make R90,000 from the large facility or R60,000 from the smaller facility. If the market is unfavourable, however, Monica estimates that she would lose R30,000 with a large facility and she would lose only R20,000 with the small facility. Because of the expense involved in developing the initial moulds and acquiring the…arrow_forwardSachs Brands's defined benefit pension plan specifies annual retirement benefits equal to 1.5% × service years × final year's salary, payable at the end of each year. Angela Davenport was hired by Sachs at the beginning of 2007 and is expected to retire at the end of 2041 after 35 years' service. Her retirement is expected to span 18 years. Davenport's salary is $83,000 at the end of 2021 and the company's actuary projects her salary to be $245,000 at retirement. The actuary's discount rate is 9%. (FV of $1, PV of $1, FVA of $1, PVA of $1, FVAD of $1 and PVAD of $1) (Use appropriate factor(s) from the tables provided.) At the beginning of 2022, the pension formula was amended to: 1.60% × Service years × Final year's salary The amendment was made retroactive to apply the increased benefits to prior service years. Required: 1. What is the company's prior service cost at the beginning of 2022 with respect to Davenport after the amendment described above?2. Since the amendment occurred at…arrow_forward
- You are managing a software project with an initial budget estimate of 5 million USD. During interim cost and schedule performance analysis, you figured out that: You should have spent $650,000 till now based on your initial plans and 1,500 man/days of scheduled activities You spent $425,000 till now and completed 600 man/days of scheduled activities which should have cost $525,000 based on your initial plans. You re-estimated the budget required for the remaining work to be done as $4,500,000. Identify: BAC PV AC EV ETC Calculate: CPI SPI CV SV VAC EAC TCPI Once you've calculated this, what does it mean for your project and your stakeholders? Note: You won't be able to view anyone else's response until you've posted yours. Also, feel free to use Google, if needed to help give you further clarity on the formulas being used. Be sure to submit an initial, substantive post by Friday at 11:59 p.m. CST.arrow_forwardThe following is a breakdown of supplying and installing a piece of equipment to a project owner, atthe time of contract: EGPCost of equipment 250,000Manpower (installation) 50,000Overhead and profits 70,000i – Calculate the price of supplying and installing the equipmentii – If the supply and installing of the equipment was delayed by the project owner, calculate theescalated price of works based on the FIDIC Red Book 1990 formula given:Updated cost of equipment 270,000 EGPBase price index of manpower 123.2Updated price index of manpower 144.9arrow_forwardConoly Co. has identified an investment projectwith the following cash flows. If the discount rate is 10 percent, what is the present valueof these cash flows? What is the present value at 18 percent? At 24 percent?Year Cash Flow1 $ 9602 8403 9354 1,350arrow_forward
- For this question, please use the standard normal distribution table, Table A2. It has been estimated that the mean time required to complete a project is 20 weeks and the variance of project completion time is 9 weeks. What is the probability that the project will be completed in 23 weeks?arrow_forwardD’Unique Enterprise, a company that manufactures hair growth oil is preparing for its upcoming financial year (2021). D’Unique’ is seeking a loan from JNT Bank to purchase equipment that will be used to package the oils in anticipation of sales. The loans officer at the bank is requesting the company’s upcoming financial year’s budget to assess their cash position to repay the loan A. Sales/Collection Sales unit in the fourth quarter of 2020 will be 2500 units and is expected to increase by 500 units over each quarter in 2021. Selling price is $60 per unit. All sales are on account. Management expects future sales collection to follow past experiences. Customers pay 60% in the quarter of sales and 40% in the quarter following sales. Accounts receivable for December 31, 2020 is expected to be $60,000. B. Production To reduce the risk of a stock-out or idle time, D’Unique Co. has maintained an ending inventory policy of 20% of the following quarter’s sales unit. C.…arrow_forwardProblem 2: You go to the racetrack and are choosing between 2 horses: Belle and Jeb (you are at the racetrack, so you will bet on one of these two horses). Betting on either horse will cost you $1, and the payoffs are as follows: • Bet on Belle: you will be paid $2 if she wins (or a net profit of $1). You believe she has a 75% chance of winning. • Bet on Jeb: you will be paid $9 if he wins (for a net profit of $8). You believe that he has a 20% chance of winning. Prior to making your decision of which horse to bet on, someone comes and offers you gambler’s “insurance.” If you agree to the gamblers “insurance,” they pay you $1 immediately and you agree to pay them 50% of the profit on your winnings (that is, $0.50 if Belle wins, and $4 if Jeb wins). What should you do? Answer this question by: a) Creating a decision tree for this scenario, making sure you label all branches and include all probabilities and consequences. b) Solving the decision tree using EMV and stating the optimal…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Practical Management ScienceOperations ManagementISBN:9781337406659Author:WINSTON, Wayne L.Publisher:Cengage,
Practical Management Science
Operations Management
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:Cengage,
Single Exponential Smoothing & Weighted Moving Average Time Series Forecasting; Author: Matt Macarty;https://www.youtube.com/watch?v=IjETktmL4Kg;License: Standard YouTube License, CC-BY
Introduction to Forecasting - with Examples; Author: Dr. Bharatendra Rai;https://www.youtube.com/watch?v=98K7AG32qv8;License: Standard Youtube License