Please see the attachment.
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.
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
The solution explains how to prepare the financial statements and also common size statements