Instructions: Create an income statement and a balance sheet for years Y1 through Y5. You must use the accrual basis of accounting and apply GAAP accounting principles. Include the following five items on a separate sheet in the same EXCEL worksheet file: balance sheet; income statement; calculation of cost of goods sold, purchases, and ending inventory; calculation of ending cash balance; cash collections of accounts receivable and cash payments of other current liabilities.
Prepare a statement of cash flows for years Y1 through Y5. (Note: this would be your sixth sheet in the same EXCEL worksheet file).
Balance Sheet 12/31/Y0
Assets
Cash 100,000
Accounts Receivable 150,000
Inventory 300,000
Other Current Assets 20,000
Total Assets 570,000
Liabilities
Accounts Payable 80,000
Other Current Liabilities 40,000
Total Liabilities 120,000
Stockholders' Equity
Common Stock 200,000
Paid in Capital in Excess Par 150,000
Retained Earnings 100,000
Total Stockholders' Equity 450,000
Total Liabilities and
Stockholders' Equity 570,000
Income Statement for the Year Ending 12/31/Y0
Sales 180,000
Cost of Goods Sold 85,000
Gross Profit 95,000
Operating Expenses 40,000
Income Before Taxes 55,000
Income Tax 18,700
Net Income 36,300
Assumptions
(1) On 1/1/Y5 the company purchased land costing $30,000 using cash.
(2) Sales (in units) increases by 15% each year. The sales price is $30 in year Y0, and the price increases by 10% each year (round to two decimal places).
(3) The purchase price per unit of inventory is $8 in Y0. The purchase price per unit of inventory in each of the year’s Y1 through Y5 is $8. The ending inventory (in units) increases by 10% each year. The ending inventory (in units) at Y0 is 37,500.
(4) All inventory is purchased on credit and annual cash payments are made on accounts payable at the end of each year for the prior year’s ending accounts payable; i.e. Y0’s accounts payable of $80,000 are paid at the Y1.
(5) Accounts receivable increase 12% each year.
(6) Other current assets increase 4% per year and are paid in cash.
(7) Other current liabilities increase 6% and operating expenses increase 5% per year.
(8) Tax rate each year is 34%.