Excel Assignment

(Financial Statements)

This is an individual assignment, not a team assignment.

In order to complete this assignment, you must turn in a single Excel file (with your name) that includes all of the following models (you may put them on separate worksheets or pages, but make sure that they are clearly labeled):

AIncome Statement

1. Create a simple income statement model that includes at least the following elements: (Sales, COGS, Depreciation, Interest Expense, EBIT, Taxes, EAT).
2. Please see Demo 1A for suggestions on how to do this.

BBalance Sheet

1. Create a simple balance sheet model that includes at least the following elements: (Current Assets, Cash, A/R, Inventory, Total Current Assets, Fixed Assets, Net Fixed Assets, Total Assets, Current Liabilities, Accounts Payable, Notes Payable, Total Current liabilities, Long Term Debt, Owners Equity, Common stock and Paid-In Surplus, Retained Earnings, Total Owners Equity, Total Liabilities and Owner's Equity.
2. Please see Demo 1B for suggestions on how to do this.

CCombined Income Statement and Balance Sheet

1. Create a new income statement and balance sheet (separate from the above) that are linked to each other. Please account for the following (please note that this is a general exercise and that there are some specific accounting nuances that may be difficult to resolve given this limited amount of dataplease see the instructor if you are having difficulty):

Revenue $100,000 (this should be an input field)

COGS 50% of revenue

Taxes 35% rate

Dividends 50% of Net Income

Interest Expense 12% of Long-Term Debt (Balance Sheet)

Depreciation 20% of Net Fixed Assets (Balance Sheet)

Number of shares 100,000 (input field)

EPS (calculate)

Current Assets

Cash $20,000 (input field)

A/R 20% of Sales (Income statement)

Inventory $20,000 (input field)

Total Current Assets (calculation)

Fixed Assets

Net Fixed Assets $75,000 (input field)

Total Assets (Calculation)

Liabilities and Owner's Equity

Current Liabilities

Accounts Payable 40% of COGS (Income statement)

Notes Payable $10,000 (input field)

Total Current Liabilities (calculation)

Long Term Debt $20,000 (input field)

Owners Equity

Common Stock and Paid-In Surplus $50,000 (input field)

Retained Earnings (calculate)

Total Owners Equity (calculate)

Total Liabilities and Owner's Equity (calculate)

1. Please see Demo 1C for suggestions on how to complete this exercise.

DSimple Cash Flow Statement

1. Create a simple cash flow statement covering a period of twelve months that accounts for the following variables: beginning cash balance, net cash collections, net cash outflows, net inflows and outflows for period, ending cash balance.
2. Please see Demo 1D for suggestions on how to complete this exercise.