Problem 1:
Basic Financial Statements
|
Using the data presented below for Conica Inc.: 2009
|
2008
|
|
Sales
|
$5,500,000
|
$4,250,000
|
|
Cost of Goods
|
3,300,000
|
2,550,000
|
|
Depreciation
|
34,000
|
30,000
|
|
Selling and G&A Expenses
|
765,000
|
632,000
|
|
Fixed Expenses
|
100,000
|
100,000
|
|
Lease Expense
|
55,000
|
55,000
|
|
Interest Expense
|
130,000
|
110,000
|
|
Tax Rate
|
35.00%
|
30.00%
|
|
Shares Outstanding
|
75,000
|
65,000
|
|
Cash
|
84,000
|
48,000
|
|
Marketable Securities
|
37,000
|
32,000
|
|
Accounts Receivable
|
370,000
|
347,000
|
|
Inventory
|
870,000
|
715,000
|
|
Prepaid Expenses
|
55,000
|
37,000
|
|
Plant & Equipment
|
5,170,000
|
4,910,000
|
|
Accumulated Depreciation
|
170,000
|
136,000
|
|
Long Term Investments
|
350,000
|
270,000
|
|
Accounts Payable
|
380,000
|
300,000
|
|
Notes Payable
|
44,000
|
25,000
|
|
Accrued Expenses
|
102,000
|
75,000
|
|
Other Current Liabilities
|
140,000
|
136,000
|
|
Long-term Debt
|
3,220,000
|
3,122,000
|
|
Common Stock
|
1,550,000
|
1,300,000
|
|
Additional Paid-in-Capital
|
572,000
|
542,000
|
|
Retained Earnings
|
758,000
|
723,000
|
|
|
|
|
A) Make Conica’s income statement and balance sheet using formulas wherever possible. Each statement should be on a separate worksheet. Improve the readability of the data by selecting the format, so that Excel will display the numbers as if they had been divided by 1,000. Make the appropriate note on the heading of each financial statement.
B) On another worksheet, create a statement of cash flows for 2009. All formulas should be linked directly to the source on previous worksheets.
C) Using Excel’s outlining feature, create an outline on the balance sheet that, when collapsed, shows only the subtotals for each section.
Problem 2: Using the data from the previous problem:
A) Create a common-size income statement and balance sheet for 2008 and 2009. These statements should be created on a separate worksheet with all formulas linked directly to the income statement and balance sheet.
B) Using the common-size income statement for 2009, create a pro-forma income statement for 2010 assuming that each item is expected to remain in the same proportion as in 2009. The forecasted sales for 2009 are $7,350,000