Assets as of Jan. 31, 2005
Cash:$5100
A/R:$27300
Inventory:$8050
Unexpired Insurance:$1375
Fixed Assets, net:$12250
Liabilities as of Jan. 31, 2005
A/P:$0
Dividend payable:$0
Rent payable:$0
Loan payable:$15,629
Assets as of Feb. 28, 2005
Cash:$37691
A/R:$36200
Inventory:$6000
Unexpired Insurance:$1250
Fixed assets, net:$12000
Liabilities as of Feb. 28, 2005
A/P:$35450
Dividend payable:$0
Rent payable:$0
Loan payable:$0
Assets as of Mar. 31, 2005
Cash:$32991
A/R:$22700
Inventory:$6000
Unexpired insurance:$1125
Fixed assets, net:$14750
Liabilities as of Mar. 31, 2005
A/P:$18000
Dividend payable:$1500
Rent payable:$16750
Loan payable:$0
Income Statement Jan. 2005
Sales $62000
Wages & Salaries ($15000)
Insurance expired ($125)
Depreciation ($250)
Misc. expense ($2500)
Cost of Goods Sold ($31000)
Dividend ($0)
Rent ($250)
Interest expense ($129)
Net Income $12746
Income Statement Feb. 2005
Sales $75000
Wages & Salaries ($15000)
Insurance expired ($125)
Depreciation ($250)
Misc. expense ($2500)
Cost of Goods Sold ($37500)
Dividend ($0)
Rent ($250)
Interest expense ($130)
Net Income $19245
Income Statement Mar. 2005
Sales $38000
Wages & Salaries ($15000)
Insurance expired ($125)
Depreciation ($250)
Misc. expense ($2500)
Cost of Goods Sold ($19000)
Dividend ($1500)
Rent ($16750)
Interest expense ($0)
Net Income ($17125)
Statement of Cash Flows Jan. 2005
Starting balance $5000
A/R collections $47200
Wages & Salaries ($15000)
Misc. expense ($2500)
A/P payments ($35550)
Dividend ($1500)
Rent ($8050)
Ending balance from operations ($10400)
Loan to bring to at least $5000: $15500
Ending balance for month: $5100
Statement of Cash Flows Feb. 2005
Starting balance $5100
A/R collections $66100
Wages & Salaries ($15000)
Misc. expense ($2500)
A/P payments ($0)
Dividend ($0)
Rent ($250)
Ending balance from operations $53450
Loan repayment with 2 months interest: $15759
Ending balance for month: $37691
Statement of Cash Flows Mar. 2005
Starting balance $37691
A/R collections $51500
Wages & Salaries ($15000)
Misc. expense ($2500)
Fixture purchase ($3000)
A/P payments ($35450)
Dividend ($0)
Rent ($250)
Ending balance from operations $32991
No loan activity required.
Ending balance for month: $32991
Notes:
The income statements record expenses and sales as they occur, while
the statement of cash flows only acknowledges activities when cash is
received or disbursed. The balance sheets reconcile the two.
The accounts receivable collections are calculated as follows:
In January, collect 60% of January sales, 30% of December sales, and
10% of November sales. In February, collect 60% of February sales,
30% of January sales, and 10% of December sales. In March, collect
60% of March sales, 30% of February sales, and 10% of January sales.
Outstanding accounts receivable are calculated by taking the starting
accounts receivable, adding sales, and subtracting collections.
Cost of goods sold is calculated by dividing the sales in each month
by eight dollars to arrive at the number of units sold and then
multiplying by the cost of four dollars.
The inventory each month is calculated by taking the starting
inventory position, subtracting the cost of goods sold, and adding
whatever purchases are needed to bring the ending total up to 6000.
The purchases become the outstanding accounts payable.
Interest expense is calculated by taking the outstanding loan balance,
plus any accrued interest, and multiplying by (0.1/12). Because the
loan is outstanding for two periods, the total repayment is equal to
the loan value multiplied by (1+ (0.1/12)^2). However, interest
expense for the purposes of the income statements must be the amount
accrued in each month rather than the two-month total.
Fixed assets, net are simply the previous month's balance less
depreciation except in March when new fixed assets and the amount of
$3000 are purchased. In this case, the existing fixed assets are
depreciated and then the new purchase is added to arrive at the total.
Depreciation would then increase in subsequent months.
No insurance payments are made during the periods in question, so the
unexpired insurance total is simply debited by the insurance expired
each month.
Wages & salaries, miscellaneous expenses, and the $250 per month rent
charged are paid as they are incurred, so nothing accrues on the
balance sheet.
The rent payable as a percentage of sales and dividend payable are
accrued in March, so they appear on the income statement for March
even though the cash associated with them will not be paid until
April. Correspondingly, the cash associated with the December rent
payable and dividend payable appears in the January statement of Cash
flows because that is when it is disbursed.
Please request clarification if needed.
Sincerely,
Wonko |
Clarification of Answer by
wonko-ga
on
04 Feb 2005 16:49 PST
No, I did it by hand with a calculator and pen and paper. You can use
the "Notes" section of my Answer to construct the appropriate formulas
in Excel. I would suggest putting the income statement, the statement
of cash flows, and the balance sheet on separate tabs and creating
formulas linking them.
In the future, may I suggest that you put all of your requirements as
part of your question, such as in this case, "I need it in Excel
format." However, if you can provide me with information regarding
what confuses you, I will try to resolve your confusion.
Sincerely,
Wonko
|
