The following is a 3-year summary of the transactions of Pen's Clothing Company, which opened its doors on January 1, 2013 and went out of business on December 31, 2015.
1. Purchased furnishings, equipment and display material on Jan. 2, 2013 for $99,000, paid in cash. These assets have a 3-year life and can be depreciated using the straight-line method.
2. Purchased merchandise and made payment on merchandise as follows:
2013 2014 2015
Purchased $301,000 $347,000 $103,000
Cash Paid $244,000 $402,000 $105,000
3. Sold all of the merchandise each year at double the purchase price, with the following collections of cash:
2013 2014 2015
Sales $555,000 $509,000 $321,000
Cash Received $406,000 $517,000 $412,000
4. Paid all other operating expenses including salaries, in cash as follows:
2013 = $127,000
2014 = $81,000
2015 = $102,000
Using Excel, create income statement for each of the 3 years as well as total for all the years, for both cash flow and accrual basis. Compute the Net Income on an ACCRUAL BASIS and a CASH FLOW BASIS for each year, then a total for all three years. Treat cost of equipment as an expense in year purchased for cash basis. You must depreciate equipment for accrual basis-you should use straight line depreciation with no residual value. Provide separate line items for equipment and operating expenses. Do not add them together and place them into 1 account name.
You must use a formula in excel whenever it is possible. For example, if you are going to do 2013 accrual method, you would have
A B
1 Sales $555,000
2 - CGS $301,000
3 Gross Profit =B1-B2 Use formula to subtract $301,000 from $555,000