Adjusting Journal Entry information as of January 31: Excel: Financial Accounting Review Practice Set Page 8 of 9 Excel: Financial Accounting Review Practice Set Adjusting Entries - record necessary adjusting entries based on facts provided These should flow to columns I and J of the Trial Balance worksheet Date JE \# Event Account \# Account Title Debit Credit Entry Description COLUMN A MUST CONTAIN THE ENTRY DATE COLUMN B SHOULD BE A SEQUENTIAL ENTRY NUMBER (ONLY ON ROW 1 OF EACH ENTRY) COLUMN C SHOULD BE THE EVENT NUMBER FROM THE ASSIGNMENT (ONLY ON ROW 1 OF EACH ENTRY) COLUMN D SHOULD BE THE GENERAL LEDGER ACCOUNT NUMBER COLUMN E MUST BE A FORMULA THAT USES THE ACCOUNT NUMBER FROM COLUMN C TO RETURN THE ACCOUNT TITLE COLUMNS F AND G CONTAIN DEBIT/CREDIT VALUES. BE SURE THAT ENTRIES BALANCE COLUMN H IS BLANK COLUMN I SHOULD BE A SHORT DESCRIPTION OF THE EVENT (ONLY ON ROW 1 OF EACH ENTRY) PUT A SUM FORMULA AT THE BOTTOM OF THE DEBIT/CREDIT COLUMNS ON THIS WORKSHEET Excel: Financial Accounting Review Practice Set Events During January (not all events will require a journal entry) Excel: Financial Accounting Review Practice Set General Jo urnal - record entries for opening balances and for events 1-28 (where required) These should flow to columns E and F of the Trial Balance worksheet \begin{tabular}{|l|c|c|c|c|c|c|c|c|c|c|} \hline Date & JE \# & Event & Account \# & Account Title & Debit & Credit & Entry Description \\ \hline \end{tabular} COLUMN B SHOULD BE A SEQUENTIAL ENTRY NUMBER (ONLY ON ROW 1 OF EACH ENTRY) COLUMN C SHOULD BE THE EVENT NUMBER FROM THE ASSIGNMENT (ONLY ON ROW 1 OF EACH ENTRY) COLUMN D SHOULD BE THE GENERAL LEDGER ACCOUNT NUMBER COLUMN E MUST BE A FORMULA THAT USES THE ACCOUNT NUMBER FROM COLUMN C TO RETURN THE ACCOUNT TITLE COLUMNS F AND G CONTAIN DEBIT/CREDIT VALUES. BE SURE THAT ENTRIES BALANCE COLUMN H IS BLANK COLUMN I SHOULD BE A SHORT DESCRIPTION OF THE EVENT (ONLY ON ROW 1 OF EACH ENTRY) PUT A SUM FORMULA AT THE BOTTOM OF THE DEBIT/CREDIT COLUMNS ON THIS WORKSHEET Bank Account Miscellaneous Accounts Receivable Allowance for Bad Debt Inventory-Operating Supplies Inventory-Raw Materials (Direct Post) Inventory-Finished Goods (Direct Post) Inventory-Trading Goods (Direct Post) Prepaid Insurance Prepaid Advertising Land (Direct Post) Property, Plant \& Equipment (Direct Post) Accumulated Depreciation-PP\&E (Direct Post) Payables-Miscellaneous Payables-Salaries and Wages Accrued Expenses Accrued Tax-Output Common Stock Retained Earnings (Direct Posting) 252,518.00 108,420.00 750.00 32,000.00 281,298.00 66,474.00 5,000.00 1,000.00 425,000.00 915,000.00 beginning balances from Dec31st 305,000.00 47,900.00 110,000.00 988.00 3,063.00 1,000,000.00 618,009.00 1/3/23 2 Assignment Overview 1. Download the Excel template file to use for this assignment. 2. Review the grading rubric to understand grading criteria. It contains some check figures that you should match. 3. Use the information provided above for events 1-28 to determine which events require journal entries on the General Journal worksheet. Be sure to use proper Excel formulas where required, such as to fulfill the requirement that account titles change when account numbers are changed. 4. Use the adjusting entry information above to determine when an adjusting entry is warranted and make those entries on the Adjusting Entries worksheet. 5. All entries are to be rounded to the nearest dollar. 6. Proper Excel formulas showing calculations are required, at a minimum, for the bad debts and depreciation adjusting entries. 7. Format all cells with dollar amounts to show 2 decimal places on all worksheets. 8. On the trial balance page, all cells you complete in columns C through L must have Excel formulas. I have already filled in columns A and B for the accounts that are needed on the trial balance. Formula guidance: - Columns C and D use data from the General Journal worksheet - Columns E and F use data from the Adjusting Entries worksheet - Columns G and H use data from columns C through F. Debit/credit cells on each row must contain a formula because I will change entries and I expect debits to change to credits (and vice versa) accordingly. - The closing entries should be formulas for all income statement accounts using data in columns G and H. Again, be sure to write proper formulas so that when I change journal entries to force the company into a loss, for example, the entries react accordingly (changing from debit to credit and vice versa). - Columns K and L will use data from column G through J. All income statement accounts should be zero in columns K and L. 9. If you have questions, use the Discussions area on D2L to get assistance. Please make the subject of your post the event number. Use separate posts if you have questions about multiple events and do not start a new thread on an event that someone else has already started (i.e. continue discussion about that event inside the already established thread). 10. Do not wait to start this assignment until the weekend it is due. There is plenty of time to do this assignment well and accurately if you start early. 11. As with all assignments this semester, if I have time, I will grade what has been submitted even before the assignment deadline. You are welcome to revise any submission based on feedback I have provided to attempt to earn full credit by turning in a revised file. I will always grade the most recent file submitted for your updated and/or final score. 12. You will use the journal entries you create for this assignment later this semester in SAP (module 6). If you have incorrect entries in Excel, you will either need to fix those prior to entry into SAP or you will lose points on the SAP assignment for the same errors being repeated. Choose to work on this assignment to arrive at the correct entries and save yourself the trouble of having to do so later! - Brenden Excel: Financial Accounting Review Practice Set Page 9 of 9 Chart of Accounts The chart of accounts (COA) is a full listing of general ledger accounts sorted by a unique account number. Generally, there are no requirements about how accounts are numbered and organized, however in some places (e.g., France) there are formal regulations regarding the structure of an entity's CoA. For this reason, SAP provides for alternate CoAs for international entities. The number of accounts in the CoA will vary widely depending on the complexity of the business entity. Taxonomy and Nomenclature In most every (computerized) accounting system, journal entries are made by entering the account number, not the account title. Therefore, the account number may be used more frequently that the title to identify the account. Creating a CoA for a large corporation can be a complex and important task. Common practice is to list the accounts in the precise order that they are presented on the balance sheet and then the income statement, however, the system will not enforce this. It is up to the accountant responsible to order accounts well and maintain consistent nomenclature in the account titles. In large organizations, the account taxonomy can become complex and account numbers are just as, if not more, important as the account title. A well-designed taxonomy for assigning account numbers based on the nature of the account is good practice. Account numbers may be as short as three of four digits, or as many as needed. Your SAP system uses six-digit account numbers with a simple taxonomy where the first two digits represent the intrinsic nature of the account. In the case of GBI, account numbers 100000 through 119999 are designated for cash or liquid assets. Among these accounts, the second digit (0) further specifies cash or (1) accounts receivable. In some companies, later segments (or blocks) of the account number can represent business units, product groups, locations, or even sales channels. You may notice that there is a large gap between the account numbers of the balance sheet and the income statement. This is somewhat unique to GBI. Also, of note, is that GBI lists Cost of Goods Sold (out of order) as the final account. Reconciliation Accounts In your integrated SAP system, Accounts Receivable (A/R), Accounts Payable (A/P), Fixed Assets and Inventory are configured as reconciliation accounts and cannot be posted to via manual journal entries. These reconciliation accounts are linked to subsidiary (or sub-ledger) accounts which are posted to directly via activities within the order-to-cash (SD), production (PP), and procure-to-pay (MM) business processes (modules). For example, when a sale is completed in the order-to-cash process, SAP will record the entries in the subledger and in the General Ledger A/R account. By disallowing manual journal entries to reconciliation accounts, the system keeps the general ledger and the sub-ledger consistent. For the purposes of this assignment (entering the beginning trial balance), your system has A/R, A/P, Fixed Assets, and Inventory "Direct Post" accounts which will allow posting via manual journal entry. The complete chart of accounts is available in the Excel template file. To complete this assignment, you do not need to use all the accounts in the COA. Accounts that are not used are marked accordingly. Excel: Financial Accounting Review Practice Set Page 2 of 9 Global Bik Inc. Trial Balance ONLY FORMULAS MAY BE USED IN COLUMNS C THROUGH L. ENTERING ANY VALUES (INCLUDING ZERO) WILL RESULT IN POINTS LOST Account 100000 Bank Account 110100 Miscellaneous Accounts Receivable 110400 Allowance for Bad Debt 200600 Inventory-Operating Supplies 200900 Inventory-Raw Materials (Direct Post) 200910 Inventory-Finished Goods (Direct Post) 200920 Inventory-Trading Goods (Direct Post) 210000 Prepaid Insurance 212000 Prepaid Advertising 215000 Prepaid Rent 216000 Deposits on Purchases 220110 Land (Direct Post) 220210 Property, Plant \& Equipment (Direct Post) 220310 Accumulated Depreciation-PP\&E (Direct Post) 300200 Payables-Miscellaneous 300700 Payables-Salaries and Wages 300800 Accrued Expenses 320000 Accrued Tax-Output 329000 Common Stock 330100 Retained Earnings (Direct Posting) 600000 Sales Revenue 610000 Sales Discounts 700000 Labor Expense 740000 Supplies Expense 740300 Rent Expense 740400 Insurance Expense 741200 Bad Debt Expense 741500 Utilities Expense 741800 Depreciation Expense 741900 Advertising Expense 780000 Cost of Goods Sold General Journal (including beginning balances) DR Adjustments DR Adjusted Balance DR EOM Closing Entries CR DR CR Post-Close Trial Balance DR CR Adjusting Journal Entry information as of January 31: Excel: Financial Accounting Review Practice Set Page 8 of 9 Excel: Financial Accounting Review Practice Set Adjusting Entries - record necessary adjusting entries based on facts provided These should flow to columns I and J of the Trial Balance worksheet Date JE \# Event Account \# Account Title Debit Credit Entry Description COLUMN A MUST CONTAIN THE ENTRY DATE COLUMN B SHOULD BE A SEQUENTIAL ENTRY NUMBER (ONLY ON ROW 1 OF EACH ENTRY) COLUMN C SHOULD BE THE EVENT NUMBER FROM THE ASSIGNMENT (ONLY ON ROW 1 OF EACH ENTRY) COLUMN D SHOULD BE THE GENERAL LEDGER ACCOUNT NUMBER COLUMN E MUST BE A FORMULA THAT USES THE ACCOUNT NUMBER FROM COLUMN C TO RETURN THE ACCOUNT TITLE COLUMNS F AND G CONTAIN DEBIT/CREDIT VALUES. BE SURE THAT ENTRIES BALANCE COLUMN H IS BLANK COLUMN I SHOULD BE A SHORT DESCRIPTION OF THE EVENT (ONLY ON ROW 1 OF EACH ENTRY) PUT A SUM FORMULA AT THE BOTTOM OF THE DEBIT/CREDIT COLUMNS ON THIS WORKSHEET Excel: Financial Accounting Review Practice Set Events During January (not all events will require a journal entry) Excel: Financial Accounting Review Practice Set General Jo urnal - record entries for opening balances and for events 1-28 (where required) These should flow to columns E and F of the Trial Balance worksheet \begin{tabular}{|l|c|c|c|c|c|c|c|c|c|c|} \hline Date & JE \# & Event & Account \# & Account Title & Debit & Credit & Entry Description \\ \hline \end{tabular} COLUMN B SHOULD BE A SEQUENTIAL ENTRY NUMBER (ONLY ON ROW 1 OF EACH ENTRY) COLUMN C SHOULD BE THE EVENT NUMBER FROM THE ASSIGNMENT (ONLY ON ROW 1 OF EACH ENTRY) COLUMN D SHOULD BE THE GENERAL LEDGER ACCOUNT NUMBER COLUMN E MUST BE A FORMULA THAT USES THE ACCOUNT NUMBER FROM COLUMN C TO RETURN THE ACCOUNT TITLE COLUMNS F AND G CONTAIN DEBIT/CREDIT VALUES. BE SURE THAT ENTRIES BALANCE COLUMN H IS BLANK COLUMN I SHOULD BE A SHORT DESCRIPTION OF THE EVENT (ONLY ON ROW 1 OF EACH ENTRY) PUT A SUM FORMULA AT THE BOTTOM OF THE DEBIT/CREDIT COLUMNS ON THIS WORKSHEET Bank Account Miscellaneous Accounts Receivable Allowance for Bad Debt Inventory-Operating Supplies Inventory-Raw Materials (Direct Post) Inventory-Finished Goods (Direct Post) Inventory-Trading Goods (Direct Post) Prepaid Insurance Prepaid Advertising Land (Direct Post) Property, Plant \& Equipment (Direct Post) Accumulated Depreciation-PP\&E (Direct Post) Payables-Miscellaneous Payables-Salaries and Wages Accrued Expenses Accrued Tax-Output Common Stock Retained Earnings (Direct Posting) 252,518.00 108,420.00 750.00 32,000.00 281,298.00 66,474.00 5,000.00 1,000.00 425,000.00 915,000.00 beginning balances from Dec31st 305,000.00 47,900.00 110,000.00 988.00 3,063.00 1,000,000.00 618,009.00 1/3/23 2 Assignment Overview 1. Download the Excel template file to use for this assignment. 2. Review the grading rubric to understand grading criteria. It contains some check figures that you should match. 3. Use the information provided above for events 1-28 to determine which events require journal entries on the General Journal worksheet. Be sure to use proper Excel formulas where required, such as to fulfill the requirement that account titles change when account numbers are changed. 4. Use the adjusting entry information above to determine when an adjusting entry is warranted and make those entries on the Adjusting Entries worksheet. 5. All entries are to be rounded to the nearest dollar. 6. Proper Excel formulas showing calculations are required, at a minimum, for the bad debts and depreciation adjusting entries. 7. Format all cells with dollar amounts to show 2 decimal places on all worksheets. 8. On the trial balance page, all cells you complete in columns C through L must have Excel formulas. I have already filled in columns A and B for the accounts that are needed on the trial balance. Formula guidance: - Columns C and D use data from the General Journal worksheet - Columns E and F use data from the Adjusting Entries worksheet - Columns G and H use data from columns C through F. Debit/credit cells on each row must contain a formula because I will change entries and I expect debits to change to credits (and vice versa) accordingly. - The closing entries should be formulas for all income statement accounts using data in columns G and H. Again, be sure to write proper formulas so that when I change journal entries to force the company into a loss, for example, the entries react accordingly (changing from debit to credit and vice versa). - Columns K and L will use data from column G through J. All income statement accounts should be zero in columns K and L. 9. If you have questions, use the Discussions area on D2L to get assistance. Please make the subject of your post the event number. Use separate posts if you have questions about multiple events and do not start a new thread on an event that someone else has already started (i.e. continue discussion about that event inside the already established thread). 10. Do not wait to start this assignment until the weekend it is due. There is plenty of time to do this assignment well and accurately if you start early. 11. As with all assignments this semester, if I have time, I will grade what has been submitted even before the assignment deadline. You are welcome to revise any submission based on feedback I have provided to attempt to earn full credit by turning in a revised file. I will always grade the most recent file submitted for your updated and/or final score. 12. You will use the journal entries you create for this assignment later this semester in SAP (module 6). If you have incorrect entries in Excel, you will either need to fix those prior to entry into SAP or you will lose points on the SAP assignment for the same errors being repeated. Choose to work on this assignment to arrive at the correct entries and save yourself the trouble of having to do so later! - Brenden Excel: Financial Accounting Review Practice Set Page 9 of 9 Chart of Accounts The chart of accounts (COA) is a full listing of general ledger accounts sorted by a unique account number. Generally, there are no requirements about how accounts are numbered and organized, however in some places (e.g., France) there are formal regulations regarding the structure of an entity's CoA. For this reason, SAP provides for alternate CoAs for international entities. The number of accounts in the CoA will vary widely depending on the complexity of the business entity. Taxonomy and Nomenclature In most every (computerized) accounting system, journal entries are made by entering the account number, not the account title. Therefore, the account number may be used more frequently that the title to identify the account. Creating a CoA for a large corporation can be a complex and important task. Common practice is to list the accounts in the precise order that they are presented on the balance sheet and then the income statement, however, the system will not enforce this. It is up to the accountant responsible to order accounts well and maintain consistent nomenclature in the account titles. In large organizations, the account taxonomy can become complex and account numbers are just as, if not more, important as the account title. A well-designed taxonomy for assigning account numbers based on the nature of the account is good practice. Account numbers may be as short as three of four digits, or as many as needed. Your SAP system uses six-digit account numbers with a simple taxonomy where the first two digits represent the intrinsic nature of the account. In the case of GBI, account numbers 100000 through 119999 are designated for cash or liquid assets. Among these accounts, the second digit (0) further specifies cash or (1) accounts receivable. In some companies, later segments (or blocks) of the account number can represent business units, product groups, locations, or even sales channels. You may notice that there is a large gap between the account numbers of the balance sheet and the income statement. This is somewhat unique to GBI. Also, of note, is that GBI lists Cost of Goods Sold (out of order) as the final account. Reconciliation Accounts In your integrated SAP system, Accounts Receivable (A/R), Accounts Payable (A/P), Fixed Assets and Inventory are configured as reconciliation accounts and cannot be posted to via manual journal entries. These reconciliation accounts are linked to subsidiary (or sub-ledger) accounts which are posted to directly via activities within the order-to-cash (SD), production (PP), and procure-to-pay (MM) business processes (modules). For example, when a sale is completed in the order-to-cash process, SAP will record the entries in the subledger and in the General Ledger A/R account. By disallowing manual journal entries to reconciliation accounts, the system keeps the general ledger and the sub-ledger consistent. For the purposes of this assignment (entering the beginning trial balance), your system has A/R, A/P, Fixed Assets, and Inventory "Direct Post" accounts which will allow posting via manual journal entry. The complete chart of accounts is available in the Excel template file. To complete this assignment, you do not need to use all the accounts in the COA. Accounts that are not used are marked accordingly. Excel: Financial Accounting Review Practice Set Page 2 of 9 Global Bik Inc. Trial Balance ONLY FORMULAS MAY BE USED IN COLUMNS C THROUGH L. ENTERING ANY VALUES (INCLUDING ZERO) WILL RESULT IN POINTS LOST Account 100000 Bank Account 110100 Miscellaneous Accounts Receivable 110400 Allowance for Bad Debt 200600 Inventory-Operating Supplies 200900 Inventory-Raw Materials (Direct Post) 200910 Inventory-Finished Goods (Direct Post) 200920 Inventory-Trading Goods (Direct Post) 210000 Prepaid Insurance 212000 Prepaid Advertising 215000 Prepaid Rent 216000 Deposits on Purchases 220110 Land (Direct Post) 220210 Property, Plant \& Equipment (Direct Post) 220310 Accumulated Depreciation-PP\&E (Direct Post) 300200 Payables-Miscellaneous 300700 Payables-Salaries and Wages 300800 Accrued Expenses 320000 Accrued Tax-Output 329000 Common Stock 330100 Retained Earnings (Direct Posting) 600000 Sales Revenue 610000 Sales Discounts 700000 Labor Expense 740000 Supplies Expense 740300 Rent Expense 740400 Insurance Expense 741200 Bad Debt Expense 741500 Utilities Expense 741800 Depreciation Expense 741900 Advertising Expense 780000 Cost of Goods Sold General Journal (including beginning balances) DR Adjustments DR Adjusted Balance DR EOM Closing Entries CR DR CR Post-Close Trial Balance DR CR