This problem asks you to construct a simple simulation model. If you do not own simulation software, see the options for downloading simulation software under Additional Resources.

 

R&E SUPPLIES
Facts and assumptions ($ thousands)
	Actual	Forecast	Forecast
	2017	2018	2019
Net sales	$20,613
Growth rate in sales		25%	30%
Cost of goods soldet sales		86%	86%
Gen., sell., and admin. expenseet sales		12%	11%
Long-term debt	$760	$660	$560
Current portion long-term debt	$100	$100	$100
Interest rate		10%	10%
Tax rate		45%	45%
Dividend/earnings after tax		50%	50%
Current assetset sales		29%	29%
Net fixed assets		$280	$270
Current liabilitieset sales		14.5%	14.4%
Owners' equity	$1,730
INCOME STATEMENT ($ thousands)
		Forecast	Forecast
Year		2018	2019
Net sales		$25,766	$33,496
Cost of goods sold		22,159	28,807
Gross profit		3,607	4,689
General, selling, and administrative expense		3,092	3,685
Interest expense		231	327
Earnings before tax		285	678
Tax		128	305
Earnings after tax		156	373
Dividends paid		78	187
Additions to retained earnings		78	187
BALANCE SHEET (thousands)
Current assets		$7,472	$9,714
Net fixed assets		280	270
Total assets		7,752	9,984
Current liabilities		3,736	4,823
Long-term debt		660	560
Equity		1,808	1,995
Total liabilities and owners' equity		6,204	7,378
External funding required		$1,548	$2,606

a. Problem 9 earlier asked you to extend the forecast for R&E Supplies contained in Table 3.5 through 2019. Using the same spreadsheet, simulate R&E Supplies' external funding requirements in 2019 under the following assumptions.

i. Represent the growth rate in net sales as a triangular distribution with a mean of 30 percent and a range of 25 to 35 percent.

ii. Represent the interest rate as a uniform distribution varying from 9 percent to 11 percent.

iii. Represent the tax rate as a log normal distribution with a mean of 45 percent and a standard deviation of 2 percent.

b. If the treasurer wants to be 95 percent certain of raising enough money in 2019, how much should he raise? (Grab the triangle below the frequency chart on the right and move it to the left until 95.00 appears in the "Certainty" window.)

Table 3.5

Why Are Lenders So Conservative?
Some would answer, “Too much Republican in-breeding,” but there is another possibility: low returns. Simply put, if expected loan returns are low, lenders cannot accept high risks.

Let us look at the income statement of a representative bank lending operation with, say, 100 $1 million loans, each paying 10 percent interest:

                                        ($ thousands)

Interest income (10% × 100 × $1 million) ....................... $10,000
Interest expense ................................................................     7,000
Gross income ......................................................................    3,000
Operating expenses ...........................................................    1,000
Income before tax ..............................................................    2,000
Tax at 40% rate ...................................................................       800
Income after tax $ ..............................................................    1,200

The $7 million interest expense represents a 7 percent return the bank must promise depositors and investors to raise the $100 million lent. (In bank jargon, these loans offer a 3 percent lending margin, or spread.) Operating expenses include costs of the downtown office towers, the art collection, wages, and so on.

These numbers imply a minuscule return on assets of 1.2 percent ($1.2 million/[100 × $1 million]). We know from the levers of performance that to generate any kind of reasonable return on equity, banks must pile on the financial leverage. Indeed, to generate a 12 percent ROE, our bank needs a 10-to-1assets-to-equity ratio or, equivalently, $9 in liabilities for every $1 in equity.

Worse yet, our profit figures are too optimistic because they ignore the reality that not all loans are repaid. Banks typically are able to recover only about 40 percent of the principal value of defaulted loans, implying a loss of $600,000 on a $1 million default. Ignoring tax losses on defaulted loans, this means that if only two of the bank’s 100 loans go bad annually, the bank’s $1.2 million in expected profits will evaporate. Stated differently, a loan officer must be almost certain that each loan will be repaid just to break even. (Alternatively, the officer must be almost certain of being promoted out of lending before the loans start to go bad.) So why are lenders conservative? Because the aggressive ones have long since gone bankrupt