Question

Hint: You can use the excel template for finish the exercise.

Question 1: Cash budgeting and Pro-forma financial statement

Short-Term Financial Planning] Artero Corporation is a traditional toy products retailer that recently also started an Internet-based subsidiary that sells toys online. A markup is added on goods the company purchases from manufacturers for resale. Swen Artero, the company president, is preparing for a meeting with Jennifer Brown, a loan officer with First Banco Corporation, to review year end financing requirements. After discussions with the company’s marketing manager Rolf Eriksson and finance manager Lisa Erdinger, sales over the next three months were forecasted as follows.

All sales are made on credit terms of net 30 days and are collected the following month and no bad debts are anticipated. The accounts receivable on the balance sheet at the end of September thus will be collected in October. The October sales will be collected in November, and so on. Inventory on hand represents a minimum operating level (or “safety” stock), which the company intends to maintain. Cost of goods sold average 80 percent of sales. Inventory is purchased in the month of sale and paid for in cash. Other cash expenses average 7 percent of sales. Depreciation is $10,000 per month. Assume taxes are paid monthly and the effective income tax rate is 40 percent for planning purposes.

The annual interest rate on outstanding long=term debt and bank loans (notes payable) is 12%. There are no capital expenditures planned during the period, and no dividends will be paid. The company’s desired end-of-month cash balance is $80,000. The president hopes to meet any cash shortages during the period by increasing the firm’s notes payable to the bank. The interest rate on new loans will be 12%.

A. Prepare monthly pro forma income statements for October, November, and December, and for the quarter ending December 31, 2014.

B. Prepare both a monthly cash budget and pro forma statements of cash flows for the October, November, and December 2014.

C. Describe your findings and indicate the maximum amount of bank borrowing that is needed.

A.
Income Statement for Arturo Corporation
(Thousands of Dollars)

Quarter
Sep. 2014 Oct. 2014 Nov. 2014 Dec. 2014 Ending Dec. 31, 2014
Sales $. 700 $. 1,000 $. 1,500 $ 3,000 $. 5,500
COGS 560 800 1,200. 2,400. 4,400
Gross Margin 140 200 300 600 1,100
Depreciation 10 10 10 10 30
Other Expense 49 70 105 210 385
Interest 12 12 15 18 45
EBT 69 108 170 362 640
Taxes (40%) 28 43 68 145 256
Net Income 41 65 102 217 384

B.
Cash Budget
($ in Thousands)

Oct.31,2014 Nov.30,2014 Dec.31,2014
Collections from Rec. $. 700 $ 000 $. 1,500
Purchases 800 1,200 2,400
Interest Payments 12 15 18
Tax Payments 43 68 145
Other Expenses 70 105 210
Net Monthly Cash Flow -225 -388 -1,273
Beginning Cash $ 50 $ 80 $ 80
Ending Balance Before
Borrowing -175 -308 -1,193
Cash Bal. Required 80 80 80
Addl. Funds Needed 225 388 1,273
Cumulative Funds Need 225 643 1,916

Statement of the Cash Flow
($ in Thousands)

10/31/2014 11/30/2014 12/31/2014
Cash Flow From Operations
Net Income $ 65 $ 102 $ 217
+Depreciation 10 10 10
-Increase In A/R 300 500 1,500
-Increase In Inventory 0 0 0
Cash Flow From
Operations -225 -388 -1,273
Cash Flow From Inv. 0 0 0
Cash Flow From Fin. 0 0 0
Total Cash Flow -225 -388 -1,273

Beginning Cash Bal. 50 80 80
Required Balance 80 80 80
Addl. Funds Needed 255 388 1,273
Cum. Funds Needed 255 643 1,916
Ending Cash Balance 80 80 80

C.
The maximum amount of bank borrowing that is needed is $1,916,000.
Question 2. Capital budgeting with percentage of sales method

The Minoso Corporation anticipates a 20 percent increase in sales for 2014 over its 2013 level. Minoso is currently operating at full capacity and thus expects to increase its investment in both current and fixed assets in order to support the increase in forecasted sales.

Minoso Corporation
Income Statement for December 31, 2013
(Thousands of Dollars)
_________________________________
Sales $15,000
Operating expenses -13,000
EBIT 2,000
Interest 400
EBT 1,600
Taxes (40%) 640
Net income 960
Cash dividends (40%) 384
Added retained earnings $576

