SPREADSHEET ASSIGNMENT: Eastland Products, Inc.
Amexicorp, Inc.
1. Retrieve the file CFM0318P.XLS. Boldface cell A1 and enter the
appropriate values in cells C7 and C17.
2. Enter the appropriate equations, not values, into the following
cells: C20, C29, C45, C46, C47, C48, C49, D9, and D16.
3. Print the worksheet.
4. Disregard the information presented in part (g) of the
Eastland problem and restore the original balance sheet values.
Construct a new balance sheet assuming 2 million common shares
were sold to net $25 per share, with the proceeds allocated as
follows: $25 million is used to discharge current liabilities,
and $25 million is added to liquid assets. Modify cells C52,
C53, C54, and C55.
5. Print the worksheet.
6. Retrieve the file CFM2A08P.XLS. Enter the label (words or
acronym) in cell A14 which describes the computations reported
in cells B14 and D14.
7. Enter the appropriate values in the following cells: B16, D16,
B18, and D18.
8. Enter the appropriate equations, not values, into the following
cells: B20, D21, B22 (assume a tax rate of 35%), and D27.
9. Print the worksheet.
Do not print equations or the contents of each cell. The pages you submit should report final results, revealing the business output (such as dollar values) of a financial statement.
Pages must be assembled in proper sequence and be stapled or clipped together.
WORKSHEET FOR EASTLAND PRODUCTS, INC.
BALANCE SHEET Year 1 Year 2
ASSETS
Current assets $40,000,000 $60,000,000
Fixed assets, net 110,000,000
– –
Total Assets $40,000,000
= =
LIABILITIES AND EQUITY
Current liabilities $30,000,000 $20,000,000
Long-term debt 40,000,000 40,000,000
Common stock ($1 par) 5,000,000
Surplus capital (CCEP) 35,000,000
Retained earnings 55,000,000 55,000,000
– –
Total liabilities and equity $150,000,000
= =
INCOME STATEMENT
Sales $120,000,000
Cost of sales 80,000,000
–
Operating income (EBIT)
Interest 5,000,000
–
Taxable income (EBT) (5,000,000)
Taxes (40%) (2,000,000)
–
Net income (EAT) ($3,000,000)
=
Total dividends $10,000,000
Market price per share $32
Common shares issued 5,000,000
Earnings per share, year 1
Price-earnings ratio, year 1
Book value per share, year 1
Market to book ratio, year 1
Retained earnings added
New common shares issued 1,000,000
Net proceeds from new shares $30,000,000
Decrease in current liabilities $10,000,000
Increase in current assets $20,000,000
WORKSHEET FOR AMEXICORP, INC.
(a) (b)
Sales revenue $400,000,000 $400,000,000
Cost of goods sold 150,000,000 150,000,000
– –
Gross profit 250,000,000 250,000,000
Selling and administrative expenses 100,000,000 100,000,000
– –
EBITDA 150,000,000 150,000,000
Depreciation expense 0 0
– –
150,000,000 150,000,000
Interest expense 100,000,000 100,000,000
Capital gain (loss)
Dividend income 10,000,000 10,000,000
Exempt portion
– –
Total income (including dividend income) 60,000,000
Taxable income (excluding exempt income) 0
Taxes paid 0
– –
Earnings after taxes 0 60,000,000
Preferred stock dividends 10,000,000 10,000,000
– –
Earnings available for common stockholders ($10,000,000)
