CHAPTER 1

EXCEL ASSIGNMENT

CORPORATE FINANCE

ACCRUAL vs. CASH FLOW

The following is a 3 year summary of the transactions of Pens 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 $96,000, paid in cash. These assets have a 3 year life and can be depreciated using the straight-line method.

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. Sales, with the following collections of cash:

2013 2014 2015

Sales $542,000 $509,000 $321,000

Cash Received $406,000 $517,000 $449,000

Paid all other operating expenses including salaries, in cash as follows: 2013 = $127,000

2014 = $91,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 all three years total. Treat cost of equipment as expense in year purchased for cash basis. You must depreciate equipment for accrual basis-you should use straight line depreciation with no residual value. Have a separate listing 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 $542,000

2 - CGS $301,000

3 Gross Profit =B1-B2 Use formula to subtract $301,000 from $542,000