Essential Excel For Project Managers

A practical guide on how to use Excel for preparing and managing projects

What you'll learn
How to prepare a cost-benefit analysis
How to create a dynamic dashboard in Excel
How to create a Gantt chart in Excel
Create a ranking of different options
Select the right projects using Excel
Use Pivot Tables and Excel Tables to analyze performance of projects
Requirements
Basic Excel
Description
What is the aim of this course?Nowadays, project managers have plenty of apps, and software that support their work on a daily basis. Nevertheless, Excel still remains an important tool used to estimate the costs and benefits of the project. In some cases, Excel can be used to manage the project. In this course, I will teach how to use fast and efficiently Excel as a Project Manager.In the course you will learn the following things:Essential Excel functions that will be useful during your work as a Project ManagerHow to conduct cost-benefit analysis using ExcelHow to select projects using ExcelHow to create and use rankings in ExcelHow to create Gantt Charts, dashboards, and project cards in Excel.How to summarize progress in ExcelThis course is based on my 15 years of experience as a consultant in top consulting firms and as a Board Member responsible for strategy, performance improvement, and turn-arounds in the biggest firms from Retail, FMCG, SMG, B2B, and services sectors that I worked for. I have carried out or supervised over 90 different performance improvement projects in different industries that generated in total 2 billion of additional EBITDA. On the basis of what you will find in this course, I have trained in person over 100 consultants, business analysts, and managers who now are Partners in PE and VC funds, Investment Directors and Business Analysts in PE and VC, Operational Directors, COO, CRO, CEO, Directors in Consulting Companies, Board Members, etc. On top of that my courses on Udemy were already taken by more than 132 000 students including people working in EY, Walmart, Booz Allen Hamilton, Adidas, Naspers, Alvarez & Marsal, PwC, Dell, Walgreens, Orange, and many others.I teach through case studies, so you will have a lot of lectures showing examples of analyses, and tools that we use. To every lecture, you will find attached (in additional resources) the Excels as well as additional presentations, and materials shown in the lectures so as a part of this course you will also get a library of ready-made analyses that can, with certain modifications, be applied by you or your team in your work.Why have I decided to create this course?Project Managers will have to use Excel in a different way than Analysts. Moreover, in some cases, you cannot use more advanced tools for project management as your team is obsessed with Excel. Project Sponsor will also ask you to create dashboards, Gantt charts, and a summary of the results of projects. Therefore, I have decided to make this course that will help students work fast and efficiently in Excel before and during projects. The course will give you the knowledge and insight into real-life case studies that will make your life easier. Thanks to this course, you will know what and how to prepare and manage efficiently any projects with the help of Excel.To sum it up, I believe that if you want to become a world-class Project Manager, you have to have a pretty decent understanding of Excel. That is why, I highly recommend this course to Project Managers, especially those who have to report to the Steering Committee the results of many projects. The course will help you become an Excel on the level of McKinsey, BCG, Bain, and other top consulting firms.In what way will you benefit from this course?The course is a practical, step-by-step guide loaded with tones of analyses, tricks, and hints that will significantly improve the speed with which you understand, and analyze the businesses. There is little theory – mainly examples, a lot of tips from my own experience as well as other notable examples worth mentioning. Our intention is that thanks to the course you will learn:Essential Excel functions that will be useful during your work as a Project ManagerHow to conduct cost-benefit analysis using ExcelHow to select projects using ExcelHow to create and use rankings in ExcelHow to create Gantt Charts, dashboards, and project cards in Excel.How to summarize progress in ExcelYou can also ask me any question either through the discussion field or by messaging me directly.How the course is organized?The course is divided currently into the following sections:Introduction. We begin with a little introduction to the course as well as some general info on how the course is organizedHow to merge data from different sources. In this section, we will see how we can use essential functions to merge data from different sources. You will need them not only to analyze data but also to summarize results and create reports for the Steering CommitteeTo-do lists in Excel. In the next section, I will discuss how you can create an easy-to-navigate to-do list in Excel. I will show you some hacks, that will turn the to-do list into an effective tool for efficient project management.Cost-Benefit Analyses in Excel. As a Project Manager, you will quite often have to see whether the project makes sense or not. That’s why I will explain to you how to create a cost-benefit analysis. We will look at useful frameworks and we will apply them to case studies.How to create a Project Charter using Excel. In this brief section, I will show you how to create a project charter, and project card in Excel.How to use the IF function and what you can do instead? IF function is not always the best choice. That’s why in this section I will show you what other, more flexible functions you can use instead.Gantt Charts in Excel. In this brief section, I will show you how to create a Gantt chart in Excel.How to create a dashboard in Excel. In this brief section, I will show you how to create a dashboard in Excel.Rankings. In some cases, you may also need to present different options to solve specific problems. Your Supervisor will expect you to somehow rank those options. That’s why in this section, I will show you how to create rankings in Excel.Project selection in Excel. In the last section, we will see how you can select which projects should be started. We will look at strategic, portfolio, and bottleneck approaches.You will be able also to download many additional resources1. Useful frameworks and techniques2. Analyses showed in the course3. Additional resources4. Links to additional presentations, articles, and movies5. Links to books worth readingAt the end of my course, students will be able to…How to prepare a cost-benefit analysisHow to create a dynamic dashboard in ExcelHow to create a Gantt chart in ExcelCreate a ranking of different optionsSelect projects using ExcelUse Pivot Tables and Excel TablesWho should take this course? Who should not?Project ManagersManagersStartup Founders

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 A little bit about me

