I have this Accounts assignment that needs to be done. Need help on that

Statement of individual work Individual work required This project is designed to assure that all students, individually, have the basic skills and knowledge necessary to participate in class discussion and complete course material that build on these skills. Therefore, individual performance is necessary. DO NOT work in teams on the project. Under no circumstances should any two students be sitting at one computer throughout their work on the project. To work together in this manner would constitute academic dishonesty for both parties involved. To ask someone else to do the work for you, or to agree to do the work for someone else, constitutes academic dishonesty (for more details about individual work and ethics requirements, see ethics standards in the syllabus). Statement of Individual Work To receive credit for the project, you must complete this statement requirement. Type your name where indicated on the signature line. Your typed name represents your verification of individual work on the assignment. I have read the \"Individual Work Required\" paragraph (above), and I have followed the guidelines described therein. Student name typed here. Type your name in cell A12 as signature. Time spent While completing the project, please keep a record of time spent, and enter the number of hours here when you are completed with the project: xx hours Student name typed here. Instructions Enter summation formula in the yellow cell. Enter transaction data in the gray cells, as appropriate, using the transaction information in Appendix 1. There may be more gray rows than what is needed for the transaction data, in which case merely leave those rows empty. Smart Fit Company Credit Sales Journal Date Invoice number Customer Account number Amount Total Customer master file Customer account number 444 555 666 777 Customer name Margaret Rose University Matt Leitao Steve Deary Melinda Flinchum Instructions Student name typed here. Enter formulas in the yellow cells as appropriate. Enter transaction data in the gray cells, as appropriate, using the transaction information in Appendix 1. There may be more gray rows than what is needed for the transaction data, in which case merely leave those rows empty. Smart Fit Company Cash Receipts Journal Date Total Receipt Number Amount General ledger account to be credited Account credited Notes Common Accounts Receivable Payable Stock Customer account number 120 350 400 Service Fees 510 Instructions Student name typed here. Enter formulas in the yellow cells as appropriate. Use the formula created in the cash receipts journal as a guide. Begin with entering the formula for cell E6. Then copy/paste to the remaining cells in E6 : I19. Enter transaction data in the gray cells, as appropriate, using the transaction information in Appendix 1. There may be more gray rows than what is needed for the transaction data, in which case merely leave those rows empty. Smart Fit Company Cash Disbursements Journal Account debited Date Total Check Number Account number to be debited Dollar amount 0 Supplies Equipment Accounts Payable Accrued Expense Notes Payable Dividends 160 210 310 320 350 420 Salaries Other Selling and and Employee Administrative expense Expense 610 690 Instructions Student name typed here. Enter transaction data in the gray cells, as appropriate, using the transaction information in Appendix 1. There may be more gray rows than what is needed for the transaction data, in which case merely leave those rows empty. Formulas in this worksheet are already completed for you. Chart of accounts is provided below under "Summary entry . . ." Smart Fit Company General Journal Date Total reference number Debit Account number Credit Account number Amount Description Amount 0 0 Summary entry for the month Account number 101 120 160 210 215 310 320 350 400 410 420 510 610 660 690 Account name Debit Cash Accounts Receivable Supplies Equipment Accumulated Depreciation Accounts Payable Accrued Expense Notes Payable Common Stock Retained Earnings Dividends Service Fees Salaries and Employee expense Depreciation Expense Other Selling and Administrative Expense Credit 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 - 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 - Instructions Student name typed here. Enter transaction data in the gray cells, as appropriate, using the transaction information in the credit sales journal and the cash receipts journal. There may be more gray rows than what is needed for the transaction data, in which case merely leave those rows empty. Complete the yellow-highlighted cells in the MargaretRose University account. Use the other account formulas as a guide. Accounts receivable subsidiary ledger Customer number: 444 Customer name: Margaret Rose University Date Beginning balance Source of posting Debit Credit Balance 0 Debit Credit Balance 0 0 0 0 0 0 0 0 0 Debit Credit Balance 0 0 0 0 0 0 0 0 0 Debit Credit Balance 0 0 0 0 0 0 0 0 0 Debit Credit Balance 0 0 0 0 0 0 0 0 0 Month end balance Customer number: 555 Customer name: Matt Leitao Date Beginning balance Source of posting Month end balance Customer number: 666 Customer name: Steve Deary Date Beginning balance Source of posting Month end balance Customer number: 777 Customer name: Melinda Flinchum Date Beginning balance Source of posting Month end balance Customer number: [xxx] Customer name: [new customer] Date Beginning balance Month end balance Source of posting Instructions Student name typed here. Complete the yellow-highlighted cells in the MargaretRose University account. Use the other account formulas as a guide. Accounts receivable trial balance Customer account number 444 555 666 777 Customer name Margaret Rose University Matt Leitao Steve Deary Melinda Flinchum Total Balance Instructions Student name typed here. Enter formulas in the cells in rows 8, 9, 10 to pull data from journals. The cash and the accounts receivable accounts have been completed for you. Complete all remaining accounts. Smart Fit Company General Ledger Cash 101 Debit Accounts Receivable 120 Credit Beginning Balance Total from Credit Sales Journal Total from Cash Receipts Total from Cash Disbursement Total from General Journal Ending Balance 0 Debit 0 Credit Debit 0 0 0 0 Supplies 160 0 0 0 0 Credit 0 0 0 Equipment 210 Debit Credit 0 Accumulated Depreciation 215 Debit Accounts Payable 310 Credit Debit 0 Accrued Expense 320 Credit Debit 0 Notes Payable 350 Credit Debit 0 Common Stock 400 Credit Debit 0 Retained Earnings 410 Credit Debit 0 Credit Dividends 420 Debit 0 Credit 0 Salaries and Employee expense 610 Service Fees 510 Debit Credit Debit 0 Credit 0 Depreciation Expense 660 Debit Credit 0 Other Selling and Administrative Expense 690 Debit Credit 0 Instructions Enter formulas in the yellow cells as appropriate. Accounts receivable subsidiary ledger reconciliation Balance per accounts receivable trial balance Balance per general ledger Unreconciled difference Student name typed here. Instructions Student name typed here. Enter formulas in the yellow cells to pull ending-balance data from the general ledger. The formula has been entered for you in the cell with the dotted-line borders. Use that formula as a guide for creating the remaining formulas. Smart Fit Company Adjusted Trial Balance Account number 101 120 160 210 215 310 320 350 400 410 420 510 610 660 690 Account name Cash Accounts Receivable Supplies Equipment Accumulated Depreciation Accounts Payable Accrued Expense Notes Payable Common Stock Retained Earnings Dividends Service Fees Salaries and Employee expense Depreciation Expense Other Selling and Administrative Expense Totals Debits Credits 0 Out of balance amount 0 Instructions Student name typed here. Enter formulas in the yellow cells to pull data from the trial balance and to calculate totals. 'The formulas have already been entered for you into the cells with the dotted-line borders. INCOME STATEMENT Smart Fit Company Service Fees Expenses Salaries and Employee expense Depreciation Expense Other Selling and Administrative Expense Net Income Statement of changes in owner's equity Smart Fit Company Common Stock Balances at beginning of month Issuance (repurchase) of common stock Net Income Dividends Balances at end of month 0 0 Retained Earnings 0 Note to students: Pay attention to how you format your dividends formula. If you use a minus sign in front of the cell reference, then you can use a sum function in D25. But if you use a positive algebraic sign on the dividends cell reference, your formula in cell D25 will have to be +D22+D23D24. - BALANCE SHEET Smart Fit Company Assets Cash Accounts Receivable Supplies Equipment Less Accumulated Depreciation Total Assets Liabilities Accounts Payable Accrued Expense Notes Payable Owners' Equity Common Stock Retained Earnings Total Liabilities and Shareholders' Equity Out of balance amount - Chart of accounts Account number 101 120 160 210 215 310 320 350 400 410 420 510 610 660 690 Account name Cash Accounts Receivable Supplies Equipment Accumulated Depreciation Accounts Payable Accrued Expense Notes Payable Common Stock Retained Earnings Dividends Service Fees Salaries and Employee expense Depreciation Expense Other Selling and Administrative Expense Customer master file Customer account number Customer name 444 Margaret Rose University 555 Matt Leitao 666 Steve Deary 777 Melinda Flinchum ACCOUNTING INFORMATION SYSTEMS CASE USING EXCEL PROF. MANSOUR ACCT-451 Page 1 ACCOUNTING INFORMATION SYSTEMS CASE USING EXCEL 1.0 OVERVIEW In thiscase assignment, you will create a basic accounting system using Microsoft Excel. The Excel AIS system includes the following: Credit sales journal Cash receipts journal Cash disbursements journal General journal Subsidiary accounts receivable ledger Accounts receivable reconciliation to general ledger General ledger Trial balance Financial statements This case is a simplistic mimic of a real-life accounting system, meant to provide students with the underlying nature of an AIS system. It would be rare (and unusual) for any actual company to use Excel for their books of record. Instructions in this case will guide you through these steps: 1. Record a set of sample transactions in the journals. 2. Post from the credit sales journal to the subsidiary accounts receivable ledger. 3. Enter formulas in the journal worksheets so that transactions will be sorted and totaled for automatic posting (by cell-reference formulas) into the appropriate subsidiary ledger and into the appropriate general ledger account. 4. Enter formulas into the general ledger so that it properly pullsdata from the books of original entry (the journals). 5. Enter formulas into the subsidiary ledger reconciliation worksheet in order to reconcile the subsidiary ledger to the general ledger control account. 6. Enter formulas in the trial balance worksheet and financial statement worksheet so that the accounting information system pulls data properly through the system and into the financial statements. The following typographical conventions are used: Sample data (dummy numbers for demonstration purposes) are entered into several of the graphic displays (e.g., Figure 5) using a combination of numbers and ##. When you see such figures, e.g., $6,###, it is intended to represent an example and not an actual number intended to be used in your solution. SMALL CAPITAL letters are used for keyboard or mouse keys such as CTRL, ENTER, CLICK, and RIGHT CLICK. Bold type is used for words on the screen that you are to do something with, like CLICK the Office Button and Save. Bold underlined typeis for text that you are to enter through the keyboard. Page 2 If underlined type is enclosed in square brackets, then you should enter a name that is unique to the particular student or student project, i.e., if the instructions state \"enter [your name].accdb, then the student should use their own last name and first initial in place of [your name]. When directed to perform a sequence of commands, the arrow ( ) key will be used. For example, assume that you are instructed to take the following actions: CLICK on the Office Button, select Word Options, then Save options, then check the Save auto recover information every [x] minutes checkbox, and after selecting that checkbox, type in the digit 10 in the space provided. This sequence of commands will be abbreviated as follows: Office Button Word Options Save Save auto recover information every [x] minutes 10. 2.0 ACCOMPANYING EXCEL SPREADSHEET TEMPLATE 2.1 SPREADSHEET WORKBOOK FILE This case comes with a partially completed Excel solution template (separate file). Use that template so that you do not spend unnecessary time formatting and typing. The template is embedded below and also posted as a separate file at our course's web site. DOUBLE CLICK the file Excel solution template file below. If the embedded object does not open with a double click, highlight the icon, RIGHT CLICK Worksheet Object Open. After opening, save the excel spreadsheet to your personal storage drive. Excel solution template for students: AIS using Excel. Template for students. by Donelan.xlsx The file will open into the first worksheet tab in the Excel workbook, the credit sales journal, which is depicted in Figure 1. Each worksheet tab within the workbook represents a separate component of the accounting information system, e.g., journal, ledger, reconciliation, trial balance, or financial statements. Page 3 Figure 1. Credit sales journal Each worksheet tab represents a separate component of the AIS system, e.g., journal, ledger, reconciliation, or financial statements. Figure 2. Worksheet color coding 2.2 WORKSHEET COLOR CODING Within the excel workbook, the following worksheet color coding is used for the worksheet tabs (see Figure 2): Light blue: journals Green: accounts receivable subsidiary ledger, accounts receivable trial balance, and accounts receivable reconciliation to the general ledger Black: general ledger Yellow: trial balance and financial statements 3.0 COMPANY BACKGROUND INFORMATION You will be creating the accounting information system for Smart Fit Company, a service company that provides sports training for athletes. The company came into existence on May 1 of the current year when Ms. Jenna contributed cash into the company in exchange for 100% of the common stock of the company. The accounting system will use thechart of accountsshown in Figure 3 and the customer master file data shown in Figure 4. Page 4 Smart Fit has a general journal and three special journals: credit sales journal, cash receipts journal, and cash disbursements journal. Figure 3. Chart of accounts Account number 101 120 160 210 215 310 320 350 400 410 420 510 610 660 690 Account name Cash Accounts Receivable Supplies Equipment Accumulated Depreciation Accounts Payable Accrued Expense Notes Payable Common Stock Retained Earnings Dividends Service Fees Salaries and Employee expense Depreciation Expense Other Selling and Administrative Expense Figure 4. Customer master file data base table Customer account number Customer name 444 MargaretRose University 555 Matt Leitao 666 Steve Deary 777 Melinda Flinchum 4.0 TRANSACTION ENTRY You will begin this case project by entering transaction data into the appropriate journal (worksheet) in the spreadsheet workbook. Use the transactions in Appendix 1 (page 21), and enter each transaction into one, and only one, of the four journals based on the rules described below: 1. If the transaction involves credit sales, enter it into the credit sales journal. 2. If the transaction involves receipt of cash, enter it into the cash receipts journal. 3. If the transaction involves payment of cash, enter it into the cash disbursements journal. 4. If the transaction does not fit into any of the three categories above, then enter it into the general journal. Read the remainder of this Transaction Entry section of the case prior to beginning the entry of transactions. 4.1 CREDIT SALES Figure 5 providesan example of the credit sales journal with sample data entered. (Note that this is sample data, and it is not related to the transactions in Appendix 1).The credit sales journal Page 5 contains all fields (columns) needed to account for credit sales: date, invoice number, customer account number, and amount. You should enter all credit sales transactions from Appendix 1 into the grey-shaded cells of this journal. Later in the case, you will create formulas for the yellow-highlighted cells, therefore do not enter anything in those yellow-shaded cells at this time. Figure 5. credit sales journal with sample data 4.2 CASH RECEIPTS AND CASH DISBURSEMENTS JOURNALS Figure 6 provides an example of the cash receipts journal with sample data entered. (Note that this is sample data, and it is not related to the transactions in Appendix 1). The cash receipts journal contains all fields (columns) needed to account for cash receipts: date, receipt number, amount, general ledger account to be credited, and customer account number. The customer account number field is used only when the cash receipts is from a customer who is making payment on account. The cash disbursements journal is organized in a similar manner. You should enter all cash receipts and cash disbursements transaction data from Appendix 1 into the gray-shaded cells in these two journals. Later in the case, you will create formulas for the yellow-highlighted cells, so do not enter anything in those yellow-shaded cells at this time. Page 6 Figure 6. Cash receipts journal with sample data 4.3 GENERAL JOURNAL The fields in the general journal allow you to post activities that do not properly fit into any of the other journals. Typically, the general journal is used for infrequent, non-cash entries and for adjusting journal entries, e.g. depreciation expense. For this case, there are two types of adjusting journal entries that you should make: Depreciation expense for one month Interest accrual for one month In addition to adjusting journal entries, you should use the general journal for any other transactions that do not properly fit into the other journals. For example, there should be no credit sales entries in the general journal because those transactions properly fit into the credit sales journal. In addition, there should be no cash entries in the general journal because all cash transactions should be entered into the cash receipts or cash disbursements journal. The date and reference number should refer to the supporting documentation for the entry. For example, depreciation entries are typically the last day of each month, and the reference number usually refers to the fixed asset ledger that is the source of the entry. For this case, you do not need to make an entry in the \"Reference number\" in column B. However, you should complete columns A, C, D, E, and F by entering the transaction data. For example, a depreciation expense entry would be entered as shown in Figure 7 (sample data is not related to this problem). Page 7 Figure 7. General journal with sample data Smart Fit Company General Journal Debit reference Account Date number number Amount 12/31/20xx FA201 660 987.00 Credit Account number 215 Amount 987.00 4.4 ACCOUNTS RECEIVABLE SUBSIDIARY LEDGER Enter transaction data in the gray cells, as appropriate, using the credit sales journal and the cash receipts journal as the source of your postings. Figure 8 shows accounts receivable subsidiary ledger posting using the sample data from Figures 5 and 6. (Note that this is sample data, and your solution will have different account numbers, names, and amounts.) Figure 8. Subsidiary accounts receivable ledger with sample data 4.5 SUMMARY OF TRANSACTION ENTRY Go to Appendix 1 and enter all transactions using the guidance in the previous sections of the case. When you are finished entering all the transactions, each transaction should be posted to one, and only one, journal. In turn, the credit sales journal and the cash receipts journal are used as the source for postings to the accounts receivable subsidiary ledger. When you have completed the transaction posting, proceed to the following parts of the case where you will create the formulas to total the journals and pull the data from the journals into the ledgers, accounts receivable reconciliation, trial balance, and financial statements. Page 8 5.0 FORMULA ENTRY In this section of the case, you will complete these steps: Enter formulas in the journal worksheets so that transactions will be automatically sorted and totaled for posting to the appropriate subsidiary ledger and general ledger accounts. Enter formulas into the general ledger so that it properly pulls data from the books of original entry (the journals). Enter formulas into the subsidiary ledger reconciliation worksheet in order to reconcile the subsidiary ledger to the general ledger control account. Enter formulas in the trial balance worksheet and financial statement worksheet so that the accounting information system pulls data properly through the system and into the financial statements. 5.1 CREDIT SALES JOURNAL After posting the transaction data in Appendix 1, the credit sales journal should look similar to what is depicted in Figure 5 (except the data for your assignment will be different than what is shown in Figure 5). The only thing missing at this point is a summation formula in D20. Place that sum function there now in order to add the range D9:D18. Later in the case, you will post to the subsidiary ledgerand the general ledger from this journal. 5.2 CASH RECEIPTS JOURNAL During the transaction entry section of this case you should have posted all the cash receipts transactions from Appendix 1 into this journal. Accordingly, there should now be several entries in the gray shaded area of the journal similar to Figure 6 (data shown there is test data and is not related to the facts of this problem). At this point, you will create the formulas in the range G12:J18. Those formulas will categorize each cash receipt transaction amount into one of the fourgeneral ledger accounts: accounts receivable, notes payable, common stock, or service fees. Begin by creating the account-column formulas for cell G12as shown here: =IF($D12=G$11,$C12,"0") The account-column formula above compares the account number (D12) to be credited in that row with the column heading for that column (G11). If they are the same number, then the column C amount is entered into that current cell location (G12). If not, then G12 is zero. The formula in G12 uses absolute references (denoted by leading \"$\")and relative references (no leading \"$\"). The specific relative and absolute references in that formula allow you to use thefill handle at the bottom right corner of cell G12tofill in all the remaining cells in the range G12:J18. Figure 9 shows thefill handle. Page 9 Figure 9. Excel fill handle Fill Handle Click on cell G12 to make it the active cell, then use your mouse pointer to point to the fill handle, click to grab the fill handle and drag to the right and down to fillall the remaining cells in G12:J18. As soon as you have completed filling in the formulas, each row should now have one, and only one, entry in one of the yellow shaded columns, G, H, I, or J. For example, based on the sample data shown in Figure 6, there should now be a $3## posting in cell I12 (See Figure 10). Finally, column totals should be made in row 20 of columns C, G, H, I and J. Use the Excel sum function for this ( ). After completing the formulas, your sheet should look similar to Figure 10 (with different data, of course). Figure 10. Cash receipts journal with sample data Use Formulas ribbon Formula Auditing Show Formulas to view the formulas, which should appear similar to what is depicted in Figure 11. Before proceeding, test your sheet by changing one or more of the account numbers in column D to be sure that the posting changes to the appropriate column. For example, using the test data shown above, if you changed cell D12 to \"120,\" the $3## posting should automatically Page 10 move from column I to column G, and the range totals in I20 and G20 should change accordingly. If you do not get the result depicted above, proof your formulas and correct your errors before proceeding. Figure 11. Formula auditing view of cash receipts journal 5.3 CASH DISBURSEMENTS JOURNAL The cash disbursements journal, shown in Figure 12, includes all fields (columns) needed to account for cash disbursements, e.g., date, check number, etc. During the transaction entry section of this case you should have posted all the cash disbursements transactions from Appendix 1 into this journal. Accordingly, there should now be one or more entries in the gray shaded area of the journal. At this point, you will create the formulas in the F11:M18. Those formulas will categorize each cash disbursement transaction amount into the one of the general ledger accounts in columns F through M. Begin by creating the formulas for cell F11, using the formula in the cash receipts journal as a guide. Use the fill handle to complete the range F11:M18, and then put summation functions in each of the yellow-highlighted Total cells in row 20. Page 11 Figure 12. Cash disbursements journal 5.4 GENERAL JOURNAL The general journal worksheet already contains formulas in columns range F25:G40. Those formulas will pull the amounts into the cell in the same manner as the cash receipts and cash disbursements journal formulas. You should have posted several transactions from the information in Appendix 1 into the gray-shaded cells of this journal. Figure 13 depicts the appearance of this journal with sample data. You are not required to create any formulas for this journal. However, you may find the formula SUMIF used in range F25.G39 very useful. For example, cell F29 formula follows: =SUMIF($D$10:$D$13,B29,$E$10:$E$18). This formula sums all the numbers in range E20.E18 if the number in the corresponding range D10.D18 equals cell D29. The formula summarizes all data in the gray shaded area, combining all the entries into a single summary entry for posting to the general journal. Page 12 Figure 13. General journal with sample data 5.5 ACCOUNTS RECEIVABLE SUBSIDIARY LEDGER At this point, you should have posted all accounts receivable activities (from the credit sales journal and the cash receipts journal) into this ledger. To finish this journal, begin by entering Page 13 this formula into cell I12: =+I11+F12-G12. Then use the fill handle to copy the formula to I13:I19. Repeat this process for all remaining subsidiary accounts. Review your work to be sure that the postings in columns F and G (entered earlier in the case) are being properly reflected in the balance column. Figure 14 depicts the appearance of this ledger with sample data (not the same data as in your case). Figure 14. Accounts receivable subsidiary ledger with sample data 5.6 ACCOUNTS RECEIVABLE TRIAL BALANCE Enter a cell reference formula into cell E7 in order to pull the MargaretRose University account balance from the accounts receivable subsidiary ledger. Use this sequence of keystrokes and clicks: press the equal sign, CLICKon the Acc. Rec. Subsidiary Ledger worksheet tab, CLICK on I19, then ENTER. Your cell should have the formula shown in Figure 15. Complete cells E8:E10 in a similar manner, and then enter a sum function in E14. Figure 15. Accounts receivable trial balance formula view Accounts receivable trial balance Balance MargaretRose University =+'Acc. rec. subsidiary ledger'!I19 Page 14 5.7 GENERAL LEDGER For each account, there are rows that you will use to pull journal totals. Cell B15 has been completed for you. You will enter remaining formulas in the appropriate rows for each account. For example, you should enter a formula into cells C16 to pull the cash disbursements journal total into the general ledger cash account. But you will not need to enter a formula into cell B17 or C17 because there will be no cash entries in the general journal. Create formulas for all other accounts as appropriate so that all of the Total rows from each journal is properly posted into the general ledger. Then create formulas to create the ending balances in each account. To assist you in this process, the cells that do NOT need formulas are unformatted, blank cells with no shading. Cells that MIGHT need formulas are formatted in yellow highlight. Some cells may have yellow highlight, but do not need formulas in this particular case. Figure 16 shows the appearance of the general ledger in normal view, and Figure 17 shows the formula view. Page 15 Figure 16. General ledger with sample data Formulas are already completed for you in the cells with the dotted-line borders. Figure 17. General ledger formula view To see the underlying formulas, you can toggle to formula view by pressing CTRL + ` (grave accent). To switch back, press CTRL + ` again. Page 16 5.8 ACCOUNTS RECEIVABLE RECONCILIATION The accounts receivable reconciliation sheet is shown in Figure 18. Enter a formula in B5 to pull the accounts receivable total from the accounts receivable trial balance worksheet. Then enter a formula in B6 that pulls the accounts receivable ending balance from the general ledger. If there are no posting or logic errors in your work, then the unreconciled difference should be zero. Figure 18. Accounts receivable reconciliation 5.9 TRIAL BALANCE The trial balance (TB) worksheet is shown in Figure 19. Enter the cell formulas for each account, add the summation functions in the Totals row, and check to be sure that debits equal credits. If they are not equal, then you have an error, either in this trial balance or in some earlier portion of this case. If you are out of balance, you will need to proof your prior work. I suggest you begin with the journals, making sure that each \"total\" row is in balance. Another approach is to create T accounts using pen and paper, post the entries to the T accounts, and compute the balances using a calculator. Then compare the paper version to the Excel version to see where there are differences. 5.10 FINANCIAL STATEMENTS The Financial Statements worksheet includes the Income Statement, the Statement of Changes in Owners' Equity, and the Balance Sheet. The Cash Flow Statement is omitted. Complete the statements in this chronological order 1. Income statement 2. Statement of Changes in Owners' Equity 3. Balance sheet. Page 17 Figure 19. Trial balance Income statement Enter formulas in the yellow highlighted cells to pull data from the revenue and expense accounts in the trial balance. Format the expense cells so that they appear as negative numbers, then add a sum function in cell D16to complete the statement. Statement of changes in owner's equity Beginning balances of common stock and retained earnings arealready formatted to pull from the beginning balance in the general ledger. The net income cell in the retained earnings column should pull from the income statement directly above. Dividends should pull from the trial balance worksheet. If you use a negative sign in front of the dividend formula in F27, then you can use a sum function in cell F28. But if you use a positive algebraic sign on the dividends cell reference, your formula in cell F28 will have to be +F24+F26-F27. The \"Issuance (repurchase) of common stock\" cell D25 has been formatted to pull the summation of all common stock activity from the general ledger account. If you have your common stock account properly formatted in the general ledger, then D25 should now include the summation of all common stock activity from Appendix 1. Balance sheet First, enter formulas to pull account balances from the individual asset and liability accounts in the trial balance. Next, enter formulas to pull common stock and retained earnings balances from the statement of changes in owner's equity directly above on this same worksheet. Finally, add the summation functions fortotal assets and total liabilities and equity. Page 18 Visually review the totals to be sure you are in balance. 6.0 TEST YOUR ACCOUNTING SYSTEM Run several tests of your system. Here are a few recommendations. Add a dummy sales transaction. For example, add one additional $10,000 credit sales transaction to the credit sales journal. Then check your general ledger, trial balance, income statement, and balance sheet. Make sure that the $10,000 dummy transaction resulted in an increase in assets and income. Then delete the dummy transaction. Add a dummy expense transaction. For example, add a $1,000 cash expense transaction to the cash disbursements journal. Make sure that the dummy entry impacted the general ledger, trial balance, income statement, and balance sheet as expected. Then delete the dummy transaction. 7.0 SAVE YOUR WORK When saving your file, please pay attention to my instructions below about how to name files. 7.1 FILE NAMING (EXTERNAL HEADERS) Name files with words, phrases, numbers, and/or symbols that progress from general to specific. For example, the best way to code files using dates follows: yyyy mm dd because you are moving from the general (yyyy = year) to the specific (dd = day of month) as you move from left to right. For example, it is better to name group of employee files using LastnameFirstname than using FirstnameLastname. In this way, all sorting becomes easier, because computer logic algorithms sort from left to right. Moreover, when sorting on the last name, there will be fewer \"Smith\" entries than \"Joseph\" entries, so you will have less trouble finding the file you are looking for. Here is another example: when I save student examinations, I use this system: Course #, exam #, exam content, exam date. Course number is the most general component, and exam date is the most specific component. If you follow a systematic file naming system such as I am suggesting, you will find your personal and professional life much easier to keep organized. 7.2 FILE NAMING (EXTERNAL HEADERS) FOR EXTERNAL DISTRIBUTION If you are sending files to other professionals, consider how that professional will identify and sort the file. For example, imagine the difficulty and trouble a tax professional would have if clients sent their tax information into her using a file name like \"my tax file 2012.\" That tax professional would have hundreds of files with meaningless file names such as \"my taxes,\" and the professional would have to rename all of the files so that she could identify a file with a particular client. So if you are sending your tax file to your CPA, rename file using an external header (file name) such as \"LastName, FirstName, tax year\" e.g., \"Smith Joseph Tax Information 2021.\" as the external file name. And always include internal headers in the file, in other words, put your name inside the Excel sheet or the Word file so that the document can be identified both prior to opening the file and after the file is opened and the professional is reviewing the file. Your internal header location for this assignment is on the Credit Sales worksheet, where you will enter your name. Page 19 7.3 INTERNAL HEADERS (TITLES ON WORD AND SPREADSHEET FILES) There is nothing more frustrating than having several files open at the same time and not being able to tell which file is which. To avoid this frustration, use an internal header, i.e., a title on the page you are looking at. For example, this word document has an external header, the file name, and it also has an internal header, found at the top of page 1 in large bold type. 8.0 CASE REQUIREMENTS 1. Record all transactions and complete formulas in all worksheets as instructed in the prior pages. 2. Submit the completed Excel file to thedrop box by the date specified in the syllabus. Page 20 APPENDIX1 I suggest that you enter all the transactions as described in Section 4 prior to entering the formulas as described in Section 5. That way, when you enter the formulas, you will be able to see immediately if those formulas are working properly. However, it does not really matter, and the problem will work correctly either way. Date Activity May 1 The Smart Fit Company came into existence on this day when Ms. Jenna paid $4,000 cash, out of her personal funds, to the Smart Fit Company. In return, Ms. Smart received 90 shares of no-par common stock in Smart Fit Co. Smart Fit issued Ms. Smith cash receipt ticket 001. May 2 Ms. Jenna signed over to the company the title to her personal office equipment, which has a fair market value of $1,200. Ms. Jenna received 12 shares of no-par common stock in Smart Fit in return. Estimated useful life of the equipment is four years; no residual value.No other common stock was issued or authorized. May 3 The company borrowed $6,000 on a five-year note, with principal and 6% interest due at maturity. Smart Fit used cash receipts ticket 002 for this cash receipt. May 4 The company purchased training equipment for $8,000 cash (check #101). Estimated life of 10 years; no residual value May 6 The company performed services and billed the client, MargaretRose University, $500 on invoice #1001. See Figure 4 for customer account numbers. May 15 The company performed services and billed the client, Matt Leitao, $700 on invoice #1002. May 17 The company performed services and billed the client, Melinda Flinchum, $800 on invoice #1003. May 18 The company performed services and billed the client, Steve Deary, $450 on invoice #1004. May 19 The company performed services and billed the client, Margaret Rose University, $925 on invoice #1005. May 20 Received $600 cash from client Matt Leitao in partial payment on her account balance outstanding. Smart Fit issued receipt ticket 003. May 21 Paid rent and other general expenses in the amount of $350 cash with check #102. May 22 Received $400 from Margaret Rose University. This is a partial payment on invoice #1001. Smart Fit issued cash receipt ticket 004 for this transaction. May 31 The company paid monthly salaries to employees in the amount of $1,200 cash (check #103). Salary covers the pay period May 1 through May 31. May 31 The company paid dividends of $1,000 using check #104. May 31 Prepare adjusting journal entries Page 21 Page 22