Question

Goal: Create an Excel worksheet to prepare an income statement, a balance sheet and a statement of changes in equity for a start-up business.

Scenario: You have started a valet parking business called First Class Valet. You have tracked your cash, supplies, revenues, expenses, and other items in a spreadsheet. The totals for each account at the end of your first month are as follows (ignore GST):

1. Sales Revenues, $400

2. Supplies on hand (asset account), $175

3. Cash, $300

4. Supplies expense, $225

5. Capital, 1 July 2019: $250

6. Salary payable, & Salary expense, $50.

7. Drawings: $20

8. Unearned revenue: $75

9. Accounts receivable: $5

Required

a) In a new worksheet, prepare an income statement, a balance sheet, and a statement of changes in equity for 31 July 2019. Your statements should show the result of the following:

1. How well did your business do in its first month of operations? 2. How did this affect your capital account?

b) In a different worksheet draw a graph to visualise the financial performance of your company in an Excel spreadsheet.

Hint: Your graphs could visualise the following ratios:
1. Expense to sales ratio
2. profit margin ratio
c) provide a brief explanation about the financial performance of your company.

Answer 1

(A)

INCOME STATEMENT

(for the period ended 31.07.2019)

PARTCULARS	AMOUNT (in $)
Revenue
Sales Revenue	400
Expenses
Selling Expenses	225
Administrative Expenses	50
	275
NET INCOME	125

BALANCE SHEET as on 31.07.2019

PARTICULARS	AMOUNT (in $)
ASSETS
Inventories	175
Account Receivables	5
Cash and cash equivalents	300
	480
EQUITY & LIABILITIES
Capital (net of drawings and net income)	355
Outstanding expenses (salary)	50
Other current liabilities (unearned revenue)	75
	480

The business’s position in the first month is satisfactory and on-track. The business so far:

• Is profitable,
• Has ample cash reserves,
• No external liability till now

(B)

Expenses to sales ratio = (expenses/sales) = 0.6875

Profit margin ratio = net income/sales = 0.3125

(The graph for only one month’s data can be simply made by just plotting the point on the graph.)

(C)

The financial performance of the business so-far is good and healthy. With satisfactory profits and a good cash flow, the company is functioning well.