Category: Accounting

Excel Dynamic Arrays: Accounting: Dynamic Schedule of Accounts (Excel Magic Trick 1530)

Posted on by Annie D

Download Excel File: https://ift.tt/2zEgCVN
In this video learn how to create a dynamic Schedule of Accounts based on an Expenses Category Formula Inpout. When we change the Expenses Category, a new report appears.
In this video see these Excel Features:
SORT Array Function
UNIQUE Array Function
Data Validation List Dropdown
FILTER Array Function
SEQUENCE Array Function
COUNTIFS Function
IF Function
ROWS Function
SUMIFS Function
Conditional Formatting
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)

View on YouTube

Excel & Business Math 07: Style Formatting Format Painter, Mini Toolbar, Styles, Clear Format & More

Posted on by Annie D

Download Start Excel File: http://ift.tt/2CXejB6
Download pdf Notes: http://ift.tt/2mblH18
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn how to
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (00:55) Add Color to Sheet Tab in Excel Workbook
3. (02:00) Show Hide Gridlines
4. (02:23) Width of Screen Determines What Buttons Look Like in Ribbon Tabs
5. (03:48) Style Formatting like: Fill, Font, Border, Indent
6. (04:30) Formatting for an Accounting Net Income
7. (06:53) Center Across Selection as an alternative to Merge & Center
8. (07:19) Format Cells Dialog Box for All Formatting Options
9. (08:29) Format Painter Button to Copy and Paste Formatting Only!
10. (09:59) Right-click a cell to get the Mini Toolbar with common Formatting Options.
11. (21:07) Styles: Use when you use the same set of Formatting Elements Over and Over
12. (22:21) Clear Formatting
13. (23:12) Summary

Next Video: Excel & Business Math 08

View on YouTube

Excel & Business Math 05: Number Formatting & When You MUST Use ROUND Function

Posted on by Annie D

Download Start Excel File: http://ift.tt/2E2Fiba
Download pdf Notes: http://ift.tt/2ERWqBG
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn how about the benefits of Number Formatting, How Formulas do NOT “see” Number Formatting, and how to use the ROUND Function to avoid calculations errors.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (00:39) What does Number Formatting do? Learn about Number Formatting as Façade
3. (03:10) Formulas do not “see” Number Format. Learn about how formula results may be misleading because of Number Formatting.
4. (04:58) Learn How Number Formatting can save a lot of time with Data Entry
5. (06:57) Learn about the important General Number Formatting as an Eraser. Also learn about Currency and Accounting Number Formatting
6. (11:18) Example of Error in a Payroll example when we do NOT use the ROUND Function
7. (14:50) Round Numbers by Hand. Rule for Standard Rounding Rule.
8. (18:07) Excel’s Built-in ROUND Function. Learn how to use it and When you MUST use it.
9. (24:55) Examples from Video #3. See an example of how to apply our three-step rule for when the ROUND Function is required. This is the Insurance Discount Example.
10. (28:53) Second example of how to apply our three-step rule for when the ROUND Function is required. This is the Payroll Deduction Example.
11. (31:13) Summary

Next Video: Excel & Business Math 04

View on YouTube

Excel Basics 17: Style Formatting: By Hand, Cell, Table & Conditional Formatting

Posted on by Annie D

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2mppN9w
Download Full Written Notes: http://ift.tt/2yXWjW8
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (00:32) Example 1: Accounting Style Formatting (4 Examples)
3. (08:47) Example 2: Accounting Borders
4. (09:25) Example 3: Column widths!!
5. (10:16) Example 4: Wrap Text in Cell: Multiple Lines in a Cell (Alt + Enter)
6. (11:38) Example 5: Table Format button (Converts to Excel table)
7. (12:55) Example 6: Cell Styles
8. (14:00) Example 7: Built-in Conditional Formatting to Visualize Data
9. (17:11) Summary – Conclusion

View on YouTube

Excel Basics 12: Complete Formula Lesson of Formula Types & Formula Elements 12 Examples

