March 2018 Webinar: My Most Important Excel Formulas; Would Recommend You Master Them

As a full-time Microsoft Excel consultant, of all the over 450 formulas in Excel there are just a handful I focus on in my training classes because they provide the most value in practical day-to-day use of Excel. If you examine any reports or templates (even the most complex one you've ever come across), you will see that the these formulas are the ones powering those reports.

In this webinar, I will be taking you through those formulas in my usual very practical way and provide you with the practice file for your own use later on.

The formulas are:
1. VLOOKUP
2. HLOOKUP
3. LOOKUP
4. INDEX & MATCH
5. IF
6. SUM
7. COUNTIF
8. SUMIF
9. SUMPRODUCT
10. ROUND
11. CEILING
12. CONCATENATE
13. LEFT
14. RIGHT
15. MID
16. TODAY
17. DAY
18. MONTH
19. YEAR
20. UPPER
21. LOWER
22. PROPER
23. RAND
24. RANDBETWEEN
25. INDIRECT

The webinar was live on YouTube and the recording is made available at https://www.youtube.com/watch?v=3KxWVqqV6ZA

Time: 3:00pm (GMT +1)
Date: 28 March 2018
Venue: YouTube Live (https://www.youtube.com/watch?v=3KxWVqqV6ZA)


To be in loop of all our future webinars, then sign up to our Webinar Directory (http://eepurl.com/bKwoaT)

Recorded Video of the Webinar On What-If-Analysis (Goal Seek, Scenario Manager, Data Table) and Solver in Excel

If you missed the live broadcast of the webinar, below is the link to the YouTube recording: https://www.youtube.com/watch?v=eE1yV9RNIko

We covered the following:

1. How to use Goal Seek to plan towards achieving a specific target/goal. I used four different examples to demonstrate the power of Goal Seek, from business loan planning to product break-even quantity analysis.
2. How to handle different scenarios and plan more proactively using Scenario Manager. I gave an illustration of how the Nigerian budget office creates scenarios around the crude oil price. The benchmark price they publish in the annual budget that is approved is just one of the many possible average prices of crude oil that they have run the budget through. It helps them put in place the processes and plans that might be drawn on if prices go too far off the benchmark value. Then I demoed how I use it for training planning, to measure the profitability of any training class based on best case, likely case and worst case scenarios.
3. How to use Data Table to plan more flexibly. It allows you to monitor the results for multiple combinations of changes in two input variables/parameters. I demoed how it would help with determining the profitable price and number of participants combination for a training class idea.
4. Finally, I showed us how to enable and use Solver for optimization analysis in business. Maybe you want to make the most of your shift workers or run your factory machines more productively or maximize profit margin or minimize costs, once you can outline your business in numbers in Excel linking the inputs to outputs, Solver will do the rest. I demoed how an FMCG uses it to determine how to supply its distributors from their warehouses in such a way that they minimize the freight cost.

You should watch the recorded session: https://www.youtube.com/watch?v=eE1yV9RNIko 

The Excel Formulas That Help You With Your Personal Finance and Investment Plans

In september, we had a webinar on Using Microsoft Excel for Investment Planning, Personal Finance & Financial Analysis. Many people loved it and the practical application in their own personal finance and family finances/investment planning. You can still watch the recorded video on YouTube.

Today, however, I have decided to do the written version of it so many more people can benefit from it. You can download the practice along Excel file here.

There are five major formulas you need to have a good grasp of if you want to put some structure and calculations around your financial goals and investment strategy. And they are:

1. PMT
2. PV
3. FV
4. RATE, and
5. NPER

PMT

PMT is the Excel formula you use to determine how much you need to save regularly (weekly, monthly, yearly) in an interest bearing account to achieve a particular set financial goal (education fund for your children, buy a house, buy a car or be a multi-millionaire). It is also the formula you use to calculate how much you pay back monthly or yearly for a loan that requires you to pay back the same amount (to cover both interest and principal) over the lifetime of the loan.

How it works is very easy. And below are the transcripts of the demonstration in the video and practice file.

a. Want to have N20 million saved in an education fund for your children by 2030 (13 years time).

Question: How much should you contribute in a 15% annual interest bearing savings/investment account?

PMT has five parameters:

• Rate: The interest rate. And in this sample case, it is the 15% annual interest rate.
• Nper: The number of periods. In this case, it is the number of years you plan to save for, 13 years.
• Pv: Present Value. How much you currently have saved in the investment account. In this case, since we are starting from scratch, that would be zero.
• Fv: Future Value. The final amount you want in the investment account by the end of the specified period. In this case, 20 million Naira. Notice the minus I put in the formula, it's just a technical way of getting Excel to show the answer in a positive value. That's all.
• Type: Will you be saving at the end of the year or at the start of the year. For end of year, you put 0 or leave it empty, and for at the start of the year, you put 1. The logic applies for other types of period -- weekly, monthly etc.

One very important thing to note is that the rate and number of periods must agree. You can't use annual rate to calculate monthly contributions without first converting that annual rate to monthly rate. You should check out the part in the practice file on monthly contribution for the same goal to get a good grasp of how to convert from annual rate and annual period to monthly rate and monthly period.

b. You bought a 3 bedroom flat for N100 million and were allowed to do 25% down payment while flexible spreading the remainder in a monthly repayment at a 2% monthly rate.

Question: How much will you pay if you spread the payment over 5 years (60 months)?

Try figure this one out and compare your answer with mine. 

In this sample case, PV is the loan amount outstanding. NPER is the duration. Rate is the repayment interest rate. FV is zero since you mustn't owe them anymore at the end of the duration nor pay them excess. And TYPE is at month end repayment (hence, the leaving empty).

PV

PV is for calculating the worth of an investment project after factoring the cost of the capital (loan interest rate, if you borrowed the money from the bank). 

c. You are part of board of directors for a manufacturing company. They are considering a business idea that will cost N300 million and generate N50 million every year into the 9th year.

Question: If the company's cost of capital (discount rate) is 15% what will be the present value of the business idea if executed and is it a profitable one?

Notice how there is all those components of PMT formula in there -- Rate, Nper, Fv and Type. They still mean the same as previously explained. This time, though, we have the periodic inflow/payment from the business project (same as PMT) and want to calculate the present value/worth (PV). If we can be able to service the cost of the capital deployed and still turn a profit (pay back both the interest and capital of the borrowed money to finance the business idea, and still have something left as profit/compensation for all our stress).

Again the minus in the PMT box is to get Excel to display the final answer in a positive figure. Nothing more.

FV

FV is a very interesting and useful financial planning formula. It allows you estimate how much you will have in an interest bearing account if you do consistent periodic (weekly, monthly or yearly) contribution/saving. 

d. You invest/save N40,000 monthly in a 15% interest rate investment account. 

       Question: In 10 years, how much will you have? What about in 30 years?

I don't think I need to explain how this work, based on all my previously explanations you should be able to figure out how it works.

Also notice how the future value stratospherically increase when you save for 30 years as against the 10 years? That is the power of compound interest.

RATE

This is useful for those who take microfinance loans or co-operative loans or buy land on installment payment or any loan that you are required to pay specific constant amounts regularly. It would make a lot of sense to calculate what the actual rate of the loan is, since most times they don't provide you the rate.

e. Joe took a 6 months loan of N400,000 from LAPO Microfinance bank. He would be paying back N479,000 split into equal monthly payments.

Question: What is the rate he was given the loan at?

No comment. You go figure out.

NPER

This is useful for calculating how long it would take you to finish paying for a car or home if you opt for an installmental payment amount you choose. 

f. Your company has a car loan scheme. You took N4 million loan to buy a car and you are allowed to make monthly repayment at a 2% monthly rate.

Question: How long will it take you to pay off the car loan if you make a monthly payment of N200,000?

No comment. You go figure out.

Don't forget to watch the demonstration video and work along with the practice file.

Recording of June 2017 Webinar On Useful Add-ins You Should Be Aware Of In Excel

This is a webinar on the add-ins I use a lot.

I work primarily with Excel and do projects for different companies across different industries and different countries/continents. To make my work and life easy I often use some add-ins in Excel.

Some of those add-ins are enhancers, enabling me achieve faster what I could still have done without the add-ins. And some are indispensable, without them I couldn't have gotten the job done.

The add-ins are

1. Fuzzy Lookup Add-in
2. PowerPivot
3. Name Manager +
4. Solver
5. Data Analysis Toolpak
6. PowerQuery
7. Nigerian Market Data

You can watch the recording of the webinar at https://www.youtube.com/watch?v=vbKf76RqIp4

There were some issues at the beginning and somewhere in the middle, you might need to fast forward through those places till I am able to do an edit of the video (hopefully this night).

Enjoy!

Webinar: Excel vs Python vs R for Data Analysis. Making the right pick.

There is a lot of textbook arguments on Excel vs Python vs R. In fact, a few weeks back I used to be part of that argument. I took sides with Excel. And I had my solid reasons.

But now I don't take sides anymore. I focus on a bigger picture, a picture I became aware of only after I transitioned for reading textbooks and doing video tutorials on Python and R to carrying out live interesting projects with them.

In this webinar I will be sharing my experience on using them all and what practical insight you too need to have to move from those textbook arguments to seeing the big picture. And most importantly, I will be showing you that big picture.

You shouldn't miss this webinar.

Time: 3:00pm to 4:00pm
Date: Tuesday, 21 March 2017
Venue: YouTube Live

You should set it up in your calendar with reminders so you won't miss this special edition.

See you!

Note: The webinar already took place but you can view the YouTube recorded session at https://www.youtube.com/watch?v=YLoOynh8iNQ 

The Recorded Version of Our December Webinar On PowerPivot

On Tuesday we did have the webinar on PowerPivot. For one hour I showed how to combine data from difference sources and easier transform them into ones you can use to create meaningful single page dashboards.

I covered using the Query Editor in Get & Transform panel in Excel. We pulled in over 15 report tables from Excel and one database table from SQL, and combined them to create a dashboard that ties everything neatly together.

You should watch the webinar here: Turbo Charging Your Reports with PowerPivot

For the demo, I worked on real data of the companies listed on the Nigerian Stock Exchange. And we created a dashboard that looked like the one below.

Don't forget to watch the webinar recording here: Turbo Charging Your Reports with PowerPivot

October 2016 Webinar: Dashboards And Dynamic Charts In Excel

This October's monthly webinar will be for Wednesday, 26 October 2016. I will be walking us through the concepts of Dashboard and building dynamic charts in Excel.

Dashboards are clear visuals of the insights in your reports in a consolidated and easy to understand way. Just think about your car's dashboard. In one glance you get to see the state of your car. And that is the way your visuals should be like if you are making reports on a system as complex as a car, having many intricate moving parts and lots of KPIs to track.

Maybe you are a sales analyst and you track over a dozen brands/products across dozens of regions/areas. Your final report is a labyrinth, even you sometimes get lost in it.

Maybe you make a fairly simple report but the lines of records are many and regular charts look so crowded/clumsy that you have given up including charts in the report.

Or maybe you want to make more engaging visuals, put in some element of interactivity. Make the data come alive. 

Then you shouldn't miss this webinar.
Date: Wednesday 26 October 2016
Time: 4:00pm UTC+1 
Venue: https://www.youtube.com/watch?v=MfnSWzwU5JY (YouTube live)

We run webinars monthly on how to improve your business data analysis skills. To be among the first informed monthly about the webinars and get timely reminders, please sign up here: Webinar Directory.

August Webinar: Demystifying Data Analysis, Business Analysis, Business Intelligence and Big Data. No more confusion.

image: kdnuggets.com

There is too much confusion as to who exactly data analysts vs business analysts vs BI analysts vs big data analysts are.

In this month's webinar, I will be helping you avoid those confusion and hopefully help you find which you really want to develop skills in.

I will be sharing my practical knowledge interacting across the boundaries of them all and the tools used by each.

The date is Thursday 11 August 2016 and time is 4:00pm to 5:00pm.

It will be a hangout webinar like the July one and the hangout webinar link is: https://plus.google.com/events/c9jkmfp6j2oa1vr5bspnrlgpk8c

Just make sure you click on it that day (Thursday at 4:00pm) to be part of the webinar. I will also be answering all your questions. So create it as an event in your phone or gmail or outlook calendar to get timely reminder for the event. 

You shouldn't miss it!

To always be in the loop of future webinar announcements, you can register at www.urbizedge.com (see screenshot below for where to register).

Once you register, you will always be first to be informed about our monthly webinars and sent the recording of previous webinars.