Lecture 3 How the course is organized

Lecture 4 How to deal with Blurry image

Lecture 5 How to reach additional resources

Section 2: How to merge data from different sources

Lecture 6 Basic concepts used in Excel – Introduction

Lecture 7 How to merge data from different sources

Lecture 8 VLOOKUP – Introduction

Lecture 9 VLOOKUP with approximate match – Introduction

Lecture 10 What you can use the VLOOKUP for?

Lecture 11 Basic VLOOKUP usage

Lecture 12 Some comments on basic VLOOKUp

Lecture 13 How to assign categories using VLOOKUP?

Lecture 14 How to use HLOOKUP?

Lecture 15 The logic of MATCH function

Lecture 16 How to assign categories based on 2 criteria using VLOOKUP and MATCH function?

Lecture 17 How to assign categories based on 2 criteria using HLOOKUP and VLOOKUP function

Lecture 18 How to assign categories using SUMIFS function – Basic Usage

Lecture 19 Assigning customers to segments – Case Introduction

Lecture 20 Assigning customers to segments – Data Available

Lecture 21 Assigning customers to segments – Solution

Lecture 22 The logic of INDEX function

Lecture 23 INDEX & MATCH function – Introduction

Lecture 24 INDEX & MATCH function – Exercise – Case Introduction

Lecture 25 INDEX & MATCH function – Exercise – Case Solution

Lecture 26 SUMPRODUCT function

Lecture 27 Conditional Formating

Section 3: To-do lists in Excel

Lecture 28 To-do lists in Excel – Introduction

Lecture 29 Make to do list

Lecture 30 Managing the team in practice

Lecture 31 To-do list in Excel – Case Introduction

Lecture 32 To-do list in Excel – Dropdown list

Lecture 33 To-do list in Excel – Table with slicers

Lecture 34 To-do list in Excel – Dynamic Dropdown List

Lecture 35 To-do list in Excel – Additional Comments

Section 4: How to estimate the costs and benefits of projects

Lecture 36 Costs and benefits of the project – Introduction

Lecture 37 Bottom-up approach

Lecture 38 Estimating the costs of a wedding – Introduction

Lecture 39 Estimating the costs of a wedding – Partial Solution

Lecture 40 Estimating the costs of a wedding – Excel Part 1

Lecture 41 Estimating the costs of a wedding – Excel Part 2

Lecture 42 Rearranging the Office – Case Introduction

Lecture 43 Rearranging the Office – Data Available

Lecture 44 Rearranging the Office – Coffee Points

Lecture 45 Rearranging the Office – Microwave Ovens

Lecture 46 Rearranging the Office – Stairs

Lecture 47 Cost reduction Investment – Ceramic Tiles – Introduction

Lecture 48 Cost reduction Investment – Ceramic Tiles – Current Costs

Lecture 49 Cost reduction Investment – Ceramic Tiles – Future Costs

Lecture 50 Cost reduction Investment – Ceramic Tiles – NPV

Lecture 51 Cost reduction Investment – Ceramic Tiles – Solution in Power Point

Section 5: How to create a Project Charter using Excel

Lecture 52 How to create a Project Charter using Excel – Introduction

Lecture 53 Project Card / Charter – Example

Lecture 54 Project Summary Tool – Example

