Tuesday, May 31, 2016

With Power BI, I easily monitor my website performance and see the important figures from any device I am on. Also, I am able to monitor my business finance -- track sales, invoices, expenses and instantly generate financial reports. And same for my email marketing campaigns. All in one central place. And you know the best part? The reports are automatically generated for me and updated daily. No need to have a staff make me reports or depend on my now less reliable memory to remind me to generate the report.And what if I tell you that all these are free and can be easily set-up by you. All you have to do is sign up for Power BI.

Sign up is very easy. Then log in afterwards. You will be directed to a page similar to the one below.

And immediately redirected to the Power BI dashboard. In your case, you will see Microsoft sample reports. I have connected mine to my Google Analytics to monitor my website performance, Mailchimp to check the performance of my email marketing and QuickBooks to see my business performance.

Google Analytics Automated Report

Mailchimp Automated Report

QuickBooks Automated Report

So how did I initiate the report generation? Very easy. I simply connect Power BI to my Google Analytics account, Mailchimp account and QuickBooks account. And that's all. Power BI will generate a comprehensive report that shows you the important numbers all by itself. And if you want, you can customize the reports or add more analysis.Just click on "Get Data" at the lower left corner to get started.

Choose "Services".

A list of all the services you can connect to will come up. Click "Get" on the ones you want the automatic report generated for.

Notice the detailed report of my business finances from QuickBooks

And that is all.And if you are a smartphone person, like most of us, you should install the Power BI app from your phone app store (available for Windows Phone, Android and iPhone). So you can always view your reports on your phone. And it automatically updates everyday. No need for you to recreate the report or refresh or update. It does that daily for you without your intervention. That way you always see the most recent report.

Tuesday, May 17, 2016

CHOOSE is one of the formulas dedicated to making your analysis life easier. You can easily do dynamic reports just by using CHOOSE.I have an illustration we can all easily understand. It is Sam's todo list for the day. A list of 10 tasks he has arranged in the order he intends to carry them out today.

In reality, you would want to use CHOOSE for something more complex. But the concept is the same and once you grasp how to use it here, you can use it anywhere -- even for a list of 100,000 entries in a different Excel file.Back to our illustration.Sam wants a simple way to see the progress he is making. He wants to type in a task number and see what it is about and if he has done it. And this where CHOOSE comes in very handy.The way CHOOSE works is you specify the position of an item you want to pick and then list our all the items. See below how Sam has used it to solve is problem.

CHOOSE checks for what number is in cell B3 and looks through the orderly list of Description field items, then returns the one that is in the position specified in cell B3. The one above should return "Call Customer XYZ" as that is what is in the position 1 in the Description field items.And below is a similar formula for the Remark.

Let's specify Task number 4.

Let's specify Task number 8.

Another interesting use of CHOOSE is in a Financial Model to select different projections scenarios.For an in-depth business relevant training on Excel and Business Data Analysis, you can see the details of our next special training session here.

Tuesday, May 10, 2016

Solver is an amazing tool. It's a decision analysis tool that lets you set your goal and set the conditions to adhere to while searching for the best way to achieve your goal.Let's see an example I have set up. Say we are a new company and have a budget for payroll which we want to maximize to the highest number of staff possible. Below are the details.

So we want Excel to calculate how many Senior Staff, how many Mid-level Staff, how many Junior Staff and how many Fresh Graduate we can employ with the annual budget of N105 million for payroll? And it should take into considerations the following constraints:

At least one of every staff level

Maximum of 3 Fresh Graduate

Each Senior Staff must have exactly 2 Mid-level Staff to manage

Mid-level Staff must be more than 3

Each Mid-level Staff must have at least 2 Junior Staff to manage

It's the kind of task you'll use Solver for.

Solver is in Data Menu.

If you don't have it in yours, follow the screenshots below to enable it. Goto Excel Options, Add-ins, Excel Add-ins and enable Solver.

Now you will be able to see under Data menu.

Launch it and set the goal (objective of maximum staff) and the conditions to adhere to.

See the screenshots below for how-to.

And that's all! See the result below.

Congrats! You just did what is referred to as linear programming. You can try include more conditions or use if for your business decision analysis.