Tag: Productivity

Excel & Business Math 39: Create Excel Invoices, Data Validation Drop-down, VLOOKUP & IF Functions

Posted on by Annie D

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

In this video learn how to create an Invoice that you can use over and over using Data Validation Drop-down, VLOOKUP, ISTEXT, ISNUMBER & IF Functions. Also see how to create a formula to extract Invoice Number from Sheet name using the CELL, REPLACE, SEARCH and LEFT Functions.
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 to creating an invoice in Excel
2. (01:30) Format Invoice
3. (02:32) Insert Company Logo and Boomerang Picture
4. (06:01) Calculate Wholesale Cost using a Single Trade Discount
5. (08:30) Data Validation – List to insert Drop-down list of Products
6. (11:29) Exact Match VLOOKUP to get price
7. (13:06) IF and ISTEXT to turn formula off when no product entered
8. (17:01) IF & ISNUMBER for Line Item Total
9. (18:41) SUM Function and other formulas for totals
10. (20:18) Page Setup
11. (21:44) Copy a Sheet using Mouse & Ctrl Key
12. (24:57) CELL, SEARCH, REPLACE and LEFT Functions to get Invoice Number
13. (29:25) Summary

Next Video: 40 Excel & Business Math

View on YouTube

Excel & Business Math 38: Date Math, Invoices, Cash Discounts on Invoices, Nested IF Functions

Posted on by Annie D

Download Start Excel File: http://ift.tt/2FEK4jJ
Download pdf Notes: http://ift.tt/2Hn20MW
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:48) Enter Dates in Excel
3. (03:19) Date Number Formatting
4. (05:44) Date Math, Date Formulas and Date Functions like EDATE and EOMONTH
5. (15:18) Invoices, Different Elements
6. (17:11) Shipping Terms, including FOB Shipping and FOB Destination
7. (19:40) When Tax is Paid, including selling final item at a whole sale cost
8. (20:58) Cash Discounts: Incentive to Pay Invoice Early
9. (22:44) Ordinary, Cash Discounts Example 1. First Example and Close look at what Terms Mean.
10. (33:04) Ordinary, Cash Discounts Example 2
11. (38:53) Ordinary, Cash Discounts Example 3. This example shows what to do if you have two discount opportunities, including how to use Two IF Functions in a single formula, known as Nesting If Functions.
12. (48:50) AS OF, Cash Discounts
13. (49:40) ROG, Cash Discounts
14. (51:10) EOM, Cash Discounts. Including the EOMONTH Function and how to use a Logical Value (TRUE / FALSE) value in a formula as a 0 or 1.
15. (01:02:42) Summary

Next Video: 39 Excel & Business Math

View on YouTube

Excel & Business Math 37: Trade Discounts, Series Discounts, Net Cost Equivalents & Wholesale Cost

Posted on by Annie D

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

In this video learn how to calculate Trade Discounts (Single and Series) in order to calculate a Wholesale Cost. Learn about Business Terms. Learn about using VLOOKUP and IF Functions to automat calculations.
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:27) Business Terms: Retail & Wholesale Businesses. List Price and Wholesale Cost. Trade Discount $ Amount.
3. (05:42) Examples of Wholesale Cost Catalogs and the four different formulas needed to calculate Whole Sale Costs under four different assumptions.
4. (11:28) Single Trade Discount % Video Example. See three different Methods to calculate Wholesale Cost.
5. (15:02) Calculate Series Discounts. See three different Methods to calculate Wholesale Cost.
6. (20:28) Series Trade Discount % with Two Successive Discounts
7. (21:42) Series Trade Discount % where 2nd Discount Depends on Quantity Purchased. Learn how to use VLOOKUP Function.
8. (26:23) Series Trade Discount % where 2nd & 3rd Discount Depends on Quantity Purchased.
9. (30:43) Learn how to use IF & VLOOKUP Functions to make calculations automatic for a Series Discount.
10. (34:57) Summary

Next Video: 38 Excel & Business Math

View on YouTube

Excel & Business Math 36: Comprehensive Excel Payroll Example: SUMIFS, MROUND, MOD, VLOOKUP & More

Posted on by Annie D

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

In this video learn how to create a complete payroll table solution based on the topics learned in Videos #29 to 35. Learn about the SUMIFS Function for the first time.
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:15) Formula for: Hours Worked, using the Functions: SUM, MOD, MROUND
3. (06:42) Formula for: Total Hours, using the Function: SUMIFS
4. (11:25) Formula for: Regular Hours, using the Function: IF
5. (13:26) Formula for: Overtime Hours
6. (13:48) Formula for: Regular Gross Pay, using the Function: ROUND
7. (14:24) Formula for: Overtime Gross Pay, using the Functions: ROUND
8. (14:57) Formula for: Total Gross Pay, using the Functions: SUM
9. (15:16) Hide Columns
10. (15:56) Formula for: Cumulative Gross Pay After This Paycheck
11. (16:24) Formula for: Social Security Deduction, using the Functions: MEDIAN, ROUND
12. (18:42) Formula for: Medicare Deduction @ 1.45%, using the Functions: MEDIAN, ROUND
13. (20:09) Formula for: Medicare Deduction @ 2.35%, using the Functions: MEDIAN, ROUND
14. (22:30) Formula for: Taxable Pay, using the Function: MAX
15. (23:30) Formula for: Federal Income Tax Withholding, using the Functions: VLOOKUP, ROUND
16. (26:03) Formula for: Total Deductions
17. (26:30) Formula for: Net Pay
18. (26:44) Summary

Next Video: 37 Excel & Business Math

View on YouTube

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