I m not capable to finish project, my adjusting balance need to be 158,977, and I try in a lot of way .and other step after this.

PROJECT: SPREADSHEET APPLICATIONS FOR BOOKKEEPING The Bernards 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. Worksheet Overview If you'll be completing the project without spreadsheet software, you can access PDF copies of the required worksheets in the Resources tab in the left-hand menu. 1. Use the Adjusted Trial Balance WorksheetProject, Part 1 (tab 1). The unadjusted balances have been inserted for your convenience. Enter the adjusting entries. (Specific information relating to these entries can be found below.) Calculate the Adjusted Trial Balance. This should be similar to a simplified version shown on page 178 in your textbook. Input from the Adjusted Trial Balance, the Balance Sheet, and the Income Statement. This should be similar to that shown on page 184 in your textbook. 2. Use Project, Part 2Aggregation of Income Account Worksheet (tab 2). Aggregate the Income Statement Accounts. This should be similar to that shown on pages 211 and 212 in your textbook, although the format is slightly different. STATEMENTS, CONTROLS, AND RECORDS 99 3. Use Project, Part 3, to prepare the company's Income Statement and Vertical Analysis Worksheet (tab 3). Prepare an Income Statement and vertical analysis for the eightmonth period ending June 30th. This should be similar to that shown on page 216 in your textbook. 4. Use Project, Part 4Aggregated Balance Sheet Worksheet (tab 4). Aggregate the Balance Sheet Accounts. This should be similar to that shown on pages 198 and 199 in your textbook; again, the format is slightly different. 5. Use Project, Part 5Balance Sheet and Key Ratio Worksheet (tab 5). Prepare the Balance Sheet for the eight-month period ending June 30th. Also, calculate the current ratio, quick ratio, and debt-to-equity ratio for the Mile High Ski and Snowboard Shop. 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 completed. 2. Depreciation needs to be calculated for all of the fixed-asset accounts. Computer equipment, computer software, and furniture and fixtures will be depreciated using the straight-line method over 60 months. Machinery will be depreciated 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 principal over the past eight months. A. 100 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. STATEMENTS, CONTROLS, AND RECORDS 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-ShortTerm 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 reclassify 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 Note 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 throughout 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. STATEMENTS, CONTROLS, AND RECORDS 101 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. SUBMITTING YOUR WORK When you complete all five parts of your project, you must submit all of your work to the school. Keep in mind that the results of this project will be graded. Once you've completed all five parts of this assignment, log in to the student portal at https://students.ashworthcollege.edu. To log in, you will need the username and password that you received via email. From the Student Portal page, enter your username and password (Figure 1). Then click Login. 102 STATEMENTS, CONTROLS, AND RECORDS Figure 1 The Home or Dashboard page of the Student Portal displays. To access your courses, click Courses in the upper right-hand banner (Figure 2). Figure 2 STATEMENTS, CONTROLS, AND RECORDS 103 Click on Courses to access your My Workspace page (Figure 3). Click on More Sites and navigate to the final segment of your program (Figure 4). Figure 3 Figure 4 Click on Assignments on the left-hand navigation menu (Figure 5). Figure 5 104 STATEMENTS, CONTROLS, AND RECORDS Click on the link for your graded project under Assignment title in the center of your screen (Figure 6). Figure 6 Click on the Choose File button at the bottom of the page to locate and attach your finished project. When you've attached your file, check the Honor Pledge box to confirm that you're submitting your own work for the assignment. Then click Submit (Figure 7). Figure 7 STATEMENTS, CONTROLS, AND RECORDS 105 You'll see a confirmation message that tells you your project has been successfully submitted to the school (Figure 8). Figure 8 106 STATEMENTS, CONTROLS, AND RECORDS Grading Rubric: Spreadsheet Applications for Bookkeeping Part 1: Adjusted Trial Balance Part 2: Aggregation of Income Account Part 3: Income Statement/ Vertical Analysis Part 4: Aggregated Balance Sheet Part 5: Balance Sheet and Key Ratios Grading Considerations Points/Actual Points/Actual Points/Actual Points/Actual Points/Actual Student made a reasonable attempt to complete the project component. 2/ 2/ 2/ 2/ 2/ Student used correct Excel template (or manual worksheet) and saved properly for submission. 3/ 3/ 3/ 3/ 3/ Student accurately identified required data for each statement or balance sheet. 5/ 5/ 5/ 5/ 5/ Student accurately completed all statements and balance sheets per answer keys. 10/ 10/ 10/ 10/ 10/ 20 20 20 20 20 Total Points Possible Total Points Earned (Each Part) Sum of Points from Parts 1-5 = (Project Grade) STATEMENTS, CONTROLS, AND RECORDS Total Grade = 107 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)