Productivity Tips, MS Excel and Inspiration

On Saturday, I did a presentation for my university alumni group on exploring additional income source in this period of job uncertainties and salary cuts.

These are the slides: (downloadable via this link):

If there's any aspect you are trying out and need some extra guidance, as much as I can, I'll try to help. Good luck to everyone (including me).

****

This guest post is by one of the very amazing people this blog has put me in contact with, VV. In 2015 when I had issue an with GTBank and was on the verge of losing out on a big project for Vodacom because GTBank wasn't going to give me a bank reference letter, VV helped out. I know VV is tough and will make lemonade out this lemon VV's been handed.

****

On April 21, 2020, the year I turn 40, I got laid off from my job of 14+ years. We had been working from home since mid-March, so I was informed via skype call. As soon as I joined the online meeting to which my manager had invited me, I knew it was a lay off meeting. There was a third person on the call – an HR representative. I tried my best not to become emotional. I listened as my manager told me that it was my last day at work. I asked questions regarding my pay off benefits and the terms. While still in the layoff meeting, I informed my spouse, my siblings, my parents and close friends in a very matter of fact way. I did not want anyone to be too concerned.

I worked in the oil service industry. We received a double blow in 2020 – Covid-19 and the Russia Saudi oil battle, so it was not a shock to be getting laid off. Oil service companies rely completely on the oil companies for their survival. If oil companies do not invest, oil service companies do not have projects to execute and the first line of action to save the bottom line is to lay off employees. I was also unfortunate to be working outside my home country in a not so critical position. I was well prepared financially for this scenario; however, I was still surprised and sad in a way. I was sad because I did not want my story to be “I got laid off”, I wanted it to be “I quit my job”. I was surprised because with Covid-19, it was a bit unfair to be laid off in a country that is not mine knowing that I would not be able to return home immediately and would have to continue spending without income. In the end, I was mostly relieved. I hated the role and I did not care too much for most of my colleagues. My colleagues were great people, hardworking and talented. I just did not connect with most of them and was not interested in office politics.

Financially, I will be OK. I took my own advice and I always saved at least 50% of my earnings. I put the maximum allowable in pension contributions, I tried my best to be frugal. I was not always perfect – I did splurge on international travel and some other fancy things, but my savings rate was always around 50% or more. I never felt I was depriving myself at any point and I also put aside funds for charitable giving. Luckily, I also have a partner who works, and we can live a comfortable middle-class life on his salary.

I am not certain what to do with the rest of my life now. I have always been in school or working for an employer. I appreciate any advice you might have.

Oh 2020! – what a year you have been. I had so many plans – trip to a new country to celebrate turning 40, important medical procedures, start of a new life in a new country, retire on my own terms but none of this worked out. I hope things get better for me, for everyone and very quickly too. Overall, I am grateful, for a wonderful family, for great friends, for good health and for the opportunity to have had a job that allowed me to achieve financial independence.

I managed to fit all my message to you today in that title.

It is becoming just as important to ask if you are staying strong as it is to ask if you are staying safe.

These last couple of weeks have been nothing I have seen. For the first time in my life, sticking my head in the sand like an ostrich made more sense than "hoping for the best and planning for the worst". The worst involved a combination of watching analysts, politicians and health experts say we are doomed in a way that shuts down all positive and creative thinking.

I have stopped watching news stations.

How have you been coping with these unusual happenings?

I pray for supernatural strength for those with children and very elderly people living with them. The stress and worries must be energy draining.

I pray for those who have close ones (friends or family) who are infected, may God heal their friends/family_member and grant everyone peace of mind.

If you need some external push to regain your mental/physical health, do reach out. I'm not very reliable but I can put in an extra push to connecting you with help. I don't think my Excel and computer skills can do much good. If the help you'll prefer is a financial one, I am in a similar boat.

