Anonymous
timer Asked: May 4th, 2020

Unformatted Attachment Preview

A Byte of Accounting, Inc. General Journal 01 01 Jun 01 Jun 01 1110 Cash 3100 Capital Stock 02 02 Jun 01 Jun 01 1211 Office Equip. Hudson equipment Invoice BC3887 2101 Accounts Payable Hudson equipment Invoice BC3887 4,000.00 03 03 Jun 01 Jun 01 1311 Computer Equip. 1110 Cash 7,000.00 04 04 Jun 02 Jun 02 5040 Repairs & Maint. Expense Avery Repairs, Invoice 25478 2101 Accounts Payable Avery Repairs, Invoice 25478 725.00 05 05 Jun 04 Jun 04 1211 Office Equip. 1110 Cash 125.00 06 06 Jun 08 Jun 08 5030 Advertising ExpenseZac Advertising,23547 2101 Accounts Payable Zac Advertising,23547 07 07 Jun 08 Jun 08 2101 Accounts Payable Pd A/p ck6003 1110 Cash Pd A/p ck6003 08 08 Jun 14 Jun 14 1110 Cash Services performed by Lucus Pictures 4100 Computer & Consulting Services Revenue performed by Lucus Pictures Hailey Computers 87245, ck6001 Hailey Computers 87245, ck6001 Scanner, Jake Supplies, 54-541 ck6002 Scanner, Jake Supplies, 54-541 ck6002 28,000.00 28,000.00 4,000.00 7,000.00 725.00 125.00 3,380.00 3,380.00 640.00 640.00 11,000.00 11,000.00 To ta lB al an ce Description Investment from Mark Friedman Investment from Mark Friedman Cr ed it Name De bi t Ac co un t Tr an sa ct io n Da te Note: You can only enter data into the yellow filled cells. 28000 (28000) 0 0 0 0 0 0 4000 (4000) 0 0 0 0 0 0 7000 (7000) 0 0 0 0 0 0 725 (725) 0 0 0 0 0 0 125 (125) 0 0 0 0 0 0 3380 (3380) 0 0 0 0 0 0 640 (640) 0 0 0 0 0 0 11000 (11000) 0 0 0 0 0 0 09 0 0 0 0 0 0 0 0 10 0 0 0 0 0 0 0 0 11 0 0 0 0 0 0 0 0 12 0 0 0 0 0 0 0 0 13 0 0 0 0 0 0 0 0 14 0 0 0 0 0 0 0 0 15 0 0 0 0 0 0 0 0 16 0 0 0 0 0 0 0 0 17 0 0 0 0 0 0 0 0 18 0 0 0 0 0 0 0 0 19 0 0 0 0 0 0 0 0 20 0 0 0 0 0 0 0 0 21 0 0 0 0 0 0 0 0 22 0 0 0 0 0 0 0 0 23 0 0 0 0 0 0 0 0 24 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0 26 0 0 0 0 0 0 0 0 27 0 0 0 0 0 0 0 0 28 0 0 0 0 0 0 0 0 29 0 0 0 0 0 0 0 0 30 0 0 0 0 0 0 0 0 31 0 0 0 0 0 0 0 0 32 0 0 0 0 0 0 0 0 33 0 0 0 0 0 0 0 0 34 0 0 0 0 0 0 0 0 35 0 0 0 0 0 0 0 0 36 0 0 0 0 0 0 0 0 37 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 38 0 0 0 0 0 0 0 0 39 0 0 0 0 0 0 0 0 A Byte of Accounting, Inc. General Journal M/DD Trans Account Detail Account Name 6/16 6/16 6/16 09 09 09 1120000.0000 4130000.0000 4110000.0000 G. Washington Invoice 1492 G. Washington Invoice 1492 G. Washington Invoice 1492 Accounts Receivable Repair Service Equipment Sales 6/16 6/16 10 10 5090000.0000 1130000.0000 Invoice 1732 Invoice 1733 Cost of Goods Sold Inventory 6/16 6/16 11 11 5380000.0000 2120000.0000 Salary Expense Salary Expense Salary Payroll Liabilities 6/16 6/16 12 12 2120000.0000 1110000.0000 To Payroll company Chk 6003 To Payroll company Chk 6003 Payroll Liabilities Cash 6/21 6/21 13 13 1110000.0000 1120000.0000 Receipt Cash Inv # 1732 Receipt Cash Inv # 1732 Cash Accounts Receivable 6/22 6/22 14 14 5030000.0000 1110000.0000 Monthly Insurance Chk 6004 Monthly Insurance Chk 6004 Insurance Expense Cash 6/23 6/23 15 15 5220000.0000 1110000.0000 Maint Chk 6005 Maint Chk 6005 Maintenance/Janitorial Cash 6/24 6/24 6/24 16 16 16 1120000.0000 4130000.0000 4110000.0000 J. Adans Invoice 1493 J. Adans Invoice 1493 J. Adans Invoice 1493 Accounts Receivable Repair Service Equipment Sales 6/16 6/16 17 17 5090000.0000 1130000.0000 Invoice 1733 Invoice 1733 Cost of Goods Sold Inventory 6/25 6/25 18 18 1130000.0000 2101000.0000 Purchased Inventory PO # 21021 Purchased Inventory PO # 21021 Inventory Accounts Payable 6/26 6/26 19 19 5080000.0000 1110000.0000 Supplies Chk 5056 Supplies Chk 5056 Supplies Cash Debit Right Side 633.00 348.00 285.00 161.03 161.03 1,430.01 1,430.01 1,430.01 1,430.01 633.00 633.00 326.00 326.00 190.00 190.00 350.00 86.00 264.00 147.84 147.84 3,350.00 3,350.00 124.00 124.00 A Byte of Accounting, Inc. Worksheet #NAME? Account Number Name 1110 Cash 1120 Accounts Receivable 1130 Prepaid Insurance 1140 Prepaid Rent 1150 Office Supplies 1211 Office Equip. 1212 Accum. Depr.-Office Equip. 1311 Computer Equip. 1312 Accum. Depr.-Computer Equip. 1411 Building Cost 1412 Accum. Depr.-Building 1510 Land 2101 Accounts Payable 2102 Advanced Payments 2103 Interest Payable 2105 Salaries Payable 2106 Income Taxes Payable 2201 Mortgage Payable 2202 Notes Payable 3100 Capital Stock 3200 Retained Earnings 3300 Dividends 4100 5010 5020 5030 5040 5050 5080 5090 5100 5110 5120 Computer & Consulting Revenue Rent Expense Salary Expense Advertising Expense Repairs & Maint. Expense Oil & Gas Expense Supplies Expense Interest Expense Insurance Expense Depreciation Expense Income Tax Expense TOTAL NET INCOME ` Unadjusted Trial Balance Debit Credit Adjusting Entries Debit Credit Adjusted Trial Balance Debit Credit Income Statement Debit Credit Balance Sheet Debit Credit A Byte of Accounting, Inc. Income Statement #NAME? 4100 5010 5020 5030 5040 5050 5080 5090 5100 5110 Revenues Computer & Consulting Revenue Expenses Rent Expense Salary Expense Advertising Expense Repairs & Maint. Expense Oil & Gas Expense Supplies Expense Interest Expense Insurance Expense Depreciation Expense Total Net Income Before Tax 5120 Income Tax Expense (Round to two decimal places) Net Income After Tax 0 0 0 Worksheet!I48 Worksheet!L48 Worksheet!I50 You must finish the worksheet before you can complete the Income Statement, see FAQ 05. A Byte of Accounting, Inc. Statement of Changes in Retained Earnings #NAME? Total Balance, Beginning of Period Net Income Dividends Balance, End of Period 0 You must finish the worksheet before you can complete the Changes in Retained Earnings, see FAQ 05. A Byte of Accounting, Inc. Balance Sheet #NAME? d Assets 1110 1120 1130 1140 1150 1211 1212 1311 1312 1411 1412 1510 Current Assets Cash Accounts Receivable Prepaid Insurance Prepaid Rent Office Supplies Total $ 2.56 Round to two decimal places Long-Term Assets Office Equipment Accum. Depr.-Office Equip. Net Office Equipment Computer Equipment Accum. Depr.-Computer Equip. Net Computer Equipment Building Cost Accum. Depr.-Building Net Building Cost Land Total Total Assets Liabilities 2101 2102 2103 2105 2106 2201 2202 Current Liabilities Accounts Payable Advanced Payment Interest Payable Salaries Payable Income Taxes Payable Total Long-Term Liabilities Mortgage Payable Notes Payable Total Total Liabilities Stockholder's Equity 3100 3200 Capital Stock Retained Earnings Total Total Liabilities and Stockholder's Equity 0 You must finish the worksheet before you can complete the Balance Sheet, see FAQ 05. ` ABC Instructions for But I Need a Trial Balance Part 1: Sign Up A. A. Go to htps://www.cybertext.com B. Select Account Setup and find your instructors course and section. Part 2: The Case 1. Go to htps://www.cybertext.com 2. Select “LOGIN HERE” and enter your email and password. You may have to change the password, use your Cybertext password if you already have one. 3. Select “Data Prep I Trial Balance” 4. Select GET EXCEL FILE 1 ABC Instructions for But I Need a Trial Balance 5. "Right-click" on the file name and choose "Save Target As" (or "Save link as") to download your Excel file. 6. Select “Save” 2 ABC Instructions for But I Need a Trial Balance 7. Read the introduction which includes your 10 questions. 9. Review the data on the sheet named “JAN” and note the line that contains the headings. 10. Close the Excel File 11. Open your ETL Software; Alteryx Designer, Power BI or Tableau Prep 12. Create a flow that transforms your data into a General Ledger and a General Journal 13. Save the General Journal and General Ledger in the same Excel file that you downloaded 14. Save the workflow as a file that you will upload to CyberText LL####.yxzp (Alteryx) LL####.pbix (Power BI) Or LL####.tfix(Tableau Prep) 3 ABC Instructions for But I Need a Trial Balance 15. Answer the 10 questions in your Excel file that are found on the “Introduction” sheet 16. Return to CyberText and select “ANSWER ANALYTICS QUESTIONS” 4 ABC Instructions for But I Need a Trial Balance 19. Enter your answers NOTE: your questions will be different 20. Select “SUBMIT ANSWERS” 5 ABC Instructions for But I Need a Trial Balance 21. Select “UPLOAD EXCEL FILE” 22. Chose Excel File to upload 23. Choose ETL file to upload Must be in the format LL####.yxzp LL####.pbix Or LL####.tfix 24. Select “UPLOAD NOW” 6 ABC Instructions for But I Need a Trial Balance 25. Select “OK” 26. Select “LOGOUT” 7 Alteryx Part 1 Mark Friedman University of Miami Using Alteryx Alteryx is used by several Big Four accounting firms to automate the ETL process. In this exercise, you will: Extract the General Journal file Transform the data into a General Ledger and an Unadjusted Trial Balance Load the transformed data into Excel The journal entries for A Byte of Accounting are stored in an Excel sheet titled General Journal. Notice that the column headings are in the fourth row, and some rows do not have account numbers. Begin with the General Journal End with the General Ledger and the Unadjusted Trial Balance 1 Alteryx Part 1 Mark Friedman University of Miami Start Alteryx Note: You cannot extract data from an open Excel file so make sure that the Excel file is closed before starting to input the data. There are four major parts of the Alteryx window. The first is the Canvas, and that’s where we’re going to create the workflow. Second, the Tools Palette is where all the tools are stored. Third, the Configuration area is where the tool settings are modified. Lastly, the Results window shows us the result after we select “Run” to apply the changes. Tool 1: Input Data Tool – Input Excel file Drag the “Input Data Tool” from the Tools Palette to the Canvas. Run Tools Palette Configuration CANVAS Results Window 2 Alteryx Part 1 Mark Friedman University of Miami In the Configuration area, select the “Connect a File or Database” drop-down arrow. Select “File”. Browse for the desired file and select the version with the .xlsx file extension. Select “Open”. 3 Alteryx Part 1 Mark Friedman University of Miami Select the drop-down arrow to the right of “Balance Sheet”. Select “General Journal”. Select “OK”. 4 Alteryx Part 1 Mark Friedman University of Miami Remember that the field names are in the fourth row. In the Configuration area, change the number in “Start Data Import on Line” to “4”. Select “Run”. The Results window will display the field headings and the data. 5 Alteryx Part 1 Mark Friedman University of Miami The workflow must be saved. Select “File”. Select “Save”. Browse to the required directory. Type in the name of the file that will be uploaded. Select “Save”. 6 Alteryx Part 1 Mark Friedman University of Miami Export the workflow and the related input files so that the project can be completed on another computer or uploaded to the web. Select “Options”. Select “Export Workflow”. Verify the “Workflow”. Verify the “Package Location”. Select “Save”. 7 Alteryx Part 1 Mark Friedman University of Miami Tool 2: Filter Tool – Exclude all rows that do not contain data There are several rows that do not contain an account number. We can modify the working dataset so that it includes only those rows that contain account numbers greater than one. Note that we are not deleting anything from the source data file. The working dataset will be split into two subsets. The first subset, “True”, will include all rows with account numbers greater than one. The second subset, “False”, will contain all rows with account numbers less than one. Drag the “Filter Tool” from the Tools Palette to the right of the “Input Data Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. With the “Filter Tool” selected, the “Basic” filter in the Configuration area can be modified to filter the field “Account” for those accounts greater than “1’. To apply the latest change, select “Run”. 8 Alteryx Part 1 Mark Friedman University of Miami We can view three different datasets in the Results window. The records that meet the filtered conditions, “Account > 1”, are displayed as T (True). The records that do not meet the filtered conditions are displayed as F (False). The data being filtered can also be displayed. 9 Alteryx Part 1 Mark Friedman University of Miami Tool 3: Select Tool – Remove fields that contain no data, from F8 to F15 Drag the “Select Tool” from the Tools Palette to the True output of the “Filter Tool” on the Canvas. The two will be connected automatically. Remove the extra fields by unchecking them. Select “Run”. 10 Alteryx Part 1 Mark Friedman University of Miami Tool 4: Select Tool – Sort by “Account” and then “Transaction” Notice that the original data from Excel had a hidden column “Total Balance” that contained the following formula: “Debits – Credits”. The data is displayed as it was extracted from the source document. We want to have the data in “Account” order and subsequently in “Transaction” order. Drag the “Sort Tool” from the Tools Palette to the right of the “Select Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. Change the sort configuration of “Account” and “Transaction” to “Ascending”. Select “Run”. 11 Alteryx Part 1 Mark Friedman University of Miami Tool 5: Data Cleansing Tool – Replace “Null” with “0” There are cells in the “Debit” and “Credit” columns that are coded as “Null”. Drag the “Data Cleansing Tool” from the Tools Palette to the right of the “Sort Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. Change the configuration, leaving only “Debit” and “Credit” checked. Select “Replace with 0” on the “Replace Nulls” option. Select “Run”. 12 Alteryx Part 1 Mark Friedman University of Miami Tool 6: Running Total Tool – Create a “Running Total” The General Ledger has a running balance within each account. Drag the “Running Total Tool” from the Transform tab to the right of the “Data Cleaning Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. Add a “Running Total Tool” from the Transform tab. Change the configuration of “Group By” to “Account”. Select “Total Balance” on “Create Running Total”. Select “Run”. 13 Alteryx Part 1 Mark Friedman University of Miami Tool 7: Select Tool Display – “Account” and “Name” in front of “Transaction” Drag another “Select Tool” to the right of the “Running Total Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. Rearrange the fields in the Configuration area. Select the field “Account”. Click on the up arrow twice to move “Account” to the first position. Move the field “Name” to the second position. Select “Run”. 14 Alteryx Part 1 Mark Friedman University of Miami Remove the field “Total Balance” from the output by unchecking it. Select “Run”. 15 Alteryx Part 1 Mark Friedman University of Miami Change the name of the field “Run Tot_Total Balance” to “Running Balance”. Select “Run”. 16 Alteryx Part 1 Mark Friedman University of Miami Tool 8: Select Tool Output Data Tool: We now want to export the cleaned and assembled General Ledger to Excel. Drag an “Output Data Tool” to the right of the “Select Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. In the output Configuration area, select the drop-down arrow. Select “File”. Select the required file name. Select “Save”. 17 Alteryx Part 1 Mark Friedman University of Miami Click on “Yes” to confirm “Save”. Enter the “Sheet Name”. Select “OK”. Select “Run”. 18 Alteryx Part 1 Mark Friedman University of Miami Open the file in Excel. Change the columns’ widths and formats. And, the General Ledger has been created successfully. “Save” and “Close” the Excel file. Return to Alteryx and select “Run” for the second time. 19 Alteryx Part 1 Mark Friedman University of Miami But, there is an error! The sheet already exits. In the output Configuration area, select “Output Options”. Select “Overwrite Sheet” (which drops the old sheet). Select “Run”. The “Unadjusted Trial Balance” will summarize the “Total Balance” by account number with the account name attached for clarity. 20 Alteryx Part 1 Mark Friedman University of Miami Tool 9: Select Tool Formula: We now want to combine the fields “Account” and “Name”. “Account” is a numeric field, while “Name’ is a string, and we would like to have two spaces between the fields. Drag a “Formula Tool” under the “Running Balance Tool” branching from the “Cleansing Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. In the output Configuration area, click on the drop-down arrow of “Select Column”. Select “Add Column”. Type “Account And Name”. 21 Alteryx Part 1 Mark Friedman University of Miami Type the following formula tostring([account])+" "+[Name] Select “Run”. 22 Alteryx Part 1 Mark Friedman University of Miami Tool 10: Select Tool Summary: We now want to create an “Unadjusted Trial Balance” by calculating the account balances. Drag a “Summary Tool” to the right of the “Formula tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. In the output Configuration area, select “Account And Name”. Click on the drop-down arrow on “Actions” and select “Add”. Select “Group By”. 23 Alteryx Part 1 Mark Friedman University of Miami Select “Total Balance”. Click on the drop-down arrow on “Actions” and select “Add”. Select “Sum”. Select “Run”. Select the output of the “Summary Tool”. And, the “Unadjusted Trial Balance” is created. 24 Alteryx Part 1 Mark Friedman University of Miami Tool 11: Select Tool – Rename a field Drag another “Select Tool” to the right of the “Summary Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. Rename the “Sum_Total Balance” in the Configuration area to “Balance”. Select “Run”. Select the output of the “Select Tool”. And, the Unadjusted Trial Balance is created. 25 Alteryx Part 1 Mark Friedman University of Miami Tool 12: Select Output Data Tool: We now want to export the “Unadjusted Trial Balance” to Excel. Drag an “Output Data Tool” to the right of the “Select Tool” on the Canvas. The two will be connected automatically. The outflow from the tool on the left is the inflow to the one on the right. In the output Configuration area, select the drop-down arrow. Select “File”. Select the required file name. Select “Save”. 26 Alteryx Part 1 Mark Friedman University of Miami Click on “Yes” to confirm “Save”. Enter the “Sheet Name”. Select “OK”. Select “Run”. 27 Alteryx Part 1 Mark Friedman University of Miami ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors