Wednesday, November 15, 2017

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 

Tuesday, November 7, 2017

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.

Monday, September 4, 2017

Using Fuzzy Lookup In Excel To Match Inconsistently Spelt Items, like People's Names

VLOOKUP won't help you if you need to match two list of names where the first name -- last name positions are often swapped and middle name initial is present in one but absent in the other.

What then can you do?

Use Microsoft's Fuzzy Lookup add-in. You can download it here: Microsoft Research's Fuzzy Lookup

When you are done installing it, you will see it show as a new menu tab in your Excel.


If it's not showing up in yours, you might need to toggle it off and on in the COM Add-in section of Excel Options.





So how do you use it?

Copy the two records side by side in one sheet in Excel.



 Then format each record set as a Table. And you 60% done. 




Just launch the Fuzzy Lookup tool and set the fields you want to match. Set the Similarity Threshold. 


Select the cell to put the output results and click on Go.



And that's all! You'll see it work its magic, saving hours you would have spent doing manual matching.


Monday, August 28, 2017

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!

Monday, August 21, 2017

Power BI #2: Getting To Know The Power BI Desktop

The Power BI Desktop is the main tool you would be using in creating Power BI reports. You can freely download it here from Microsoft




Once you are done installing it. You get a startup screen like the one below.


There are two major parts of Power BI Desktop you will need to get very familiar with:

1. The Designer part.



2. The Query Editor part.


Let's start first with the Designer part. It is the window you are presented with upon launching Power BI. It has four main sections.


  1. The menu section comprising File (for Open, Save, Options/Preference settings etc.), Home, View and Modelling.
  2. The Report, Data and Relationship section
  3. Page section (like Sheets in Excel), and
  4. The context based section that shows Fields and Visualization when you are in Reports, Fields only when you are in Data and nothing when you are in Relationship.
Now to the Query Editor. It is the exact equivalent of PowerQuery (now merged into Get & Transform Data in Excel 2016). Its main function is to help you wrangle data before they are fully loaded/downloaded into the Power BI. So instead of downloading a 16 GB database table and then specifying which fields/rows to keep and which to discard, you can do the specifying using just a preview of the data and only import just the very data you want/need. This is a life and time saver. And space/memory saver too. Then you can do some very interesting and complex stuff you can't do from the Designer part -- like merge or append data from different sources, unpivot and a few other things I find myself doing repeatedly on client/commercial projects.

You get to the Query Editor from the Home menu in the Designer part.


And it has four sections too.

  1. The menu section
  2. The Queries section
  3. The Data section, and
  4. The Query Settings section (which only shows up when you have/selected a Query)
And that is it for this second tutorial post in the new beginner to expert series I am doing on Power BI. Cheers!

Monday, August 14, 2017

Video Recording: July 2017 Webinar On Getting Comfortable With Microsoft Excel VBA

You can watch the video recording on YouTube at https://www.youtube.com/watch?v=pLOfPuwcUKI




For July webinar, we explored the powerful Microsoft Excel VBA. 

Too many people wrongly think that VBA is hard or for just for core programmers. 

I will be helping you  see the benefits of learning Excel VBA and then help you make your first steps into becoming proficient at Microsoft Excel VBA.

In the webinar, I showed:
1. How to record Excel VBA macros
2. How to edit recorded Excel VBA macros
3. How to carry out simple tasks with Excel VBA
4. How to work with Userforms
5. And introducing you to loops (If loop, For loop, For Each loop, Do loop, Do While loop and Do Until loop), they form the backbone of Excel VBA coding

Ultimately, I don't expect you to become proficient by just watching in this webinar. The goal is to get you started and comfortable with exploring Excel VBA. To take away any fear you have for it and replace those fears with a curious admiration.


If you have not, do join our webinar directory list to be kept aware of every future (monthly) webinars at http://eepurl.com/bKwoaT 

Cheers!

Monday, August 7, 2017

Lagos, Port Harcourt and Abuja: Executive Dashboards, Business Data Analysis and In-depth Excel Training



The business world is now data driven and every business professional must now be fluent in the language of data. William E. Deming had the most accurate way of portraying this new age: “In God we trust; all others must bring data.” Without data skills you will have a tough time influencing in the business world. You must learn to manipulate data, make compelling data stories and leverage data for insights.

UrBizEdge Limited, Nigeria’s leading business data analysis company is putting together this special training for proactive business professionals. This training is aimed at making you extremely good in Microsoft Excel, dashboard making, data presentation and business data analysis; teaching you with live business scenarios from our experience consulting for multinationals within and outside Nigeria. It's intended for Sales Managers, Financial Analysts, Business Analysts, Data Analysts, MIS Analysts, HR Executives and power Excel users.