Just last week, I had to have a salary related meeting with my team. It resulted in pausing health insurance, pension and monthly gift allowance. In addition, I had to pause my own salary so that the little that comes in can (hopefully) cover the others' salaries.

I make some money from a couple of online stuff I have running. Not a lot but enough, with my wife's help, to cover our downsized monthly expenses.

Now, I appreciate what it means to have a diversified income stream.

The consulting jobs I used to turn down in other to make more time for training services are now what I am looking for. The online training I never saw as more than some form of marketing to get people to come for the better priced class training is now the only training we can provide. And we have had to remake our online training platform to be as full fledged and valuable as our class training.

These are very unusual times.

May we all come out better and with no scars/loss.

Make the most of this lockdown and work-from-home situation, even get your HR to see this as a way of achieving their staff learning and development goals with reduced budget and within the lockdown restriction.

Register at https://class.urbizedge.com/p/business-data-analysis-with-microsoft-excel and other courses at https://class.urbizedge.com

Microsoft Excel is the world's most used and versatile business analysis, reporting and strategy software. Having a deep practical knowledge of Excel will turn you almost superhuman at work and increase your productivity. You will be seen as a very efficient, highly competent and indispensable partner in the organization's progress. And, hopefully, it will lead to a much greater career role and opportunity for you.

This training is going to focus on making you highly proficient in the use of Excel for business data analysis, dashboard creation and reporting the professional way. And most of this would be achieved through lots of samples that will be similar to what you'll need at work.

It covers the following topics:

Data Manipulation in Excel

How Excel handles different data types
Data consistency, starting with the end in view
Building Datasheets that can easily scale
Sorting

Cascaded sorting
Sorting across rows (left to right sorting, not the usual up to down sorting)
Sorting and Conditional Formatting to identify trends

Filtering
Data cleaning

Removing duplicates
Text-to-column
Grouping
Data Validation
Conditional Formatting

Data formatting

Using Tables (and when to convert to tables)
Formatting for printing
Formatting for email
Data Review and formatting for 3rd party use

Named Ranges

Charts

Chart types

Line chart and when to use it
Column chart and when to use it
Bar chart and when to use it
Pie chart and it’s dangers

Combining charts; when and how.
Dynamic Charts,
using filter.
Best practices when making charts

Pivot Table, Pivot Chart and PowerPivot

Pivot Table

Default Pivot Table
Tabular Pivot Table
Pivot Table Filtering
Making a very dynamic regular table from Pivot Table
Calculations and Formula use with Pivot Table
Advanced Pivot Table tricks

Pivot Chart

Pivot Chart and its limitations
Dynamic Pivot Charts

PowerPivot (for Excel 2010, 2013 and 2016 only)
Power BI for dynamic dashboard and analysing millions of rows of data

Business Data Analysis

Linking sheets
Duplicating sheets (better than copy and paste)
Inserting sheets, labeling and coloring the professional way
Freezing Panes and splitting windows
Conditional formatting

To identify patterns
Using formulae
To make extremely intelligent reports

Lookup functions

VLOOKUP
HLOOKUP
Looking up the last data or pattern in a particular row or column
Overcoming the limitations of VLOOKUP and HLOOKUP using index and match functions

Power Functions

IF, IFERROR, AND, OR,ISBLANK, and others in the same family
TEXT manipulative functions to make a completely automated Dashboard
COUNTIFS, SUMIFS, AVERAGEIFS and others, to make dynamic summary tables
MATCH and INDEX to do the impossible

Other Functions

Math Functions
Text Functions
Logical
Others

Formula Auditing
Goal Seek, Scenario Manager and Solver
Excel Web Query
Most useful Excel keyboard shortcuts

Executive Dashboards and Reporting

Best Practices
Executive Dashboards

Executive Dashboards
Dynamic Reports
Determining the KPIs and tracking them
Strategic Insights & Analysis

Data Visualization
Having the audience/recipient in mind
E-mails and Excel reports

Excel to PowerPoint and Word

