Summer 2010 Name Computer Project 100 points 75% of project grade Student Number Birthday Month A file entitled Computer Project has been

Summer 2010 Name________________________

Computer Project – 100 points

75% of project grade Student Number_______________

 

Birthday Month________________

 

 

A file entitled “Computer Project” has been placed on MOODLE for you to download and save. This file contains 6 worksheet tabs.

 

Free Apparel began their business several years ago and recently lost their accountant. Thankfully, they have been able to hire a fine accounting/finance student from MSU. They have provided you with the unadjusted trial balance for Free Apparel Incorporated as of 12/31/09. Free Apparel needs to issue financial statements in accordance with US GAAP for the year ended 12/31/2009.

 

Your required tasks are as follows:

 

1. On the designated worksheet, prepare in journal entry form all adjusting and correcting journal entries based on the following information (Round all numbers to the nearest dollar). Before preparing entries, finish the story by filling in the blanks.

a. On June 1, 2009 a 19 month insurance policy was purchased for $24,000.

b. On 7/1/2009, Free paid Glamor Advertising $50,000 for two years of advertising services. Equal services are provided each month.

c. Free Apparel needed some temporary additional storage space so on 7/1/2009 they rented a unit for an annual rate of $12,000.

d. $14,250 of store supplies were purchased during the year and store supplies expense was increased. $9,850 of these supplies remain at the end of the year.

e. $2,500 of office supplies were purchased during the year and the asset office supplies was increased. $1,619 of these supplies remained at the end of 2009.

f. On December 1, 2009, Free Apparel issued a 3-month note receivable to New Skin Company at an annual interest rate of 10%. Principle and interest will be paid at the end of the 3-months. The note was recorded in Notes Receivable and is the only note outstanding.

g. You note that store equipment and office equipment has not been depreciated for this year. The company uses DDB. Free Apparel expects to use all equipment for 20 years with a salvage value of $1,000 for each.

h. The storage building was purchased this year on February 1. The building is being depreciated on a straight-line basis using partial year. The building is estimated to have a life of 40 years and a savage value of $18,619.

i. Office salaries of $9,800 remained unpaid at 12/31/09, but the prior accountant did not accrue. Sales personnel are also due their last commission check which is net sales times 1%.

j. On 7/1/2009, Free Apparel rented a portion of one store to Twister Co. The contract was for 19 months and Free Apparel required all of the cash up front. The rent is being earned equally each month. When cash was received, unearned rent was appropriately recorded and no revenue has been recorded for this transaction in 2009.

k. Based on your review of the cash balances, you note that there was an overdraft of ($10,250) in one of your bank accounts where this is the only account at that bank. The previous accountant netted this amount with the other cash accounts.

l. At 12/31/2009, based on the aging method of all accounts with debit balances, Free apparel determines that uncollectible accounts are $94,250.

m. Free Apparel uses the Dollar Value LIFO inventory method. For internal purposes, the Merchandise Inventory Account is maintained at FIFO (current costs). At the end of the year, the LIFO reserve account is adjusted so inventory on the balance sheet reflects Dollar Value LIFO. You need to calculate the proper inventory balance and adjust the LIFO reserve. The price index for this year is 1.06. Prior year inventory records show the following calculation for 2008:

300,000 X 1.0 = 300,000

20,000 X 1.03 = 20,600

15,000 X 1.04 = 15,600

336,200

n. Free Apparel was authorized to issue 1,000,000 shares of $1 par Common Stock but has only issued 500,000 shares of common stock at 12/31/2009. All 500,000 shares are outstanding.

 

o. Free Apparel has two loans outstanding. Interest is paid annually. The facts on each loan is as follows:

Onstar Bank – outstanding since March 1, 2009 with a 6.5% interest rate.

Coldstar Bank – outstanding since 7/1/2009, with a 5.9 %.

Since interest will not be paid until 2010, Free Apparel did not accrue any interest.

p. Free Apparel has a straight tax rate of 28%.

q. The Land Account includes a piece of land with a cost and fair value of $225,000 that is not currently being used in the normal operations of the building. This piece of land is being held for speculative purposes.

r. On January 1, 2007, Free Apparel purchased a patent for $70,000. At the time of the purchase the patent had an estimated useful life of 7 years with no residual value. In 2009, the company has estimated that the patent will only be useful until the end of 2010. No one knows what to do with this information so no amortization has been recorded for 2009.

s. As of 12/31/2009 the Available for Sale Securities have a fair value of $82,619. Due to the major market decline, the company does not plan on selling the assets in 2010, but their intent is to sell at some point in time. (Hint: Unrealized gains and losses can be recorded directly to Accumulated Other Comprehensive Income.

t. During the year, Free Apparel did some research and development on some new products. They properly expensed all of the research but the accountant read in an article that under IFRS they could capitalize development costs so he capitalized $189,000 of development expenses as an other intangible asset.

 

1. After the above adjusting entries are entered on the adjustment worksheet, the cells should be linked to the adjustments column of the worksheet. Your adjustment amounts in the worksheet column should be linked to the adjustment sheet so if you change the debit/credit amount in the adjusting entry, the column amount will automatically change.

 

1. Complete the adjusted columns by the use of the formula. Think about the best way to do this. Your last two columns should never contain constant numbers but will include formulas only. (Maximum points are given for using an ‘if” statement, but the majority of the points are just given for having a proper formula).

 

1. Prepare a multiple-step income statement on the proper worksheet. Your Income Statement should be in good form (proper titles, etc., use examples from your book) and well formatted. Do your best designating between selling and administrative expenses. You should use formulas in all cells, not constant numbers. (That means, your income statement should be linked to the adjusted numbers on your worksheet.)

 

1. Prepare a Comprehensive Income Statement on the proper worksheet. Free Apparel uses the Second Income Statement Approach. (see page 147 & 148)

 

1. Prepare a Retained Earnings Statement on the proper worksheet. Your Statement should be formatted. You should use formulas in all cells, not constant numbers.

 

7. Prepare a Classified Balance Sheet on the proper worksheet as of 12/31/09. Your Statement should be formatted. You should use formulas in all cells, not constant numbers.

 

Due Date: Submit a finished excel file through MOODLE by Tuesday, July 6th at 1 p.m. In class on July 6^th, submit the top sheet of this assignment with your information filled out. Your file should be named yourlastnamecomputerproject. Only files submitted by MOODLE will be accepted and no part of the assignment will be accepted without this top sheet with your relevant information.

 

As noted on the syllabus, late projects will be accepted until graded projects have been returned. There is a 10% penalty for each 24-hour period that the project is late.

 

 

Grading: Your grade will be based on correctness, statement form, and spreadsheet formatting.