Lecture 55 Project Charter in Excel – Case Introduction

Lecture 56 Project Charter in Excel – Data Available

Lecture 57 Project Charter in Excel – Solution

Section 6: How to create a dashboard in Excel

Lecture 58 How to create a dashboard – Introduction

Lecture 59 Pivot tables – Introduction

Lecture 60 How to use pivot tables?

Lecture 61 How to use pivot charts?

Lecture 62 How to combine pivot tables with regular calculations?

Lecture 63 How to use slicers?

Lecture 64 How to replace pivot tables with sumifs and countifs

Lecture 65 Going from general to specific with pivots

Lecture 66 Combining slicers with Pivot Charts

Lecture 67 Building a Dashboard – Part 1

Lecture 68 Building a Dashboard – Part 2

Lecture 69 Look & Feel of Pivot Chart

Section 7: Gantt Charts in Excel

Lecture 70 Gantt Chart in Excel – Introduction

Lecture 71 Gantt Chart – Case Introduction

Lecture 72 Gantt Chart – What we want to get

Lecture 73 Gantt Chart – Timeline

Lecture 74 Gantt Chart – Chart Lines

Lecture 75 Gantt Chart – Slicers

Lecture 76 Gantt Chart – Final

Section 8: Rankings in Excel

Lecture 77 Rankings in Excel – Introduction

Lecture 78 Rankings – Introduction

Lecture 79 How to expand the brand – Introduction

Lecture 80 How to expand the brand – Solution – Part 1

Lecture 81 How to expand the brand – Solution – Part 2

Lecture 82 How to expand the brand – Scatter Graph

Lecture 83 Expansion strategy into other countries – Introduction

Lecture 84 Expansion strategy into other countries – Case Introduction

Lecture 85 Expansion strategy into other countries – Data for the case study

Lecture 86 Expansion strategy into other countries – Which function to use

Lecture 87 Expansion strategy into other countries – Solution in Excel

Lecture 88 Expansion strategy into other countries – Solution in Power Point

Section 9: Project selection in Excel

Lecture 89 Project selection – Introduction

Lecture 90 Strategic alignment framework – Introduction

Lecture 91 Strategic alignment framework – Case Introduction

Lecture 92 Strategic alignment framework – Overiew of Projects

Lecture 93 Strategic alignment framework – Did we achieved the goal

Lecture 94 Strategic alignment framework – When it makes sense to use it

Lecture 95 Value proposition alignment framework – Introduction

Lecture 96 Value proposition alignment framework – Cases Introduction

Lecture 97 Value proposition alignment framework – Data Available

Lecture 98 Value proposition alignment framework – Overview of Projects Part 1

Lecture 99 Value proposition alignment framework – Overview of Projects Part 2

Lecture 100 Value proposition alignment framework – Solution Summary

Lecture 101 Value proposition alignment framework – When it makes sense to use it

Lecture 102 Bottleneck Framework – Introduction

Lecture 103 Low-hanging fruits

Lecture 104 Theory of constraints and bottlenecks

Lecture 105 Removing bottlenecks – Content marketing agency – Introduction

Lecture 106 Removing bottlenecks – Content marketing agency – Solution

Lecture 107 Bottleneck Framework – The Process

Lecture 108 Bottleneck Framework – Case Introduction

Lecture 109 Bottleneck Framework – Data for the Case

Lecture 110 Bottleneck Framework – Case – Summary of Projects

Lecture 111 Bottleneck Framework – Case – Analysis of Bottlenecks

Lecture 112 Bottleneck Framework – Case – the Approach to solving the case

Lecture 113 Bottleneck Framework – Case – Project New Format

Lecture 114 Bottleneck Framework – Case – Project Private Label

Lecture 115 Bottleneck Framework – Case – Project Rebranding

Lecture 116 Bottleneck Framework – Case – Project Hospitals

Lecture 117 Bottleneck Framework – Case – Project Marektplace

Lecture 118 Bottleneck Framework – Case – Project Loyalty Program

Lecture 119 Bottleneck Framework – Case – Project Foreign Expansion

Lecture 120 Bottleneck Framework – Case – Project BI

Lecture 121 Bottleneck Framework – Case – Project MA

Lecture 122 Bottleneck Framework – Case – Summary

Lecture 123 Bottleneck Framework – When it makes sense to use it

Section 10: Conclusions

Lecture 124 Bonus Lecture

Project Managers,Managers,Startup Founders