Purpose:

The purpose of this assignment is to assess your skills in using software to make business decisions.

Instructions:

This exercise involves preparing an income statement, balance sheet, and cash flow statement for a fictional company called "ABC Corporation" based on the provided data. You have been provided with the following data for ABC Corporation for the year ending December $31,20$XX:

Income Statement Data:

Sales Revenue: $$500,000$

Cost of Goods Sold $($COGS$)$: $$250,000$

Gross Profit: Calculate the gross profit.

Operating Expenses: $$150,000$

Net Income: Calculate the net income.

Balance Sheet Data:

Cash: $$50,000$

Accounts Receivable: $$30,000$

Inventory: $$40,000$

Property, Plant, and Equipment $($PPE$)$: $$200,000$

Accumulated Depreciation: $$30,000$

Total Assets: Calculate the total assets.

Accounts Payable: $$20,000$

Short$-$term Debt: $$15,000$

Long$-$term Debt: $$100,000$

Total Liabilities: Calculate the total liabilities.

Common Stock: $$50,000$

Retained Earnings $($beginning of the year$)$: $$25,000$

Retained Earnings $($end of the year$)$: Calculate the retained earnings.

Total Equity: Calculate the total equity.

Cash Flow Statement Data:

Cash from Operations: Calculate the cash from operations using the direct method.

Cash from Investing: Assume $$10,000$ was spent on purchasing PPE and $$5,000$ was received from selling an old asset.

Cash from Financing: Assume $$20,000$ was borrowed $($long$-$term debt$)$ and $$10,000$ was repaid $($short$-$term debt$).$

Net Cash Flow: Calculate the net cash flow for the year.

Your task is to create Excel worksheets for the Income Statement, Balance Sheet, and Cash Flow Statement.

Here's a step$-$by$-$step guide for each statement:

Income Statement:

In an Excel worksheet, create a table with the following headers: "Item," "Amount," and "Notes."

Fill in the "Item" column with the appropriate income statement items $($e$.$g$.,$ Sales Revenue, COGS, Gross Profit, Operating Expenses, Net Income$).$

In the "Amount" column, input the respective values for each item.

Use Excel formulas to calculate Gross Profit and Net Income.

Balance Sheet:

Create another Excel worksheet with the headers: "Item," "Amount," and "Notes."

Input the balance sheet items $($e$.$g$.,$ Cash, Accounts Receivable, Inventory, PPE, Accumulated Depreciation, Accounts Payable, Short$-$term Debt, Long$-$term Debt, Common Stock, Retained Earnings$)$ in the "Item" column.

Input the respective values for each item in the "Amount" column.

Use Excel formulas to calculate Total Assets, Total Liabilities, and Total Equity. Ensure that Total Assets equals Total Liabilities and Equity.

Cash Flow Statement:

Create a third Excel worksheet with the headers: "Item," "Amount," and "Notes."

Input the cash flow statement items $($e$.$g$.,$ Cash from Operations, Cash from Investing, Cash from Financing, Net Cash Flow$)$ in the "Item" column.

Input the respective values for each item in the "Amount" column.

Use Excel formulas to calculate Cash from Operations and Net Cash Flow based on the provided data.

Remember to format your Excel worksheets neatly and use appropriate formatting for currency values. Double$-$check your calculations to ensure accuracy. You can use Excel functions like SUM, subtraction, and addition to perform the necessary calculations.

Once you've completed these worksheets, you'll have a set of financial statements for ABC Corporation.