Posted on by Annie D

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2iZNLH6
Download Full Written Notes: http://ift.tt/2z9Fzrs
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (00:30) Different Types of Formulas
3. (02:07) Formula Elements – what you can out into a formula.
4. (04:55) Formula 01: Goal: Add calls made a service center last week.: =SUM(C23:C29) . Number Formula.
5. (05:53) Formula 02: Goal: Calculate Monthly Insurance Expense.: =C37/12. Number Formula.
6. (06:25) Formula 03: Goal: Calculate Deduction for Each Employee.: =ROUND(C46*$F$46,2) . Number Formula.
7. (09:31) Formula 04: Goal: Calculate Cost of Goods Sold (COGS) in Accounting: =(C58-D58)*E58. Number Formula.
8. (13:17) Formula 05: Goal: Join “Text ” and an Item Number into One Cell.: =”Item # “&C70. Text Formula.
9. (17:02) Formula 06: Goal: Join First & Last Names into One Cell.: =C82&”, “&B82. Text Formula.
10. (20:43) Formula 07: Goal: Extract State Abbreviation using the RIGHT Function: =RIGHT(B96,2) . Text Formula.
11. (24:01) Formula 08: Goal: Extract Product Number using the LEFT Function: =LEFT(B108,6) . Text Formula.
12. (26:00) Formula 09: Goal: Determine If Debits = Credits: =B127=C127. Logical Formula.
13. (30:00) Formula 10: Goal: Determine If Employee Gets a Bonus: =C135 Greater Than =$F$135. Logical Formula.
14. (33:35) Formula 11: Goal: Count how many of each product we sold: =COUNTIFS($B$148:$B$153,B156). Number Formula.
15. (35:15) Formula 12: Goal: Formula to determine whether we need to reorder?: =$D$173 Greater Than B166-C166. Logical Formula.
16. (41:00) Formula 13: Goal: Calculate Net Income: =C181-SUM(C182:C185) . Number Formula.
17. (42:30) Summary – Conclusion

View on YouTube

Excel Basics 9: Number Formatting as Façade & the ROUND Function

Posted on by Annie D

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2yCGQtg
Download Full Written Notes: http://ift.tt/2gAVevo
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (02:10) What does Number Formatting do?
3. (03:20) Formulas do NOT see Number Formatting
4. (04:20) Number Formatting can save a lot of time for Data Entry
5. (07:03) Compare General, Currency and Accounting Number Formatting
6. (09:43) Percentage Number Formatting
7. (14:55) Rounding
8. (16:15) ROUND Function Basics
9. (20:34) When you MUST Round
10. (21:36) ROUND Function for Payroll
11. (24:00) ROUND Function for Invoicing
12. (26:30) ROUND Function for Income Taxxes when you need to round to the dollar
13. (27:22) Summary – Conclusion

View on YouTube

Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

Posted on by Annie D

Complete Introduction to Power BI Desktop. This video will teach you about how to use Power BI Desktop to clean, transform data and make a Dashboard to Monitor Sales. Take multiple Text files and import them into a single table which can be used for Reports and Dashboards and can be updated easily when new data arrives.
Download Files:
Zipped Folder file Text Files: http://ift.tt/2uczRVa
Excel File: http://ift.tt/2vEnQFN
Finished File: http://ift.tt/2uc5gXA
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the third video in a series of three videos.
Topics in Video:
1. (00:10) Download Files
2. (00:24) Video Introduction and review of Topics covered in video
3. (03:13) Introduction to Power BI Desktop
4. (04:15) Overview of process of using Power BI Desktop to build our specific Reports and Dashboards
5. (05:09) Open and Save Power BI File
6. (06:28) Import Excel dProduct Lookup Tables
7. (08:05) Import and Append Multiple Text Files with Sales Data
8. (15:11) Clean Data
9. (16:42) Discussion of Relationships and why they are important
10. (17:58) Create Calendar Table using DAX CALENDAR Table Function
11. (21:27) Create Calculated Columns with DAX Functions: MONTH, FORMAT and YEAR
12. (21:54) Create Relationships and discuss more about Relationships
13. (26:25) Save As to save Power BI File
14. (26:54) Create Cross Tabulated Power BI Report
15. (28:11) Show Values As % of Column Total
16. (30:35) Create Gross Profit Dash Board
17. (31:29) DAX Measure for Gross Profit %
18. (34:24) Column and Bar Chart as Slicer for Gross Profit Dashboard
19. (36:13) Dashboard Interactivity
20. (38:00) Add New Data And Watch Dashboard Update
21. (39:53) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM

View on YouTube

