Month: February 2018

Excel & Business Math 35: VLOOKUP & MAX Functions for Percentage Method Federal Income Tax Deduction

Posted on by Annie D

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

In this video learn how to use VLOOKUP & MAX Functions to make Federal Income Tax Withholding Deduction formulas.
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:31) Federal Income Tax Withholdings Deducted from Employees Paychecks
3. (02:42) IRS web site with 2018 Percentage Method Tax Tables.
4. (04:00) 2018 Percentage Method Federal Income Tax Withholding Tables in Excel
5. (06:19) Video Example 1: Calculate Federal Income Tax Withholdings for Marital Status “Single” and Pay Period Weekly. Calculations made by Hand and with MAX and VLOOKUP.
6.
7. (17:05) Use Formula Evaluator to verify that VLOOKUP Formula is working correctly
8. (18:38) Video Example 2: Calculate Federal Income Tax Withholdings for Marital Status “Married” and Pay Period Weekly. Calculations made by Hand and with MAX and VLOOKUP.
9. (21:36) Video Example 3: Calculate Federal Income Tax Withholdings for Marital Status “Single” and Pay Period Weekly for an Entire Payroll Table. Calculations made with MAX and VLOOKUP.
10. (27:33) Video Example 4: Calculate Federal Income Tax Withholdings for Marital Status “Married” and Pay Period Weekly for an Entire Payroll Table. Calculations made with MAX and VLOOKUP. See how to copy a Whole Table for formulas to a new template.
11. (28:28) Summary

Next Video: 36 Excel & Business Math

View on YouTube

Excel & Business Math 34: MEDIAN Function for FICA Social Security & Medicare Payroll Deductions

Posted on by Annie D

Excel & Business Math 34: MEDIAN Function for FICA Social Security & Medicare Payroll Deductions
Download Start Excel File: http://ift.tt/2t0QtAv
Download pdf Notes: http://ift.tt/2Cr8I67
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. (01:28) FICA Payroll Social Security & Medicare Tax Deductions
3. (04:50) Calculating FICA Deductions and Net Pay Long Hand, all three situations
4. (17:32) Learning How to Make a Tax Calculation When There is a Hurdle That Changes The Tax Rate
5. (18:13) Using MEDIAN Function to Deal With Tax Hurdles
6. (31:17) Payroll Employee Table Example for Tax Deductions and Net Pay
7. (37:44) Summary

Next Video: 35 Excel & Business Math

View on YouTube

Excel & Business Math 33: VLOOKUP Function for Incentive Pay: Commissions & Piecework (15 Examples)

Posted on by Annie D

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

In this video learn all about the VLOOKUP Function, How to Build Lookup Tables and all about Incentive Pay such as Piecework and Commission Pay.
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. (02:25) Incentive Pay such as: Straight Piecework, Variable Piecework, Straight Commission, Variable Commission Rates, Commission Rate Based on Amount of Sales
3. (06:42) Straight Piecework Example
4. (08:24) Variable Piecework Example Making the Calculations Long-Hand
5. (11:28) Why Excel is so Helpful AND why we must be REALLY Knowledgeable with Excel.
6. (12:00) Variable Piecework Example Making the Calculations with the “Lookup Table Method”
7. (20:04) Creating the Lookup Table from Scratch
8. (30:31) Variable Piecework Example Making the Calculations with the “Lookup Table Method & VLOOKUP Function using Approximate Match”
9. (40:19) Variable Piecework & VLOOKUP & Approximate Match Lookup for Entire Payroll Table:
10. (44:10) What is VLOOKUP Function?
11. (44:42) “Mash” all 3 VLOOKUP Functions into Single Formula using Clipboard:
12. (not in video look in pdf notes) VLOOKUP Function Arguments (Full Details):
13. (49:00) Lookup Tables are Everywhere
14. (51:55) Straight Commission Rates
15. (52:51) VLOOKUP & Exact Match Lookup for Straight Commission Rates When each Employee has Different Rate
16. (59:00) VLOOKUP & Approximate Match Lookup for Variable Commission Rates; Make Lookup Table and use VLOOKUP
17. (01:15:25) VLOOKUP & Approximate Match Lookup for Commission Rate Based on Amount of Sales Made by the Employee
18. (01:20:02) Summary

