Description:
In this video we show to you how inventory aging report with QuickBooks Enterprise and Excel pivot tables works
Video:
Table of contents:
- 00:00:00 – Intro (inventory aging report)
- 00:02:17 – Preferences (items & inventory) (company preferences) (advanced inventory settings)
- 00:03:32 – Turn on FIFO (edit, preferences, items & inventory, company preferences, advanced inventory settings, FIFO)
- 00:04:12 – Reports (inventory, FIFO cost lot history by item) (collapse columns we don’t need)
- 00:06:29 – Memorize Report
- 00:07:16 – Excel tab (create new worksheet) (send report to excel) (export)
- 00:07:52 – Excel sheet (CSV file) (clean up and label sheet)
- 00:09:09 – Item list (QuickBooks Desktop) (edit item)
- 00:10:21 – Go to tool (go to special) (excel)
- 00:10:51 – Formula (excel sheet) (general)
- 00:11:56 – Copy/paste special columns (excel)
- 00:12:30 – Data (filter) (sort) (excel)
- 00:13:20 – Data, sort (sort by, sort on) (re-sort column) (excel)
- 00:15:55 – No. of days (excel) (formula)
- 00:19:15 – Multiply the no. of days times quantity (DaysxQTY) (formula) (excel)
- 00:20:27 – How to create pivot table (select all and insert tab) (pivot table) (create pivot table) (rows: item, subitem) (analyze, fields items, & sets, insert calculated field)
- 00:21:25 – Insert calculated field (formula) (add)
- 00:22:19 – Reports (inventory, inventory stock status by item)
- 00:24:00 – One type of inventory aging report (average number of days)
- 00:24:14 – Build side table (0-90 days; 91-180 days; 181-365 days; 365+) (AGINGTABLE)
- 00:25:54 – Aging column (formula) (vlookup)
- 00:28:00 – Build a new pivot table (rows: item, subitem; columns: aging; values: sum of acq amt)
- 00:29:50 – Reports (inventory valuation summary)
