Question
Crane Harbour is a newspaper company. After a particularly poor performance in 2018, the CEO, Simon Smith was removed by the board. A lifelong employee,
Crane Harbour is a newspaper company. After a particularly poor performance in 2018, the CEO, Simon Smith was removed by the board. A lifelong employee, Smith had worked his way up from the warehouse to become CEO in 2003. Smith was known as a very conservative manager. His strategy included the use of a significant cash cushion, parked in marketable securities. Crane Harbour drew on the cash when the company faced unexpected shortfalls, but it was expensive insurance. Over the past several years, the marketable securities paid next to no interest. Consistent with his cautious approach, Smith had responded to the most recent downturn in demand by paying down the companys revolving debt and renegotiating the interest on the companys outstanding debt. While the board noted Smith success in renegotiating the companys debt to cut interest expenses, he was not able to enjoy the fruits of his labours. After the company announced a loss of $2.60/ share at the 2018 annual general meeting, Smith was replaced by Hunter Lee, and he was given a mandate to return the company to profitability within five years.
In contrast to Smith, Lee has a reputation as a relentless competitor, who is unafraid to take risks. His reputation as a skilled turnaround artist is well deserved. He has successfully revived other firms and the board of Crane Harbour fully supports her agenda. One of her first acts as CEO was to liquidate the companys marketable securities. In one particularly pointed interview on BTS, Lee exclaimed Cash is trash! and he went on to explain that her predecessor had been overly cautious, and her team was not afraid to use debt responsibly. We arent reckless, but debt has a place in any organization. No company should be holding onto cash in a low interest environment. It isnt a life preserver. It is a millstone around the companys neck. He cut headcount to streamline operations and control costs and he made some immediate changes to the companys paper pricing strategy. He argued that Crane had the market power to raise prices, despite the challenging demand slump. Her strategy was met with consternation by her sales team, but he was unmoved.
1. Create a set of linked historical financial statements in Excel (including income statement, balance sheet and cash flow statement). Make sure your model is formatted appropriately (all formulas are the same colour; all constants are coded the same way; all off-sheet links are coded consistently.) Your spreadsheet model should be professional and easy to understand for the user. Include a title page or header. Use a legend so I know what your colour coding convention means.
Forecast Assumptions
Income Statement:
Sales: Management is divided on whether further price increases are feasible. As a base case, you are asked to assume that prices will not change, but volume will increase by 5% annually (for both types of paper.)
Gross Profit margin: 10% of sales each year
SG&A margin: 5% of sales each year
Depreciation: = 5.4% of current year net fixed assets, but it should not be greater than EBITDA and it should not be negative. Hint: try wrapping a MAX function around a MIN function in Excel.
Interest Expense: The interest rate on the revolver (short-term debt) and long-term debt is 4%. For simplicity, assume the interest this year is calculated on last years ending balances.
Income Tax Rate: 33% of Pre-tax Income. Assume the company does not pay any tax if Pretax Income is less than zero.
Common Stock: The Company does not plan to issue or buy back any shares. Assume that the Price /Earnings ratio will be 17 going forward this will let you estimate a stock price for future periods. The company does not plan to pay any dividends during the projection period.
Balance Sheet:
Cash: 3% of sales
Accounts Receivable: Assume A/R Days is 64 days for the forecast period.
Inventory: Assume an inventory turnover of 3.5X.
Other Current Assets: 1% of sales
Gross Property, Plant and Equipment: Assume that capital expenditures will be zero for the next 5 years. In other words, Gross PP&E is 654487.1 for every year in the forecast period.
Other Long-Term Assets: will be 17% of sales.
Accounts Payable: Assume days payables outstanding will be 48 days. (Use Average COGS, not Sales)
Long Term Debt: The company will continue to pay down $30,333.30 every year. This is part of their agreement with their bank.
Deferred Taxes and Other Liabilities: will remain constant for the next 5 years. Just project the 2021 value across the forecast period.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Intermediate Accounting Reporting And Analysis, 2017 Update
Authors: James M. Wahlen, Jefferson P. Jones, Donald Pagach
2nd Edition
1337505625, 9781337505628
