Microsoft Excel – practical solutions for logisticians: calculation models, data analysis using pivot tables as well as creation of effective charts

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Faster work in Excel - You will speed up daily tasks with keyboard shortcuts, efficient copying of data and sheets, and better file handling, so you can prepare logistics reports and summaries much faster.

Efficient data import and cleanup - You will learn how to pull data from CSV, XLSX, PDF, websites, and databases, then reshape it in Power Query, reducing manual fixes and errors in your day-to-day analyses.

Better demand forecasting - You will use averages, weighted moving averages, and exponential smoothing to estimate demand more accurately, plan stock levels better, and reduce shortages or excess inventory.

More reliable cost and sales analysis - You will apply logical, lookup, and conditional formulas to analyze margins, costs, and sales performance, helping you make quicker and better-informed operational decisions.

Cleaner and usable text data - You will learn to split, combine, and clean text using Text to Columns, text functions, and Flash Fill, so you can prepare report-ready data without tedious manual editing.

Faster exception detection - You will use conditional formatting to highlight deviations, duplicates, unique records, and extreme values, making it easier to spot issues in logistics and sales datasets early.

Better schedule planning - You will calculate days, hours, and working days between dates, which will help you plan deliveries, execution deadlines, and analyze delays and operational workload more precisely.

Reports and decision optimization - You will build pivot tables, charts, and Solver models to analyze sales, transport costs, and profit scenarios, then present the results clearly in reports ready for printing or PDF.

Training programme

1. Improving logistics tasks using Excel tools

quick data analysis,
shortcuts facilitating and speeding up work with the program,
assigning and creating value formats: e.g. €, pcs., pallets, m2, m3,
copying data, sheets, formats, creating references between sheets, etc.,
performing calculations using the Subtotals tool.

2. Import of data for logistics calculations – PowerQuery add-in

retrieving data from a worksheet, TXT/CSV/XLSX/PDF files,
importing tables and queries from databases,
retrieving data from the internet,
introduction to PowerQuery and basic data transformations,
updating retrieved data.

3. Demand forecasting

arithmetic mean,
weighted moving average,
exponential smoothing.

4. Economic analysis using functions

calculations using AutoSum,
logical functions: IF OR, AND, IFNA,
lookup functions: VLOOKUP or XLOOKUP,
statistical and mathematical functions COUNTIF, SUMIF, AVERAGEIF, ROUND, SUMPRODUCT, COUNTIFS, SUMIFS, etc.,
relative and absolute references (the $ symbol in the cell address),
nesting – creating calculations in one cell consisting of several functions.

5. Text processing

splitting and joining texts,
the Text as Columns tool,
text functions: LEFT, RIGHT, MID, PROPER.CASE, LOWER, LEN, TRIM etc.,
flash fill.

6. Conditional formatting

building reports based on conditions from reports,
current reports, finding duplicates and unique values in data,
highlighting the lowest/highest values in a range,
creating conditional formats using formulas and functions.

7. Dates

discussion of date and time in Excel,
calculations of the number of days between dates and between time notation,
calculations of working days between dates,
date functions: WORKDAYS, WORKDAY, DATEDIF, DAY, MONTH, YEAR, DATE etc.

8. Working with the Table object

formatting data,
performing calculations: Sum, Average, etc.

9. Solver Add-in

searching for the markup ratio on the purchase price while obtaining the planned profit,
searching for the sales growth ratio while obtaining the appropriate level of profit growth,
optimization of transport costs.

10. Charts

principles of creating charts, selecting a chart for the presented data,
modifying charts using Chart Style, Quick Layout and Change Colors,
column, bar, line, pie, doughnut charts, etc.,
creating a Pareto chart,
creating charts with two axes,
creating charts of trends over time and editing them.

11. Solver Add-in

searching for the markup rate on the purchase price while obtaining the planned profit,
searching for the sales growth rate while obtaining the appropriate level of profit growth,
optimization of transport costs.

12. Sorting and filtering

simple sorting and sorting by two criteria,
filtering numeric data, text data and data containing dates,
advanced filtering.

13. Pivot Table

introduction to analyses using a pivot table,
creating basic calculations: sum, average, max, min, data counting,
sorting and filtering data in a pivot table,
grouping by dates and numerical values,
sales analyses, logistics costs, etc.,
discussion of basic errors made by users.

14. Printing

defining the print area and adjusting the data range to the paper size,
setting margins, page orientation, etc.,
repeating the first row and the first column of the table on each of the printed pages,
creating page numbering,
printing to PDF.

What are the prerequisites for participating in the training?

Basic Excel navigation - You should be comfortable moving around a worksheet, entering data into cells, selecting ranges, and saving files, so you can focus on analysis rather than basic software use.

Simple formulas and cell references - You should understand how simple formulas work and how to reference cells and ranges, because the course expands these basics into more advanced calculations and analysis.

Working with tabular data - You should have some experience with simple row-and-column datasets, so it will be easier for you to sort, filter, analyze sales figures, and work with larger data sets.

Business data awareness - You should understand basic logistics, distribution, or sales terms such as costs, lead times, or volume, so you can correctly interpret the results used during the exercises.