Linking PowerPoint/Word Charts to Excel
Embedding Excel sheets in PowerPoint/Word
Making a Powerful PowerPoint Presentation

Excel VBA

Recording Excel macros
Introduction to editing Macro codes

The course is run by UrBizEdge Limited

UrBizEdge is a registered Microsoft Excel consulting, financial modelling, business intelligence, data analysis and enterprise solutions firm. We specialise in helping companies and high value professionals be on top of their business data. We focus exclusively on the world’s most used and most flexible business intelligence tools. We have a 5x Microsoft Excel Most Valuable Professional (MVP) and we are an approved training provider for the AFM (Advanced Financial Modeler) certification by the global body, Financial Modeling Institute. Our team are the developers of the only Nigerian financial markets analysis tool on Microsoft office store, the developers of Nigerian stocks analysis dashboard and the brains behind the Nigeria Market Data platform

Access our online comprehensive training classes at specially discounted price with lifetime access, downloadable classes and future updates :

You can always pick between two options for any of our online courses:

Specially Discounted with lifetime access, downloadable classes and future updates
Specially Discounted with lifetime access, downloadable classes and future updates + 4 hours Live Session with instructor

Make the most of this lockdown and work-from-home situation, even get your HR to see this as a way of achieving their staff learning and development goals with reduced budget and within the lockdown restriction.

Register at https://class.urbizedge.com/p/power-bi-and-dashboards-for-the-busy-professional and other courses at https://class.urbizedge.com

This course is a fully hands-on one to take you from just having an idea of what Power BI does to being able to create insights-rich and interactive reports/dashboards in Power BI.

We cover the following aspects:

Get Data (bringing in your data for analysis)

Data Sources in Power BI Desktop
Connect to web data and web apps
Import Excel Workbooks
Connect to enterprise Database

Data Transformation, Power Query and DAX

Get Data
Model section of the report builder (Power BI Desktop)
Creating Relationships
Cardinality
Direction
Data section of the report builder (Power BI Desktop)
Data Type
Data Format
DAX
New Column
New Measure
New Table
Must know DAX formulas
DAX formula reference guide
Row level Security to control report access using roles (dept, function, branch, cost centre etc)
Report section of the report builder (Power BI Desktop)
Report creation
Native Visuals
Custom Visuals
Menu Tools for professional looking reports
Edit Interactions
Report creation from scratch
Power Query
Transforming and Combining Data before loading to Model
Common Power Query tasks
Unpivot
Pivot
Split Column
Append Tables
Merge Tables
Remove rows
Use first row as header
Merge Columns
Add Columns
Group By
Extract
Fill
Transpose
M Code reference resource

Report Publishing, Dashboards and Power BI Service

Understanding the concept of Workspaces
Publishing report (from Power BI desktop to Power BI service for consumption)
Controlling access to aspects of the report by role (region, department or level)
Published Report options
Exporting to PowerPoint or PDF
Embedding in SharePoint
Embedding in a website
Making it available via a web link
Sharing with specific users
Editing the report – adding new visuals, changing visuals used and creating new report pages
Adding comments and tagging users
Scheduling the report to send our daily or weekly to specific people
Creating a Dashboard
Can be created from one or more reports to show management useful analysis
Can incorporate data and visuals from other people’s dashboards or reports shared with you
Set Alerts to be instantly notified when a KPI is reached or an SLA is breached
Add actionable comments
Schedule the dashboard to be sent out daily or weekly to specific people
Share the dashboard with final users
Best practices
Setting automatic/scheduled refresh so new data is ingested automatically
Data Gateway configuration
The type of data sources that require data gateway

AI in Power BI and Settings

Q&A for autogenerating charts and tables in answer to questions
Organization wide settings
Account wide settings
Workspace settings
Dashboard settings
Report settings
Datasets settings

This is the course to solidify your Power BI proficiency and is run by UrBizEdge Limited

