Problem:
Stetson Skydiving Adventures
Income Statements
For the years 2010 and 2011
2011 2010
Sales $3,500,000 $3,230,000
Cost of Goods $2,273,300 $2,162,500
Gross Profit 1,226,700 1,067,500
Depreciation 84,000 71,000
Selling & Admin. Expense 869,800 835,000
Lease Expense 65,000 65,000
Net Operating Income 207,900 96,500
Interest Expense 112,000 68,000
Earnings Before Taxes 95,900 28,500
Taxes 33,565 9,975
Net Income $62,335 $18,525
Notes:
Tax Rate 35.00% 35.00%
Shares 50,000 40,000
Earnings per Share $1.25 $0.46
Stetson Skydiving Adventures
Balance Sheet
For the Year Ended December 31, 2011
2011 2010
Assets
Cash $52,000 $41,000
Marketable Securities 2,435 $21,000
Accounts Receivable 420,000 372,000
Inventory 515,000 420,000
Total Current Assets 989,435 854,000
Gross Fixed Assets 2,680,000 2,170,000
Accumulated Depreciation 569,000 485,000
Net Plant and Equipment 2,111,000 1,685,000
Total Assets $3,100,435 $2,539,000
Liabilities & Owners Equity
Accounts Payable $505,000 $290,000
Accrued Expenses 35,000 30,000
Total Current Liabilities $540,000 $320,000
Long-term Debt 1,171,000 1,061,000
Total Liabilities 1,711,100 1,381,000
Common Stock ($2 par) 100,000 80,000
Additional Paid-in-Capital 691,000 542,000
Retained Earnings 598,335 536,000
Total Owners Equity 1,389,335 1,158,000
Total Liab. & Owner's Equity $3,100,435 $2,539,000
Question 1. Using the data presented above:
a. Recreate the income statement and balance sheet using formulas wherever possible. Each statement should be on a separate excel worksheet. Try to duplicate the formatting exactly. ( must be in excel worksheet)
b. On another excel worksheet, create a statement of cash flows for 2011. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source on previous worksheets.
c. Using Excel’s outlining feature, create an outline on the statement of cash flows that, when collapsed, shows only the subtotals for each section.
d. Suppose that sales were $3,800,000 in 2011 rather than $3,500,000. What is the 2011 net income and retained earnings? (must be in excel worksheet)
e. Undo the changes from part d, and change the tax rate to 40%. What is the 2011 net income and retained earnings?
Question 2. Using the data from the previous problem:
a. Create a common-size income statement for 2010 and 2011. This statement should be created on a separate worksheet with all formulas linked directly to the income statement. (must be in excel worksheet)
b. Create a common-size balance sheet for 2010 and 2011. This statement should be created on a separate worksheet with all formulas linked directly to the balance sheet. (must be excel worksheet)