Excel for Accountants: Power Query & PivotTables to Import & Clean Data and Build Reports

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tB6Zp7
Zipped Folder file Text Files; http://ift.tt/2tI91nF
Finished File: http://ift.tt/2t6W9Uh
Finished Data Model File For Last Example: http://ift.tt/2tI0d0J
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the second video in a series of two videos.
This video will teach you about how to use Power Query and PivotTables to clean, transform data and make summary PivotTable Reports. Take multiple Text files and import them into a single table which can be used for PivotTable reports and can be updated easily when new data arrives.
Topics in Video:
(00:10) Introduction to video topics and Excel files and how to navigate large workbook and video
(01:19) Overview of all Power Query & PivotTable steps in the video, including looking at Text Files that we need to import.
38. (07:05) Power Query To Import and Append Multiple Text Files with Sales Data. See how to use the From Folder feature to import many Text Files into a Single Proper Data Set thatr you can then use for PivotTable Reports.
39. (14:26) Power Query To Clean Data. Clean imported data before it is loaded to an Excel Sheet or the Data Model.
40. (17:30) Power Query To Merge Lookup Tables With Sales Table. See how to use the Combine, Merge, Left Outer Merge Feature to replace the VLOOKUP Function.
41. (22:50) PivotTable Basics and Monthly Profit Change and % Change Report using Show Values As feature and the PivotTables Group by Month feature.
42. (33:43) Build Cross Tabulated Report With Slicer and learn how to use the “Show Report Filter Page” option to create many PivotTables, each on a new sheet with a single click.
43. (39:56) Gross Profit Report, including a Formula in a PivotTable (Calculated Field)
44. (44:00) Add New Data and Update Reports
45. (46:18) Excel 2013 or 2016 Data Model to Reduce File Size Dramatically
(50:24) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Vice President
Darlene Sondergaard
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM
Tags: Excel, Microsoft Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Excel For Accountants, Excel for Accounting, WAATP Excel, WAATP Excel seminar, Power Query, Get & Transform, PivotTables, Pivot Tables, Power Query in Accounting, PivotTables in Accounting, Clean Data in Excel, Import Multiple Text Files into Excel, Power Query to Import Multiple Text Files, Transform Data in Power Query, Merge Tables, Combine Tables, Left Outer Merge, Replace VLOOKUP with Merge Table, Gross Profit Pivot Table Report, Cross Tabulated Report, PivotTable, Monthly Revenue Report, Basics of PivotTables, Pivot Table Basics, Power Query Basics, Introduction to Power Query

View on YouTube

Excel for Accountants: Formulas, Functions & Formatting to Build Account Templates (37 Examples)

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tB6Zp7
Finished File:  http://ift.tt/2t6W9Uh
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

This is the first video in a series of two videos.
This video will teach you everything you need to know about Excel for your Accounting Job : )

Topics in Video:
(00:08) Introduction to video topics and Excel files and how to navigate large workbook and video
1. (02:13) Keyboards Rule Because They Are Fast
2. (04:33) Number Formatting is a Façade
3. (07:01) How to Add Quickly
4. (10:15) Date Calculation Templates
5. (10:50) TODAY and (19:50) EDATE functions
6. (12:24) Angry Rabbit Mouse Copy Trick
7. (14:46) Date Number Formatting
8. (17:05) Formulas for Date Calculations
9. (20:21) Absolute & Relative Cell References
10. (22:15) Excel’s Golden Rule
11. (24:46) Payroll Quickly
12. (24:46) ROUND Function Importance
13. (24:46) Formulas Can NOT See Number Formatting
14. (31:40) Create Cosumer Loan Amortization Table
15. (33:13) PMT Function to calculate PMT on Annuity with Regular PMT and Constant Rate
16. (36:44) Fill Series to generate list of numbners from 0 to 360
17. (38:51) Smart Tags to Fill End of Month Only (And it knows Leap Year!!!)
18. (40:01) Borders to enhance Report
19. (42:48) Calculations for Amortization Table with Relative and Absolute Cell References
20. (47:10) Invoice Template
21. (47:24) VLOOKUP Function
22. (52:40) Data Validation, List to add Drop-Down List to Cell
23. (54:32) IF Function
24. (54:32) ISBLANK Function
25. (24:46) ROUND Function (link back to earlier part of video)
26. (01:02:35) FV of Investment Template
27. (01:02:35) Mixed Cell References
28. (01:05:50) F4 Key to cycle through 4 different types of Cell References (Merry Go Round)
29. (01:08:05) FV Function
30. (01:11:00) SUMIFS and COUNTIFS to create Summary Reports. Also: Advantage of Formulas Over PivotTable.
31. (01:18:38) Check Book Register Example
32. (01:19:02) Border Formatting
33. (01:20) Formula with Relative Cell References to calculate Balance
34. (01:21:29) IF Function
35. (01:21:29) ISNUMBER Function
36. (01:23:44) 1) Data Validation For Reconciling Column. 2) Conditional Formatting for whole row using Mixed Cell References
37. (01:29:50) Flash Fill to extract and clean Data Quickly

Learn Excel Basics, Learn how to build Accounting Templates, Learn Excel Tricks for your Accounting Job.

View on YouTube