Access our online comprehensive training classes at specially discounted price with lifetime access, downloadable classes and future updates :

You can always pick between two options for any of our online courses:

Specially Discounted with lifetime access, downloadable classes and future updates
Specially Discounted with lifetime access, downloadable classes and future updates + 4 hours Live Session with instructor

Have you ever been faced with a situation where duplicates keep showing up in your compiled data and you are thinking of a way of restricting your users from inputting duplicates entries in your records thereby saving yourself the time of cleaning them up manually?

We will configure a custom data validation rule to allow only unique entries using the classic COUNTIF formula to identify duplicates.

Let’s say we are in charge of helping the Federal Government compile the list of Nigerians to get the Covid-19 cash support. We definitely do not want duplicate BVNs and duplicate Account Numbers.

The formula we put in is =COUNTIF($C$3:$C$32,C3)<=1

The custom validation criteria allows us to use logical formula to specify what is accepted and what should be rejected. Any entry on which the formula evaluates to TRUE is accepted and when it evaluates to FALSE, that entry is rejected.

We also put a nice Error Alert

If anyone tries to smuggle himself or a family member in twice, the sheet rejects his duplicate entry

And that’s how Excel helps us with a nationally important issue.

Don't forget to check out our premium online courses: https://class.urbizedge.com

Make the most of this lockdown and work-from-home situation, even get your HR to see this as a way of achieving their staff learning and development goals with reduced budget and within the lockdown restriction.

Register at https://class.urbizedge.com/p/financial-modelling-financial-planning-and-dcf-valuation and other courses at https://class.urbizedge.com

This is a fully hands-on course that will take you from a blank Excel file to creating a robust, dynamic and fully automated financial model that can be used for a startup or an existing company.

It covers the following sections:

Financial Model template creation and Excel tips
Fleshing out a dedicated Model Input sheet to aggregate all the client's plans
Creating scenarios -- Optimistic, Conservative, Pessimistic -- that will flow dynamically into the entire financial statements forecast
Creating the Revenue Schedule
Creating the Cost of Sales Schedule
Creating the Working Capital Schedule
Creating the Depreciation Schedule
Creating the Debt Schedule
Creating the Equity Schedule
Creating the Tax Schedule
Creating the Income Statement dynamically
Creating the Balance Sheet dynamically
Creating the Cashflow Sheet dynamically
Carrying out the DCF Valuation
Computing the Financial Ratios

The course is run by UrBizEdge Limited

Access our online comprehensive training classes at specially discounted price with lifetime access, downloadable classes and future updates :

You can always pick between two options for any of our online courses:

Specially Discounted with lifetime access, downloadable classes and future updates
Specially Discounted with lifetime access, downloadable classes and future updates + 4 hours Live Session with instructor

You can register for free between today and Thursday 9th April 2020 for our newly published Financial Modelling, Financial Planning and DCF Valuation online course by using the following coupon link: https://www.udemy.com/course/financial-modelling-financial-planning-and-dcf-valuation/?couponCode=SPECIALOFFER

This is a fully hands-on course that will take you from a blank Excel file to creating a robust, dynamic and fully automated financial model that can be used for a startup or an existing company. It also prepares you for the Advanced Financial Modeler (AFM) certification.

It covers the following sections:

Financial Model template creation and Excel tips
Fleshing out a dedicated Model Input sheet to aggregate all the client's plans
Creating scenarios -- Optimistic, Conservative, Pessimistic -- that will flow dynamically into the entire financial statements forecast
Creating the Revenue Schedule
Creating the Cost of Sales Schedule
Creating the Working Capital Schedule
Creating the Depreciation Schedule
Creating the Debt Schedule
Creating the Equity Schedule
Creating the Tax Schedule
Creating the Income Statement dynamically
Creating the Balance Sheet dynamically
Creating the Cashflow Sheet dynamically
Carrying out the DCF Valuation
Computing the Financial Ratios