AutoSave Search (AltO) Of Midterm Excel Template A - Protected View Data View File Formulas Review Developer Page Layout Help Home Insert Enable Editing PROTECTED VIEW Be carefulfiles from the Internet can contain Viruses. Unless you need to edit it's safer to stay in Protected View. A21 fx F G D A B C 5 Directions 1 Use this Excel template file (see tabs) to complete the required accounting activities and rename it to begin with your last name (i.e. Doe_Jane ACC 120 Midterm) Note: The Unadjusted Trial Balance and the Adjusted Trial Balance have been completed for you 6 7 8 10 11 IZ 13 14 2 Journalize the routine transactions described on the Journal Tab. You do not need to complete the PR section for this assignment Use the Account Names shown on the ATB 3 Use the Unadjusted Trial Balance and adjusting information provided (See Journal tab) to journalize the 7 adjusting entries (a-g.) 4. Use the Adjusted Trial Balance provided to prepare an Income Statement (Multi-Step) appropriate to a merchandising business, a Statement of Owner's Equity, and a report form Balance Sheet. These should be completed on the Financial Statements tab 15 16 17 TO 19 20 Notes/Tips: There were no investments by owner during the reporting period. This company prepares an Adjusted Trial Balance and Financial Statements at the end of each month. All adjustments are for the last 30 days. (Effective date Dec 31). Z2 Directions Journal Unadjusted Trial Balance Adjusted Trial Balance Financial Statements Ready C DE F G H ABC Company Page 1 K Note: There is no correlation between the placement of the financial transactions listed below and their placerent in the journal. General Journal During the last month of the year, the following transactions were completed: Date Description P.R. Debit Credit Dec 1 Paid rent for December, $5000. Dec 3 Purchased merchandise from Goat Company for $10,000 under terms 1/15, n/3 FOB Shipping Point Dec 4 Paid $500 cash for freight charges on purchase of Dec 3. Dec 5 Sold merchandise to Beaver company for $12,000 under credit terms 2/10, ne FOB Shipping Point. The merchandise had a cost of $5,000. Dec 6 Purchased merchandise from Duck Company for $25,000, under terms nleom (net / net end of month). Dec 8 Received $22,400 on account from Warthog Company, no discount. This sal was made in November. Dec 9 After negotiations with Goat Company concerning problems with the merchandise purchased on Dec 3, ABC Company received a credit credit memorandum from Goat Company granting an allowance of $2,000. Directions Financial Statements Journal Unadjusted Trial Balance Adjusted Trial Balance 64F Mostly cloudy A ()) ( M G 1 H J K B DE C Dec 10 Paid part-time sales clerk $3,000 which included $2,000 of Sale Salaries Payable on December 1st. (Hint: This entry will require three lines.) Dec 11 Sold merchandise on account to Cow company, terms 2110, n/60, FOB destination, $15,000. The merchandise had a cost of $9,000. Dec 12 Paid $600 cash for shipping charges related to the Dec 11 sale to Cow Company. Dec 13 Paid the amount due to Goat Company for the Dec 3 purchase less the allowance granted on Dec 9. Dec 15 Received balance due from Beaver Company for merchandise sold on Dec 5. Dec 16 Cow company returned merchandise from the Dec 11 sale that had cost ABC Company $2,000 and been sold for $5,000. The merchandise was restored to inventory. Dec 21 Received amount due from Cow company for the Dec 11 sale less the sale return on Dec 16. Dec 23 Paid advertising expense for ads running the last week of December, $1,000. Dec 24 Paid part-time sales clerk for two weeks' salary, $3,000. Financial Statements Directions Journal Unadjusted Trial Balance Adjusted Trial Balance ABC Company Unadjusted Trial Balance December 31, 20xx Cash Petty Cash Notes Receivable Accounts Receivable Merchandise Inventory Estimated Returns Inventory Prepaid Insurance Office Supplies Delivery Equipment Accumulated Depreciation-Delivery Equipment Accounts Payable Salaries Payable Customer Refunds Payable R.W. Miller, Capital RW. Miller, Drawing Sales Interest Revenue Cost of Merchandise Sold Sales Salaries Expense Directions Journal Unadypted Trial Balance Debit Credit 47,760 1,000 30,000 52,430 135,220 0 8,740 2,050 60,150 22,950 64,900 0 4,100 193.870 30,000 1,043,160 1,650 653,070 924330 Adjusted Tral Balance 0 die A B DE F G H J K M Dec 24 Paid part-time sales clerk for two weeks' salary, $3,000. Dec 27 Purchased office supplies on account, $800. Dec 31 Paid Duck Company amount due from the Dec 6 purchase. At the end of December, the following adjustment data were assembled. a After a physical count of inventory, it was determined that $134,500 of inventory exists at December 31. b 1% of sales is expected to be refunded. Estimated Cost of Merchandise that will be returned in the next year is $8,000 d Insurance Expired during the year, $3,300. e Office supplies on hand at Dec 31, $1,250. f Depreciation for December is $700. g Sales clerk earned $1,500 of unpaid and unrecorded salary as of Dec 31. Directions Financial Statements Journal Unadjusted Trial Balance Adjusted Trial Balance 1009 64F Mostly cloudy A D (1) 9:30 PM 4/12/2022 Q D 22,950 64,900 0 4,100 193,870 30,000 1,043,160 1,650 Accumulated Depreciation-Delivery Equipment Accounts Payable Salaries Payable Customer Refunds Payable R.W. Miller, Capital R.W. Miller, Drawing Sales Interest Revenue Cost of Merchandise Sold Sales Salaries Expense Rent Expense Delivery Expense Deprecation Expense-Delivery Equipment Advertising Expense Miscellaneous Selling Expense Office Salaries Expense Insurance Expense Office Supplies Expense Miscellaneous Administrative Expense Interest Expense Total 653,070 92,330 60,000 39,360 7,630 13,030 5,090 52,300 15,280 8,700 14,490 2,000 1,330,630 1,330,630 Directions Journal Unadiysted Trial Balance Adjusted Twal Balance Financial State X 8 10 12 ABC Company Adjusted Trial Balance December 31, 20xx Debit Credit 47,760 Cash 1,000 Petty Cash Notes Receivable 30,000 Accounts Receivable 52,430 Merchandise Inventory 134,500 Estimated Returns Inventory 8,000 Prepaid insurance 5,440 Office Supplies 1,250 Delivery Equipment 60,150 hccumulated Depreciation Delivery Equipment 23,650 Recounts Payable 64,900 Salaries Payable 1,500 Customer Refunds Payable 14,532 R.W. Miller, Capital 193,870 RW. Miller, Drawing 30,000 Sales 1,032,728 Interest Revenue 1,650 Cost of Merchandise Sold 645,790 Directions Journal Unadjusted Trial Balance Adjuster Trial Balance Financial statements 2 P ile Home insert Page Layout Formulas Dala PROTECTED VIEW Be careful-Files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected 11 X fo A F D E R.W. Miller, Capital 193,870 R.W. Miller, Drawing 30,000 Sales 1,032,728 Interest Revenue 1,650 Cost of Merchandise Sold 645,790 Sales Salaries Expense 93,830 Rent Expense 60,000 Delivery Expense 39,360 Deprecation Expense-Delivery Equipment 8,330 Advertising Expense 13,030 Miscellaneous Selling Expense 5,090 # Office Salaries Expense 52,300 # Insurance Expense 18,580 # Office Supplies Expense 9,500 Miscellaneous Administrative Expense 14,490 Interest Expense 2,000 Total 1,332,830 1,332,830 * ABC Company considers these items "Selling Expenses. # ABC Company considers these items "Administrative Expenses." Directions Journal Unadjusted Trial Balance Adjusted Trial Balance Financial Statem E 3 CD Note: See Chapter 6 (Exhibits 11,12,14) for proper formatting oftFinancial Statements DO NOT MODIFY FORMATTING (Multi-Step Income Statement and Report Form Balance Sheet) ABC Company ABC Company Income Statement Balance Sheet Assets Tournal Financial Statements Unadiusted Trial Balance Adjusted Trial Balance A55 -