Next Video: 34 Excel & Business Math

View on YouTube

Excel & Business Math 32: Rounding Time Calculations to Nearest 5 or 15 minutes using MROUND

Posted on by Annie D

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

In this video learn how to use the MROUND Function to round a time calculation to the nearest 5 minutes or nearest 15 minutes.
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:14) How To use MROUND for Payroll or Product Pricing
3. (06:13) MROUND on a Payroll Time Sheet
4. Day Shift or Night Shift Payroll calculations. See MROUND, MOD and ROUND Functions to make time calculations rounded to the neatest 5 minutes and then calculate Gross Pay for each employee
5. () Summary

Next Video: 33 Excel & Business Math

View on YouTube

Excel & Business Math 31: Night Shift Hours Worked Formula for Payroll Time Sheet (3 Examples)

Posted on by Annie D

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

In this video learn about three different formulas for n Excel Payroll Time Sheet with the Night Shift Employees work past midnight and have a Time Out that is smaller than the Time In.
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:13) What is Night Shift?
3. (01:12) Look at Time Values for Night Shift and Negative Times in Excel
4. (05:18) Formula # 1 using IF Function
5. (09:41) Formulas #2 using Logical Formula or Boolean Formula
6. (13:29) Formula #3 using MOD Function
7. (14:57) Complete Payroll Time Sheet for Day or Night Shift using all three formulas and the ROUND Function.
8. (19:43) Summary

Next Video: 32 Excel & Business Math

View on YouTube

Excel & Business Math 30: Payroll Time Sheets, IF Function, Sheet Reference for Overtime & Gross Pay

Posted on by Annie D

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

In this video learn how to create Daily Time Sheets for the whole week, then use Sheet References, the IF Function and the ROUND Function to calculate the correct Gross Pay, including Overtime, for each Employee.
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. (01:04) Formula For Daily Employee Total Hours Worked
3. (04:57) Summary Table of Employee Hours for the Week
4. (07:47) Sheet Reference to pull Employee Weekly Hour Total to Gross Pay Sheet
5. (11:38) IF Function to calculate the correct Regular Hours for each Employee
6. (15:52) Overtime Hours Worked Formulas
7. (16:46) Regular Hours Gross Pay with ROUND Function
8. (18:26) Overtime Gross Pay with ROUND Function
9. (19:56) Total Gross Pay for each Employee for the week
10. (20:14) Summary

Next Video: 31 Excel & Business Math

View on YouTube

Excel & Business Math 29: Time Number Formatting & Time Calculations in Excel for Payroll Table

Posted on by Annie D

Download Start Excel File: http://ift.tt/2ol0qmU
Download pdf Notes: http://ift.tt/2C9onHg
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn about Time Number Formatting and learn about Time Calculations in Excel to calculate Total Number Of Hours between two time values. Also see a Payroll table that allows us to calculate Hours Worked, Gross Pay, Deductions and Net Pay.
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:51) What is Excel Time Number Format?
3. (07:40) Excel Formula for Total Hours between two-time values
4. (11:27) Excel Formulas for Total Hours between four-time values, Time In and Time Out including Lunch break
5. (15:20) Payroll table with formulas for: Hours Worked, Gross Pay, Deductions, Net Pay, including the correct use of the Round Function
6. (18:51) Summary

Next Video: 30 Excel & Business Math

View on YouTube

Excel Magic Trick 1478: Conditional Format Row When Event Code is a Duplicate AND Task = “Special”

Posted on by Annie D

