GLOBAL BIKE EXCEL ASSIGNMENT

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. Analyze Events and Prepare Journal Entries Following, you will find the GBI Post-Close Trial Balance as of December 31st which must be posted as a manual journal entry into your brand-new SAP system (in module 8). You will need to do the same in Excel. Please record this as Event 0. Additionally, you are provided descriptions of routine events occurring during January for which you are to make general journal entries in a manual accounting system (Excel spreadsheet). Your manual accounting system needs to include a general journal worksheet and a trial balance worksheet, all of which will be generated in Excel. An Excel template file has been provided in the correct format. Download the template file to your computer and use it to complete this assignment. Excel formulas must be used where indicated. Part of the scoring of this assignment will be based on changing journal entries in ways that should flow through correctly to the trial balance. A grading rubric has been provided for this assignment. To simulate the practice of running parallel systems, in module 8 you will post your manual system entries into the SAP system. After you post all your entries into SAP, the resulting trial balances from your manual system and the SAP system should match exactly, validating the extra work required of running parallel systems for the month of January Manual Accounting Cycle Process 1. Record the 12/31 Post-close Trial Balance as the 1/1 Beginning Trial Balance as a single journal entry (event zero). 2. Record the daily transactions if appropriate, (some events may not involve journal entries), as general journal entries into Excel. Your entries should flow into the appropriate calculations on your trial balance worksheet. You should create links and use appropriate formulas between your spreadsheets to expedite this process and minimize the risk of an error in data entry. 3. The next step is to record the adjusting entries into the general journal and to create formulas in the trial balance to pull the results of these entries onto that worksheet. 4. Record closing entries in your trial balance as if this were a year-end close. These are to be entered onlv on the trial balance worksheet. Trial Balance as of December 31 (enter as event zero on the journal entry worksheet). Events During January (not all events will require a journal entry) Adjusting Journal Entry information as of January 31: \begin{tabular}{|c|c|l|l|} \hline 92 & 780000 & Cost of Goods Sold & \\ 93 & & & \\ 94 & Non-reporting accounts (disregard for financial accounting) \\ \hline 95 & These accounts are used for SAP internal processes and not part of the GL Trial E \\ \hline 96 & 800000 & Labor allocation & NOT USED \\ 97 & 800100 & Assessed Costs & NOT USED \\ 98 & 800200 & Internal Order Settlement & NOT USED \\ 99 & 800300 & Production Order Settlement & NOT USED \\ 100 & 800400 & EAM Cost Allocation & NOT USED \\ 101 & 800500 & EAM Settlement & NOT USED \\ 102 & 800600 & Customer Service Settlement & NOT USED \\ 103 & 800700 & Service Labor Allocation & NOT USED \\ 104 & 800800 & Customer Service Labor Assessment & NOT USED \\ 105 & 900000 & Human Resources Trade Fair & NOT USED \\ 106 & 900100 & Human Resources Trade Fair Assessment & NOT USED \\ 107 & 900200 & IT Programming Allocation & NOT USED \\ 108 & 900300 & Raw Material Settlement & NOT USED \\ 109 & & & \end{tabular} 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. Analyze Events and Prepare Journal Entries Following, you will find the GBI Post-Close Trial Balance as of December 31st which must be posted as a manual journal entry into your brand-new SAP system (in module 8). You will need to do the same in Excel. Please record this as Event 0. Additionally, you are provided descriptions of routine events occurring during January for which you are to make general journal entries in a manual accounting system (Excel spreadsheet). Your manual accounting system needs to include a general journal worksheet and a trial balance worksheet, all of which will be generated in Excel. An Excel template file has been provided in the correct format. Download the template file to your computer and use it to complete this assignment. Excel formulas must be used where indicated. Part of the scoring of this assignment will be based on changing journal entries in ways that should flow through correctly to the trial balance. A grading rubric has been provided for this assignment. To simulate the practice of running parallel systems, in module 8 you will post your manual system entries into the SAP system. After you post all your entries into SAP, the resulting trial balances from your manual system and the SAP system should match exactly, validating the extra work required of running parallel systems for the month of January Manual Accounting Cycle Process 1. Record the 12/31 Post-close Trial Balance as the 1/1 Beginning Trial Balance as a single journal entry (event zero). 2. Record the daily transactions if appropriate, (some events may not involve journal entries), as general journal entries into Excel. Your entries should flow into the appropriate calculations on your trial balance worksheet. You should create links and use appropriate formulas between your spreadsheets to expedite this process and minimize the risk of an error in data entry. 3. The next step is to record the adjusting entries into the general journal and to create formulas in the trial balance to pull the results of these entries onto that worksheet. 4. Record closing entries in your trial balance as if this were a year-end close. These are to be entered onlv on the trial balance worksheet. Trial Balance as of December 31 (enter as event zero on the journal entry worksheet). Events During January (not all events will require a journal entry) Adjusting Journal Entry information as of January 31: \begin{tabular}{|c|c|l|l|} \hline 92 & 780000 & Cost of Goods Sold & \\ 93 & & & \\ 94 & Non-reporting accounts (disregard for financial accounting) \\ \hline 95 & These accounts are used for SAP internal processes and not part of the GL Trial E \\ \hline 96 & 800000 & Labor allocation & NOT USED \\ 97 & 800100 & Assessed Costs & NOT USED \\ 98 & 800200 & Internal Order Settlement & NOT USED \\ 99 & 800300 & Production Order Settlement & NOT USED \\ 100 & 800400 & EAM Cost Allocation & NOT USED \\ 101 & 800500 & EAM Settlement & NOT USED \\ 102 & 800600 & Customer Service Settlement & NOT USED \\ 103 & 800700 & Service Labor Allocation & NOT USED \\ 104 & 800800 & Customer Service Labor Assessment & NOT USED \\ 105 & 900000 & Human Resources Trade Fair & NOT USED \\ 106 & 900100 & Human Resources Trade Fair Assessment & NOT USED \\ 107 & 900200 & IT Programming Allocation & NOT USED \\ 108 & 900300 & Raw Material Settlement & NOT USED \\ 109 & & & \end{tabular}