Balance Sheet as of December 31, 2013
(Thousands of Dollars)
______________________________________________________________________________
Cash $ 1,000 Accounts payable $ 1,600
Accounts receivable 2,000 Bank Loan 1,800
Inventories 2,200 Accrued liabilities 1,200
Total current assets 5,200 Total current liabilities 4,600
Long-term debt 2,200
Fixed assets, net 6,800 Common stock 2,400
Total assets $12,000 Retained earnings 2,800
Total liabilities & equity $12,000 ______________________________________________________________________________

Estimate the additional funds needed (AFN) for 2013 using the formula or equation method that is based on constant “percent of sales” relationships.

Current Net sales NS0 ?
Total assets TA0 ?
Accounts payable AP0 ?
Accrued liabilities AL0 ?
Net Income NI0 ?
Retention ratio RR0 ?

Next period Sales growth rate g 20%
NS1 NS0 * (1+g) ?

Change NS1 - NS0 ∆NS ?

AFN Change in TA, or ΔTA (TA0 / NS0) * ∆NS ?
Δ (AP + AL) ((AP0 + AL0)/ NS0) * ∆NS ?
addition to retained earnings (NS1 * (NI0 / NS0) * RR0) ?
(ΔTA) - Δ (AP+AL) - (addition to Retained earnings) ?

Question 3. Short-term financing planning
Capstan Autos operated an East Coast dealership for a major Japanese car manufacturer. Capstan’s owner, Sidney Capstan, attributed much of the business’s success to its no-frills policy of competitive pricing and immediate cash payment. The business was basically a simple one—the firm imported cars at the beginning of each quarter and paid the manufacturer at the end of the quarter. The revenues from the sale of these cars covered the payment to the manufacturer and the expenses of running the business, as well as providing Sidney Capstan with a good return on his equity investment.
By the fourth quarter of 2018 sales were running at 250 cars a quarter. Since the average sale price of each car was about $20,000, this translated into quarterly revenues of 250 × $20,000 = $5 mil- lion. The average cost to Capstan of each imported car was $18,000. After paying wages, rent, and other recurring costs of $200,000 per quarter and deducting depreciation of $80,000, the company was left with earnings before interest and taxes (EBIT) of $220,000 a quarter and net profits of $140,000.
The year 2019 was not a happy year for car importers in the United States. Recession led to a general decline in auto sales, while the fall in the value of the dollar shaved profit margins for many dealers in imported cars. Capstan more than most firms foresaw the difficulties ahead and reacted at once by offering 6 months’ free credit while holding the sale price of its cars constant. Wages and other costs were pared by 25% to $150,000 a quarter, and the company effectively eliminated all capital expenditures. The policy appeared successful. Unit sales fell by 20% to 200 units a quarter, but the company continued to operate at a satisfactory profit (see table).
The slump in sales lasted for 6 months, but as consumer confidence began to return, auto sales began to recover. The company’s new policy of 6 months’ free credit was proving sufficiently
popular that Sidney Capstan decided to maintain the policy. In the third quarter of 2019 sales had recovered to 225 units; by the fourth quarter they were 250 units; and by the first quarter of the next year they had reached 275 units. It looked as if by the second quarter of 2020 the company could expect to sell 300 cars. Earnings before interest and tax were already in excess of their previous high, and Sidney Capstan was able to congratulate himself on weathering what looked to be a tricky period. Over the 18-month period the firm had earned net profits of over half a million dollars, and the equity had grown from just over $1.5 million to about $2 million.
Sidney Capstan was first and foremost a superb salesman and always left the financial aspects of the business to his financial manager. However, there was one feature of the financial statements that disturbed Sidney Capstan—the mounting level of debt, which by the end of the first quarter of 2020 had reached $9.7 million. This unease turned to alarm when the financial manager phoned to say that the bank was reluctant to extend further credit and was even questioning its current level of exposure to the company.
Mr. Capstan found it impossible to understand how such a successful year could have landed the company in financial difficulties. The company had always had good relationships with its bank, and the interest rate on its bank loans was a reasonable 8% a year (or about 2% a quarter). Surely, Mr. Capstan reasoned, when the bank saw the projected sales growth for the rest of 2020, it would realize that there were plenty of profits to enable the company to start repaying its loans.
Mr. Capstan kept coming back to three questions:
1) Was his company really in trouble?
2) Could the bank be right in its decision to withhold further credit?
3) And why was the company’s indebtedness increasing when its profits were higher than ever?

Please give your answer.