Question

QUESTIONS 1. Complete the 1992 columns of Tables 3 through 6, disregarding for now the projected data in the 1993 and 1994 columns. If you are using the spreadsheet model, use it to complete the tables. Be sure you understand all the numbers, as it would be most embarrassing and harmful to your career) if you were asked how you got a particular number and you could not give a meaningful response. 2. Based on the information in the case and on the results of your calculations in Question 1, prepare a list of Mark X's strengths and weaknesses. In essence, you should look at the common size statements and each group of key ratios (for example, the liquidity ratios) and see what those ratios indicate about the company's operations and financial condition. As a part of your answer, use the extended Du Pont equation to highlight the key relationships 3. Recognizing that you might want to revise your opinion later, does it appear, based on your analysis to this point that the bank should lend the requested money to Mark X? Explain. 4. Now complete the tables to develop pro forma financial statements for 1993 and 1994. For these calculations, assume that the bank is willing to maintain the present credit lines and to grant an additional $6,375,000 of short-term credit effective January 1, 1993. In the analysis, take account of the amounts of inventory and accounts receivable that would be carried il inventory utilization (based on the cost of goods sold) and days sales outstanding were set at industry-average levels. Also, assume in your forecast that all of Mark X's plans and predictions concerning sales and expenses materialize, and that the firm pays no cash dividends during the forecast period. Finally, in your calculations use the cash and marketable securities account as the residual balancing figure, In responding to Questions 5 through 8. no spreadsheet model modifications are required. Answers should be based solely on the data contained in the financial statements completed in response to Question 4.

Answer 1

Answer to Question 1:

Let us calculate the Table 3 (Common Size Balance Sheet in percentages)
This is the simplest to calculate. It is just converting the figures mentioned in table 1 to a percentage of the total assets of the company. Also note than in a balance sheet, total assets = total liabilities ie both sides of the balance sheet are "balanced."

	TABLE 3
	1,992	Calcuation Formula	Calculation Steps
Assets:
Cash and Marketable Securities	4.06
Accounts Receivable	30.55	Accounts Receivable/Total Assets	29357/96102*100
Inventory	48.55
Current Assets	83.16	Current Assets/Total Assets	79922/96102*100
Land, Building, Plant, Equipment	23.80	Land, Building, Plant, Equipment/Total Assets	22874/96102*100
Accumulated Depreciation	(6.97)
Net Fixed Assets	16.84
Total Assets	100.00
Liabilities and Equity:
Short-Term Bank Loans	18.97
Accounts Payable	20.81
Accruals	7.63
Current Liabilities	47.41
Long-Term Bank Loans	9.95	Long-Term Bank Loans/Total Liabilities	9563/96102*100
Mortgage	2.43
Long-Term Debt	12.39	Long-Term Debt/Total Liabilities	11903/96102*100
Total Liabilities	59.80
Common Stock	24.21
Retained Earnings	15.99	Retained Earnings / Total Liabilities	15368/96102*100
Total Equity	40.20	Total Equity/Total Liabilities	38637/96102*100
Total Liabilities & Equity	100

Let us calculate the Table 4 (Common Size Income Statement in percentages)
This is the simplest to calculate. It is just converting the figures mentioned in table 2 to a percentage of the net sales of the company

	TABLE 4
	1,992	Calcuation Formula	Calculation Steps
Net Sales	100
Cost of Goods Sold	85.24	Cost of Goods Sold/ Net Sales	166837/195732*100
Gorss Profit	14.76	Gross Profit/Net Sales	28895/195732*100
Administrative Expenses	8.62
Depreciation	1.04	Depreciation/Net Sales	2040/195732*100
Miscenalleous expenses	2.92
Total Operating Expenses	12.59	Total Operating Expenses/Net Sales	24646/195732*100
EBIT	2.17
Interest on Short-Term Loans	0.93	Interest on ST Loans/Net Sales	1823/195732*100
Interest on Long-Term Loans	0.49	Interest on LT Loans/Net Sales	956/195732*100
Interest on mortgage	0.11
Total Interest	1.53
Before Tax Earnings	0.64
Taxes	0.26	Taxes/Net Sales	504/195732*100
Net Income	0.39
Dividends on Stocks	0.10
Additions to retained earnings	0.29

Let us now calculate table 5 (Statement of Cashflows).
The data used to make the calculations is typically taken from Tables 1 and 2

	TABLE 5
	1,992	Calcuation Formula	Calculation Steps
CashFlow from operations:
Sales	195,732
Increase in receivables	(10,895)	(Receivables 1991) - (Receivables 1992)	18462-29357
Cash Sales	184,837	Sales+Increase in receivables	195732-10895
Cost of Goods Sold	166,837
Increase in Inventories	(13,630)
Increase in accounts payable	9,492
Increase in accruals	2,231	(accruals 1992) - (accruals 1991)	7331 - 5100
Cash cost of goods	179,639	Cost of Goods - Increase in Inventory - Increase in receivables - increase in accruals- increase in accounts payable	166837 - (-10895) - (-13630)-9492-2231
Cash Margin	5,198	Cash Sales - Cash Cost of Goods Sold	184837-179639
Administrative expenses	(16,881)
Miscellaneous Expenses	(5,725)
Taxes	(504)
Net CF from Operations	(17,912)	Cash Margin + Administrative Expenses + Miscellaneous Expense + Taxes	5198-16881-5725-504
Cash Flow from fixed asset investment
Investment in fixed assets	(2,774)
Cash Flow from Financing Activity
Increase in short-term debt	13,133
Increase in long-term debt	-	(Long-Term Debt 1992) - (Long-Term Debt 1991)	9563-9563
Repayment of Mortgage	(261)
Interest Expense	(2,990)
Common Dividends	(189)
Net Cashflow from financing Activities	9,693
Increase (Decrease) in cash and marketable securities	(10,993)	Net Cash Flow from operation + Cash Flow from Fixed Asset Investment + Net Cash Flow from Financing	-17912-2774+9693

Let us calculate Table 6 which uses values from Tables 1 & 2

	TABLE 6
	1,992	Calcuation Formula	Calculation Steps
Liquidity Ratios:
Current Ratio	1.75	Current Assets/Current Liabilities	79922/45562
Quick Ratio	0.73
Debt Management Ratios:
Debt Ratio	0.78	Total Debt (short+long) / Total Equity	(11903+18223) /38637
TIE Ratio	1.42
Asset Management Ratios
Inventory Turnover (Sales)	4.91	Sales 1992 / Average Inventory (1991 & 1992)	195732/ (46659+33029) * 2
Profitability Ratios:
Return on Total Assets	0.79%	Net Income / Total Assets	755/96102
Other Factors:
Altman Z Factor	2.04	The Altman Z score is calculated based on the formula given in table 6	0.012 *(79922-45562)/96102 + 0.014 *(567/96102) + 0.033 * (4249/96102) + 0.006 * (3500000*3.67)/(11903000+18223000) + 0.999 * (195732/96102)