You are required to create a 10-column work sheet that starts with an Unadjusted Trial Balance as of December 31, 2020 and ends with a Balance Sheet as of the same date. You will also be required to prepare a Balance Sheet, Statement of Retained Earnings, and an Income Statement in good form based on this worksheet. Ensure that your financial statements are correctly formatted in terms of headings, dollar signs, appropriate spacing and current verses long-term classifications.
Excel’s ability to link portions of the spreadsheet together to achieve your output requirements will prove very helpful in the completion of this assignment. You are required to use the linkage function to automatically move data from your worksheet to your financial statements. Failure to do so will result in a significant loss of credit points. Once you have entered data into the adjusting entries portion of your worksheet, your Excel template should automatically fill in the other required portions of your project. These portions of your spreadsheet coincide with the requirements of your project. You will need to have data automatically loaded into your Balance Sheet, Income Statement, Post-Closing Trial Balance, and the Adjusting and Closing entries sections of your project. Information you will need to complete this project is provided below.
The purpose of this exercise is to illustrate that the preparation of a year-end worksheet with the use of an electronic spreadsheet. Completing an electronic, rather than a manual worksheet is an extremely simple and practical way for the accountant to save time during the month-end closing process. Additionally, the project should reacquaint students with the basic procedural reporting tasks normally associated with any accounting information system, and the corporate form of a business entity.
REQUIRED MATERIALS TO BE INCLUDED IN YOUR PROJECT FOLDER:
Note: Carefully review these requirements before you turn in your project. Failure to address each of these in your completed project will result in a significant loss of points.
- Include two typed “COVER SHEETS” in your packet of materials. Reference the proper format for this sheet below. Type in your name and sign the “No Assistance Form” and insert this form directly behind the two copies of your COVER SHEETS.
- Printout of your 10-column worksheet (Landscape formatting) on one sheet of paper.
- Printout of your three Financial Statements and Post-Closing Trial Balance (Portrait formatting), each on a separate page.
- Printout of your Adjusting and Closing entries on separate pages.
- Ensure that you use appropriate headings for the items listed above and all statements and entries are in good form.
- SanDisk Card (SD card) or thumb drive that contains your complete Excel worksheet project. Put your SD card (or flash drive) in an envelope, seal the envelope, and staple it to the BACK of one of your cover sheets. Ensure that you place the two cover sheets at the front of your project. An SD card is preferred (or a MICRO-SD & adapter) because it is less expensive and more reliable than the thumb drives have been on recent projects in the course.
- For simplicity, students may use a closable manila envelope/folder that allows all printouts and accompanying SD card or thumb drive to be placed into the folder. If you are unsure of the type of folder to use, check with your instructor prior to project submission. To give students the maximum time possible to complete this project, the assignment due date is scheduled for the last week of classes this semester. Also, students will have one free day during the semester to complete the project. The project must be completed for students to pass the course.
- Ensure that your SD card or thumb drive is free of any malware and is of sufficient size to hold your entire project for this assignment.
- Turn your project in to the professor in his office (during office hours) by 2:00 p.m., April 23, 2020, or earlier.
- Late projects will not be accepted. Plan accordingly. Ensure that you bring it to the instructor’s office on time. Early submission is HIGHLY RECOMMENDED.
BACKGROUND DATA AND PROJECT REQUIREMENTS:
- Prepare a 10-column worksheet for December 31, 2020, starting with the unadjusted trial balance and including adjusting and closing entries based on these additional facts:
- Annual depreciation is calculated using the units of production method utilizing the number of items processed through the manufacturing equipment during the year. The original cost of equipment for Isaiah, Inc. was $34,200 which the corporation paid for using a combination of stock and current assets. Depreciation for the year was calculated to be $3,700. One of the members of the BOD espoused the belief that no expense should be recorded for the depreciation calculation and instead the depreciation of the equipment should be charged directly to the equipment account.
- An analysis of the accruals of revenues and expenses revealed that company’s employees have earned $800 of accrued wages at fiscal year-end.
- Using a physical count of inventory at year-end, Isaiah, Inc. determined that its merchandise inventory as no longer $19,000 but had risen to $21,000. No adjusting or closing entry has been recorded for this change in inventory balance. A new employee in the accounting department argues that no entry is required because the company should be using a perpetual inventory system anyway.
- Isaiah, Inc. follows the practice of recording all deferred expenses as permanent accounts. A year-end inventory count of supplies indicated that $800 was the amount available for future periods. One new accounting clerk argues that all deferred expenses should initially be recorded as expenses because that ensures that the resultant adjusting entry is done correctly.
- Based on calculations relating to the current remaining term of applicable insurance polices, the cost of expired insurance for the fiscal year was $600. Future rate increases for next year will require the company to nearly double its provision for insurance expense.
- Enter the adjusting entries required into your spreadsheet. Then complete the spreadsheet columns using Excel formulas or other linking functions to efficiently move the data. Ensure that your spreadsheet template performs requisite calculations accurately after totals are manually entered into the column reserved for adjustments. Merely inputting the data manually IN ALL THE COLUMNS will decrease your score by 50%.
- Prepare the Income Statement and the Classified Balance Sheet for Isaiah Corporation. Also prepare a Post-Closing Trial Balance. These should be prepared in Excel, using the linkage functions, and should be printed out separately for your final project submission. Remember: To create and print these financial statements, you are required to use Excel’s linking functions, or some other methodology within the Excel template, that will automatically move data from your template to the pre-formatted financial statements required. Merely inputting the data manually will decrease your score by 50%.
- Generate both your adjusting and closing entries in your Excel template. These journal entries should be in proper form and dated appropriately. Print these entries on two different sheets of paper. Again, these should be linked to the data in your electronic worksheet and should be generated automatically by your Excel template. Posting and posting reference columns are not necessary.
- Unadjusted trial balance amounts are as follows: (next page)
|Unadjusted Trial Balance for Isaiah, Inc., December 31, 2020:
ACCOUNT DEBIT CREDIT
Accounts Receivable 11200
Merchandise Inventory 19000
Prepaid Insurance 900
Accumulated Dep. – Eq. 3700
Accounts Payable 16000
Common Stock 10000
Retained Earnings 32600
Sales Returns & Allowances 2000
Sales Discounts 4300
Purchase Returns & Allowances 1500
Purchase Discounts 4200
Salaries Expense 43000
Rent Expense 9000
Advertising Expense 11300
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at firstname.lastname@example.org with your version of question.