I need the answer for the following question in excel

Prepare the following in the excel workbook provided:

1) A sheet with all of the trial balances (Unadjusted, Adjusted, and Post Closing);

2) A sheet with all adjusting entries;

3) A sheet with the required financial statements in proper form; and

4) A sheet with closing entries.

Sams Snow Shop Incorporated (SSSI) is a local Vancouver Island company that specializes in selling ski and snowboarding clothing and equipment in several locations across Vancouver Island. SSSI uses accounting standards for private enterprises (ASPE) for their financial reporting and is required by their financial institution to submit financial statements prepared using ASPE each year, specifically balance sheet, income statement and statement of retained earnings. The bank does not require a statement of cash flows.

SSSI has a January 31 year-end and it is now February 19, 2017. The companys CEO, Sarah Snowbird, has hired you to complete the year end adjusting entries, financial statements and closing entries for the year ended January 31, 2017.

SSSI has provided you with an unadjusted trial balance in alphabetical order (in excel) and information about their accounting policies and other information you will need to complete the financial statements (Exhibit A).

Sarah has asked you to make sure you re-order the trial balance in the proper order and provide descriptions for all journal entries. The company uses the income summary for closing entries.

EXHIBIT A

To: Acct 111 Student

From: Sarah Snowbird, CEO Sams Snow Shop Incorporated

Date: February 19, 2017

Subject: SSSIs Financial Information

Depreciation policies

Depreciation is only taken at the end of each fiscal year and the company uses only one depreciation expense account for all assets.

The vehicle was sold on January 1, 2017 for $1,500. The bookkeeper recorded the following entry:

Dr. Cash 1,500Cr. Loss on disposal 1,500

Im not sure the bookkeeper recorded correctly, so please correct if needed. Up until that point in the year, the vehicle was driven 10,000 km.

Information to prepare other year-end adjusting entries

Management has reviewed the aging accounts receivable and estimates that the allowance for doubtful accounts should be $75,000.

The prepaid insurance balance is a 12-month insurance policy which was purchased and paid for on September 1, 2016.

Rent is prepaid quarterly based on a calendar year on March 1st, June 1st, September 1stand December 1stand is pro-rata expensed at the end of each fiscal quarter.

On January 5, 2017 SSSI received a deposit of $6,700 for a snowboard order to be delivered March 15th. SSI recorded this as revenue.

Supplies on hand at year-end totalled $4,587. The company expenses supplies as general and administrative expenses.

On September 30, 2016 the company issued a 5-year, 4%, $500,000 bond when the market rate was 5% and it pays interest semi-annually on March 31 and September 31. The company uses the effective interest rate method for amortization bond discounts and premiums.

	Depreciation Method	Salvage Value	Useful Life
Office furniture	Declining balance	$0	10 years (20% rate)
Equipment	Straight-line	$13,000	15 years
V ehicles	Units of production	$0	120,000 km
Building	Straight-line	$35,000	35 years

Accounting 111 Excel Project 2017W

EXHIBIT A

(continued)

The bookkeeper records the entire mortgage payment as a reduction to principal; therefore, the interest needs to be separated at year-end. The mortgage is paid every 4 months with blended payments of $6,129. The annual interest rate is 3.5%. The previous accountant correctly prepared the following amortization table:

Payment Date	Blended payment	Interest	Principal Reduction	Closing balance
31-Jan-16				69,531
31-May-16	6,129	608	5,521	64,010
30-Sep-16	6,129	560	5,569	58,441
31-Jan-17	6,129	511	5,618	52,823
31-May-17	6,129	462	5,667	47,156
30-Sep-17	6,129	413	5,716	41,440
31-Jan-18	6,129	363	5,766	35,674
31-May-18	6,129	312	5,817	29,857
30-Sep-18	6,129	261	5,868	23,989
31-Jan-19	6,129	210	5,919	18,070
31-May-19	6,129	158	5,971	12,099
30-Sep-19	6,129	106	6,023	6,076
31-Jan-20	6,129	53	6,076	-

Other information

The company has both preferred shares and common share authorized and outstanding. The company is authorized to issue 10,000 $5 non-cumulative preferred shares and at January 31, 2017 it has 500 issued and outstanding. The company is authorized to issue unlimited common shares and at January 31, 2017 has 71,000 issued and outstanding. SSI reports the share details on the face of their Balance Sheet even though this is not a required disclosure for ASPE.

In May 2016, management determined that their ski and snowboard rentals line of business was not profitable and could not compete with other rental shops and decided to discontinue operations. On September 23, 2016 the company sold all assets relating to the ski and snowboard rental business and recorded a gain on the transaction.

The companys tax rate is 18%.