Hi i am not able to resolve this project can you please help me to resolve it . Below is link of word and excel .

The Bernard had quite a successful year in their newly opened Pennsylvania Store. Matt and Julie Bernard have engaged their bookkeeper, Barb Burke, to Prepare the shop's Trial Balance, Net Income Statement, and Balance Sheet, which Will be forwarded to their CPA. The Pennsylvania shop will use the same Chart of Accounts set-up in Practice Set 1. You'll notice one or two accounts have been added for this final project. The shop has been open for eight months. The grand opening was on November 1st of last year. The store's fiscal year will be July 1st-June 30th. Barb Burke calculated the shop's unadjusted trial balance sheet in May after the Store had officially closed for the season. There are five separate worksheets for you to complete in this project. The Worksheets are in an Excel format. The cells highlighted in light yellow are input Cells. If you don't have Excel, you'll need to manually calculate the worksheets. Be sure to read the all the instructions for this project and review the worksheets Before you begin your work. Part :1 Adjusted Trial Balance Barb has found the following adjusting entries that need to be made to the Unadjusted Trial Balance. Prepare the Adjusting Journal Entries, and enter them onto the Adjusting Entries column of the Adjusted Trial Balance worksheet. Denote the entry number of each adjustment in the entry # column. Note: Some items won't require an adjusting entry. Read each item carefully. 1. Business insurance was purchased in November for $12,000, which covers a 12 month period. An entry needs to be made between prepaid and insurance expense. This entry has been done for you and should be used as an example of how the rest of the entries should be used as an example of how the rest of entries should be completed. 2. Depreciation needs to be calculated for all of the fixed-asset accounts. Computer equipment, computer software, furniture and fixtures will be deprecated using the straightline method over 60 months. Machinery will be deprecated over 84 months using the straight-line method of accounting. 3. An entry was made incorrectly recording the Note Payable interest and principal payments to the Other Accrued Liability account. The entry should have been made to Interest Expense and the Notes Payable accounts. The loan has a monthly payment of $1001.61 The shop has paid $2,439 in interest and $5,565 in principle over the past eight months. A. The note was used to purchase a customer list. The asset will have a life of 10 years, and it should be amortized evenly over the 10-year period. You'll need to record the amortization. B. The loan has a monthly payment of $1,001.61. The short-term portion of the loan (the next 12 months of payments) needs to be reclassed from account 400-Note Payable to account 335-Short-Term Portion of Notes Payable. C. As stated above, the original interest expense of $2,439 was incorrectly recorded in the Other Accrued Liabilities account number 320 for the eight-month period. You'll need to make an adjusting journal entry to correct this. You'll need to make an adjusting journal entry to correct this. You'll need to reclassify the error to the error to the interest expense account. D. The principal amount of $5,565 was also incorrectly recorded in Other Accrued Liabilities account number 320. You'll need to make an adjusting entry, moving this from Accrued Other and reducing the Long-Term Payable account. 4. Several late payables were received but not recorded. These payables were for clothing and accessories discounted after the season and will be sold next year. The total late payable amount is $6,998. 5. Inventory is valued correctly at the lower of cost or market. 6. Since it's the shop's first year, it could be hard to estimate what the balance in the allowance for doubtful accounts should be. But based on the Bernard' s New York store and the similarities of both, Mrs. Bernard estimated the allowance should be 5 percent of Accounts Receivable. 7. Mr. and Mrs. Bernard collect their salary thoughout the year. They're paid one week in arrears Their gross payroll for a week is $3,942, and the employer portion of tax associated with the accrual is 15 percent. You'll need to make an adjusting entry to record the one week of salary and tax expense. 8. Rent is $3,000 per month and is prepaid on the first day of the month. 9. Utilities this time of year are low and run $275 a month. Services are billed for the previous month at the beginning of the next month. For example, June's utility invoice would be sent at the beginning of July. You'll need to adjust for this. 10. The shop's CPA has advised the Bernards that their estimated tax liability for the year will be $51,924. They've made payments through the end of May of $45,434. The remaining amount will need to be accrued. Total the adjusting entries to be sure they zero out. Your next step will be to combine the Unadjusted Trial Balances and the Adjusting Entries to calculate the Adjusted Trial Balance. Next, divide the accounts into either Net income or Balance Sheet accounts and enter into the appropriate columns. Hint: The net balance of both the Income and Balance Sheets accounts will be $158,977. Part 2: Aggregation of Income Account Aggregate the Net Income account balances. You can use Project, Part 2, to Complete this task. This should be similar to that shown on page 184 in your Textbook. Remember, the format is slightly different. Part 3: Income Statement/Vertical Analysis Prepare an Income Statement and Vertical Analysis for the eight- month period Ending June 30. This should be similar to that shown on page 216 in your Textbook. Part 4: Aggregated Balance Sheet Aggregate the Balance Sheet Accounts. This should be similar to that shown On pages 198 and 199 in your textbook. Again, remember the format is slightly Different. Part 5: Balance Sheet and Key Ratios Prepare the Balance Sheet for the eight-month period ending June 30. Also, you Need to define and calculate the current ratio, quick ratio, and debt-to-equity Ratio for the Mile High Ski and Snowboard Shop Mile High Ski and Snowboard Shop Pennsylvania Entity Adjusted Trial Balance For the Eight Month Period Ending 6/30/XX Final Project (Part 1) EXCEL Account Number 010 020 030 040 050 060 100 110 120 130 150 160 170 180 210 255 300 310 315 320 330 335 340 400 Unadjusted Trail Balance Current Assets Cash Accounts receivable Bad debt reserve Marketable Securities Inventory Prepaid Assets Computer Equipment Computer Software Furniture and Fixtures Machinery Accumulated Depreciation - Computer Equipment Accumulated Depreciation - Computer Software Accumulated Depreciation - Furniture and Fixtures Accumulated Depreciation - Machinery Customer List Customer List Amortization Accounts payable Accrued Payroll & Payroll Tax Liability Income Tax Payable Other accrued liabilities Other short term liabilities Short-tem Portion of Note Payable Unremitted sales tax Note Payable (120 payments) 1 of 21 $ 134,195 12,500 (600) 25,000 34,600 600 27,500 5,000 10,000 12,500 Adjusting Entries 4,000 Entry # 1 100,000 (12,500) (3,942) (45,433) (17,157) (100,000) Adjusted TB (PS# Part 1) Mile High Ski and Snowboard Shop Pennsylvania Entity Adjusted Trial Balance For the Eight Month Period Ending 6/30/XX Final Project (Part 1) EXCEL Account Number 500 510 600 610 620 630 640 650 700 710 720 730 740 800 805 810 812 814 815 820 822 825 830 835 Unadjusted Trail Balance Current Assets Capital Retained earnings Clothing and accessory sales Rentals Services Ski equipment sales Snow board equipment sales Other revenues Adjusting Entries Entry # (176,800) (128,500) (76,300) (225,320) (193,400) (32,500) 70,720 32,125 34,335 90,128 87,030 5,230 3,500 Clothing and Accessory - Cost of Sales Rentals - Cost of Sales Services - Cost of Sales Ski Equipment - Cost of Sales Snow Board Equipment - Cost of Sales Advertising Bank fees Depreciation Expense Amortization Expense Bad Debt Expense Dues and Subscriptions Employee benefits Income Tax Payable Insurance Expense Interest Expense Legal and accounting fees 500 45,433 12,000 3,000 2 of 21 (4,000) 1 Adjusted TB (PS# Part 1) Mile High Ski and Snowboard Shop Pennsylvania Entity Adjusted Trial Balance For the Eight Month Period Ending 6/30/XX Final Project (Part 1) EXCEL Account Number 840 845 850 855 860 865 870 875 Unadjusted Trail Balance Current Assets Payroll Tax Expense Postage Rent Salaries and Wages Telephone Travel and Entertainment Utilities Vehicle Expense Adjusting Entries Entry # 29,446 1,200 24,000 196,306 3,200 4,200 4,525 3,679 $ - - 3 of 21 Adjusted TB (PS# Part 1) Adjusted Trial Balance $ Balance Sheet Income Statement 134,195 12,500 (600) 25,000 34,600 4,600 27,500 5,000 10,000 12,500 100,000 (12,500) (3,942) (45,433) (17,157) (100,000) 4 of 21 Adjusted TB (PS# Part 1) Adjusted Trial Balance Balance Sheet Income Statement (176,800) (128,500) (76,300) (225,320) (193,400) (32,500) 70,720 32,125 34,335 90,128 87,030 5,230 3,500 500 45,433 8,000 3,000 5 of 21 Adjusted TB (PS# Part 1) Adjusted Trial Balance Balance Sheet Income Statement 29,446 1,200 24,000 196,306 3,200 4,200 4,525 3,679 $ - $ - $ - 6 of 21 Adjusted TB (PS# Part 1) Mile High Ski and Snowboard Shop Pennsylvania Entity Aggregation of Income Account Worksheet For the Eight Month Period Ending 6/30/XX Final Project -- (Part 2) EXCEL Account Number Adjusted Trial Balance Current Assets Account Aggregations Revenue 600 610 620 630 640 Clothing and accessory sales Rentals Services Ski equipment sales Snow board equipment sales 650 Other revenues Revenue - - - - Cost of Goods Sold 700 710 720 730 740 Clothing and accessory sales Rentals Services Ski equipment sales Snow board equipment sales Cost of Goods Sold 7 of 21 Income Statement (PS3 Part 2) Mile High Ski and Snowboard Shop Pennsylvania Entity Aggregation of Income Account Worksheet For the Eight Month Period Ending 6/30/XX Final Project -- (Part 2) EXCEL Account Number Adjusted Trial Balance - Current Assets Gross Profit Margin 800 812 810 805 814 825 835 845 850 865 875 815 840 855 822 860 870 820 830 Expenses Advertising Amortization Expense Depreciation Expense Bank fees Bad Debt Expense Insurance Expense Legal and accounting fees Postage Rent Travel and Entertainment Vehicle Expense Dues and Subscriptions Payroll Tax Expense Salaries and Wages Income Tax Payable Telephone Utilities Account Aggregations - Advertising Amortization Depreciation G&A G&A G&A G&A G&A G&A G&A G&A G&A Salaries & taxes Salaries and Taxes Income Taxes Utilities Utilities Employee benefits Interest Expense - Total Expenses 8 of 21 - Income Statement (PS3 Part 2) Mile High Ski and Snowboard Shop Pennsylvania Entity Aggregation of Income Account Worksheet For the Eight Month Period Ending 6/30/XX Final Project -- (Part 2) EXCEL Account Number Adjusted Trial Balance Current Assets Net Income - 9 of 21 Account Aggregations - Income Statement (PS3 Part 2) 3159 Aggregated Income Statement - - 10 of 21 Income Statement (PS3 Part 2) 3159 Aggregated Income Statement - - 11 of 21 Income Statement (PS3 Part 2) 3159 Aggregated Income Statement - 12 of 21 Income Statement (PS3 Part 2) Mile High Ski and Snowboard Shop Pennsylvania Entity Income Statement and Vertical Analysis Worksheet For the Eight Month Period Ending 6/30/XX Final Project (Part 3) EXCEL $ Totals % - Revenue Cost of Goods Sold Gross Profit Margin - Expenses: Advertising Amortization Expense Depreciation Expense Dues and Subscriptions Salaries and Wages Telephone Advertising Amortization Depreciation G&A Salaries and Taxes Utilities - Total Expenses Net Income before Interest and Taxes Income Tax Expense Interest Expense Income Taxes Interest Expense Net Income after Interest and Taxes - - 13 of 21 P&L Stmt w Vertical (PS3 Part 3 Mile High Ski and Snowboard Shop Pennsylvania Entity Aggregated Balance Sheet Worksheet 6/30/XX Final Project (Part 4) EXCEL Account Number 010 020 030 040 050 060 Current Assets Unadjusted Trail Balance Cash Accounts receivable Bad debt reserve Marketable Securities Inventory Prepaid Assets Total Current Assets - Fixed Assets 100 110 120 130 190 Computer equipment Computer Software Furniture and Fixtures Machinery Office Equipment Total Fixed Assets - Accumulated depreciation 150 160 170 180 Computer equipment Computer Software Furniture and Fixtures Machinery Total Accumulated Depreciation Net Fixed Assets - Other Assets - (long term) 210 255 Customer List Accumulated Amortization Net Intangible Assets 14 of 21 - BS (PS3 Part 4) Mile High Ski and Snowboard Shop Pennsylvania Entity Aggregated Balance Sheet Worksheet 6/30/XX Final Project (Part 4) EXCEL Total assets 300 310 320 330 335 340 400 - Current Liabilities Accounts payable Accrued payroll liability Other accrued liabilities Other short term liabilities Short-tem Portion of Note Payable Unremitted sales tax - Total Current Liabilities - Long-term Debt 500 510 Total Current and Long Term Liabilities - Owner's Equity Capital Retained earnings - Total Owner's Equity - Total Liabilities and Owners Equity - 15 of 21 BS (PS3 Part 4) Account Aggregations - - - - 16 of 21 BS (PS3 Part 4) - Other Liabilities - - - 17 of 21 BS (PS3 Part 4) Mile High Ski and Snowboard Shop Pennsylvania Entity Balance Sheet and Key Ratios 6/30/XX Final Project (Part 5) EXCEL Current Assets Liquidly Definitions and Calculations: Cash Accounts receivable Bad debt reserve Marketable Securities Inventory Prepaid Assets - Total Current Assets - Fixed Assets Accumulated Depreciation Net Fixed Assets - Current Ratio: Definition: Quick Ratio: Definition: Other Assets - (long term) Customer List Accumulated Amortization Net Intangible Assets - Total Long Term Assets Debt Equity Ratio: Total assets Definition: 18of 21 BS (PS3 Part 5) Current Liabilities Accounts payable - Short-tem Portion of Note Payable Other Liabilities - Total Current Liabilities - Long Term Debt Total Short and Long Term Liabilities - Owner's Equity Capital Retained earnings - Total Owner's Equity - Total Liabilities and Owners Equity - 19of 21 BS (PS3 Part 5) and Calculations: 20of 21 BS (PS3 Part 5) 21of 21 BS (PS3 Part 5)