Download Files: Start Excel File: http://ift.tt/2FaY1mx
Entire page with all Excel Files for All Videos: http://ift.tt/1zHY6GI about how to
In this video learn how to use Conditional Formatting to format the row in a data set and how to use the COUNTIFS and AND Functions to create a Logical Formula that will deliver a TRUE when the Event Key is a Duplicate AND the Task Column equals “Special”.

View on YouTube

Excel Magic Trick 1477: COUNTIFS & AND Functions for Duplicate Event Codes AND Task = “Special”

Posted on by Annie D

Download Files: Start Excel File: http://ift.tt/2FaY1mx
Entire page with all Excel Files for All Videos: http://ift.tt/1zHY6GI about how to
In this video learn how to use the COUNTIFS and AND Functions to create a Logical Formula that will deliver a TRUE when the Event Key is a Duplicate AND the Task Column equals “Special”.

View on YouTube

Excel & Business Math 28: IF Function, IS Functions & Building a Check Register Other IF Tricks too

Posted on by Annie D

Download Start Excel File: http://ift.tt/2C1u5ea
Download pdf Notes: http://ift.tt/2ByEwVd
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to build a check Register using the IF Function and the ISBLANK Function
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. (01:08) Double Vertical Borders for Checkbook Register
3. (03:16) Data Validation to ONLY allow an X in the Reconciliation Column
4. (04:38) Conditional Formatting with Logical Formula and Mixed Cell Refence to format the row yellow when the item has been reconciled
5. (08:20) Checkbook Balance Formula
6. (11:07) First look at the IS Function: ISNUMBER
7. (14:07) First look at the IF Function
8. (15:20) Checkbook Balance Formula #1 using IF Function, ISNUMBER Function and a Zero Length Text String.
9. (16:37) First look at “Show Nothing”, Double Quote, Double Quote, Zero Length Text String.
10. (18:06) Test IF Function. Use Formula Evaluator to see how the IF Function puts One of Two Things in the cell.
11. (21:00) Checkbook Balance Formula #2 using IF Function, ISBLANK Function and a Zero Length Text String
12. (24:48) Difference between using ISNUMBER or ISBLANK for the Logical Test.
13. (25:38) ISFORMULA & FORMULATEXT to show formula when formula is entered into a cell.
14. (28:35) Test to see if Zero Length Text String is in cell using the ISBLANK, ISTEXT and LEN Functions
15. (30:44) Summary

Next Video: 29 Excel & Business Math

View on YouTube

Excel & Business Math 27: Bank Reconciliation Made Easy

Posted on by Annie D

Excel & Business Math 27: Bank Reconciliation Made Easy
Download Start Excel File: http://ift.tt/2Bxb4zg
Download pdf Notes: http://ift.tt/2o0wqgO
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn about checks and Bank Reconciliation. Learn about the concepts of why and how we perform bank reconciliation. Also learn about how to show two worksheets from the same workbook side-by-side. Also learn hoe to create Conditional Formatting and data Validation to format or highlight a row in a checkbook register.
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. Parts of a Checks
3. Types of Endorsements
4. Bank Services & Fees
5. Bank Reconciliation Example 1: Make sure both sides are actually Equal.
6. Bank Reconciliation Example 2: Make sure both sides are actually Equal and enter the bank statement entries that are not in our records, into our records
7. Bank Reconciliation Example 3: Bank Reconciliation with a Bank Reconciliation Form
8. How to show two worksheets from the same workbook side-by-side: View, New Window, Arrange All
9. How to use Conditional Formatting and Data Validation to format or highlight row that has an “X” for “item has been reconciled”.
10. () Summary

Next Video: 28 Excel & Business Math

View on YouTube

Excel & Business Math 26: Is Rate of Change Same for Increase by $10 and Decrease by $10?

Posted on by Annie D

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

In this video learn about how the Rate of Change for a $10 increase and $10 decrease are different.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.

Next Video: 27 Excel & Business Math

View on YouTube