You will get our high value materials, tea break + lunch, our branded DVD with over 20 training videos and practice files, training notepad with pen, a comprehensive training reference material, a training certificate from us (
UrBizEdge Limited, a registered Microsoft Partner), after training support and free refresher classes.

The training will be facilitated by a Microsoft recognized Excel Expert with Microsoft Office Specialist Excel 2013  certification and the only Microsoft Excel Most Valuable Professional (MVP) in Africa (there are just about 125 in the whole world and it is the highest level of recognition from Microsoft to an industry expert). We have had participants of our training from Citi Bank, Dalberg, SaveTheChildren, Mobil, Chevron, Vodacom, Nestle, Guinness Nigeria, Nigerian Breweries, Delta Afrik, LATC Marine, Broll, Habanera (JTI), SABMiller, IBM, Airtel, Diamond Bank, ECOWAS, Biofem Pharmaceuticals, Ministry of Finance, FMDQ, Schlumberger, Palladium Group, Nokia Siemens Networks and DDB.

To register reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com  or Opeyemi on 09020043560 and opeyemi@urbizedge.com to register. There is a class size limit.
Lagos Date: Friday  25th August 2017 to Saturday  26th August 2017
Lagos Venue: Kristina Jade Learning Center, 70b Olorunlogbon street, after Banex Hotel, Anthony Village, Lagos. 

Port Harcourt Date: Friday 22nd Sepetember 2017 to Saturday 23rd September 2017 for Port Harcourt.
Port Harcourt Venue: Aldgate Hotel, 20B King Perekule street, GRA Phase 2, Port Harcourt, Rivers state.

Abuja Date: Friday 27th October 2017 to Saturday 28th October 2017 for Abuja.
Abuja Venue: Hotel Rosebud, 33 Port Harcourt crescent, off Gimbiya street, Garki 11, Abuja.

The training outline is:

1) Data Manipulation in Excel 
We’ll show you, from a consultant expertise level, how to manipulate data in Excel. From data preparation/cleaning to data formatting the professional way. We’ll cover both the science and the art of data manipulation in Excel, and share very useful keyboard shortcuts and expert tricks that will speed up your productivity in Excel.

2) Data Visualization and Presentation in Excel
A picture is worth a thousand words. And in the business world, it is often the only way to not bore your audience and pass the valuable message you’ve uncovered in your data analysis. We will teach you the foundations of data visualization – from the different types of charts to when to use each of them. Then we will work through business samples to learn the art part of doing data visualization right. You will learn the rules of business data reporting via charts and gain from our industry wealth of consulting for businesses in this vital area.

3) Large Data Analysis: Pivot Table, Pivot Chart and PowerPivot
You should never say you know Excel if you don’t know how to use Pivot Table. It is that important. It is Excel’s premium tool for analysing large data – sales data, inventory data, HR data, transaction data and most business operations data. We are going to cover from the basics to the very advanced use of Pivot Tables. We will show you how to create dynamic reports with Pivot Table; how to overcome some of its layout issues; how to turn off the distracting controls in your final report; how to create calculated fields; how to create Pivot Charts; and the special tricks only a full-time Excel consultant can show you that will turbo-charge you Pivot Table skills. Then we’ll show you how we analyse data of up to (and even above) 30 million rows in Excel. Yes, in Excel. Heard of PowerPivot?

4) Business Data Analysis 
In this section, we teach you the secrets that separate the analysis experts from the people with head/academic knowledge Excel. It is one thing to know the different tools in Excel and it is another completely different thing to know how to expertly mix them together to creatively deliver value at high speed. As full-time Excel chef, we will show you the secret ingredients that make companies consult us even when they have Excel super users in their organization.

5) Executive Dashboards and Reporting
This is the level self-knowledge will not get you to. How do you create an uncrowded insightful visualization for a report with 1000s of rows? Then how about for your sales analysis report of many products and regions? How do you show the different interactions in your data? In short, how do you bring your data to life and take it from a boring confusing mass of text to an interactive exciting visualization? You have to come to get the answers.

6) Excel to PowerPoint
Management level reports are best presented in PowerPoint. When you’ve got a delicate story to tell, you have to guide your audience through a one idea/insight a slide PowerPoint. We won’t teach you how to design slides but we will teach you how to make your chart slides speak very loud. You will also learn the tricks of linking your PowerPoint charts to Excel. It will help cut down the hours you spend on weekly/monthly PowerPoint reports. We will also show you how to embed Excel files in your PowerPoint slide. No more sending separate Excel files when you can embed them right on the very slide you reference their data.

7) Excel VBA
Forget about all you’ve heard about Macros or VBA. Let’s show you how easy and exciting it is to break into the Excel VBA world.

Reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com or Opeyemi on 09020043560 and opeyemi@urbizedge.com to register. There is a class size limit.


For a taste of our high quality content, view our free tutorial videos at www.urbizedge.com/tutorials

What's New In Microsoft Office 365 Excel

Office 365 is updated monthly, so no more waiting three years for new significant features. Before now, you buy Office 2007 or Office 2010 or Office 2013 and then wait for the next major version for any significant feature change. You mostly get bug fixes and minor feature updates till that next major version that often takes three years. Now, with Office 365, you get a monthly stream of significant feature updates/additions.

And in this post I will be sharing with you some of the new features that have been added to Excel, in particular, this year. You can check out the exhaustive list of update details here: https://support.office.com/en-us/article/What-s-new-in-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426

1. Realtime Collaboration
In 2014, after I was taken for Nigeria Leadership Initiative Associate Fellowship and we were done with the two weeks seminar, we committed to doing a project and required some level of collaboration on planning the project phases and execution. We were all scattered across the world, many of us in Nigeria but the ones outside Nigeria seldom stay in one place/country. We used Skype and other tools that helped us work together in realtime. One of the tools was Google Docs. All of us could type into the same document at once and see what the other person did instantly.

Good news, you now have that in Excel. All you need do is save the file on OneDrive, share the file with your co-collaborators and right from Excel (not just the online Excel version), you can all work together on the file in realtime. There is an autosave that syncs yout work/changes instantly for others to see.



2. Easy access to Superscript and Subscript

For those in the technical or engineering line, no more long trips to putting a superscript and subscript in your Excel. Now you can just pin them to the Quick Access Toolbar and use in one click.





3. 3D Models

You can now work with 3D models in Excel. Really cool! Just visit the Insert menu.


4. Icons to make Infographics

Again, you'll find them under Insert menu. Now you can create your own infographics from scratch in Excel using representative vector images.




5. Customise the Default Settings of PivotTable

So you have a favourite way you like your PivotTable to look -- color theme, remove subtotal, tabular form instead of the terrible looking compact form etc. Now you can permanently set your PivotTable to look that way. Just follow my screenshot steps below -- File >> Options >> Data >> Edit Default Layout




Just set your own defaults.


Those are just ones I can make out time to share for now. I have seen lots of new stuffs in the Data menu, especially with the Get & Transform section of it. Same with the Insert menu. And there is a new standalone Add-ins menu.





Excel is becoming more exciting month-on-month!

Wednesday, July 12, 2017

How To Create A Dynamic List In Excel

Think about this: You have a list of products, the list grows with time, and you want your Excel chart of the products versus sales to automatically capture any additional products you add. Or maybe it is a daily sales report, and you want the chart to automatically expand to show the newly added dates.

Well, what you need to create is a dynamic list (proper term is range, but let's go one with the easy fathom "list"). 

It turns this


To this (without you re-making the chart or even touching the chart at all)


And can add life sweetening spice to your Data Validation lists.



Just think of all the magic that would do in some of your reports.

So how do you create these dynamic lists?

Easy. There are two popular ways to create dynamic lists in Excel. One is to use INDEX and COUNTA. The other is to use OFFSET and COUNTA.

Technically, you should choose the INDEX + COUNTA one over OFFSET + COUNTA one as it has better performance. Again, remember the keyword -- technically. Practically, the one you find easier to master is better.

INDEX + COUNTA

For the chart one, I used the INDEX + COUNTA one. 


For the list field I created a named range:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

At the core, it is
=A2:INDEX(A:A,COUNTA(A:A))

What the index part does is to return the cell reference of the last row cell in column A that is populated. 

INDEX(A:A represents a list/array of all the cells in column A while COUNTA(A:A) part instructs Excel to locate the cell in the row position equal to the count of all filled cells in column A.






Now you get how it works.


OFFSET + COUNTA

For the Data Validation List, I used OFFSET + COUNTA


The named range is
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Again, at its core the formula is
=OFFSET(A2,0,0,COUNTA(A:A)-1,1) 

Easy to understand.


We take A2 as the start or reference point, go zero rows down and zero columns right, then expand the rows (height) downwards by the count of non-empty rows in column A minus one to avoid counting cell A1 (which is used for the field header), then take one column wide (sticking to column A).

The result is



Finally

For the chart, I simply replaced the Legend Entries and Axis Label with the dynamic named range.






And that does the magic!
For the Data Validation List, I simply put =months as the source. (months is the named range I created with the OFFSET + COUNTA formula)



And that's all!

Now you should be a dynamic list guru :)