Understanding The Practical Differences Between Excel 2011 for Mac, Excel 2016 for Mac and Excel for Windows

In our training classes, whenever I hear a participant say he can't find a particular menu item exactly where I pointed out then I suspect he is using Mac. And I am usually correct.

If you use the Mac version of Microsoft office, a lot of things are not the same as with the Windows version. In today's post I am going to show you the practical differences you should be aware of between the two common editions of the Mac version and the Windows version of Excel.

First of all, the verdict. My recommendations. 

1. If you want to be a superb user of Excel for data analysis and master the new advanced tools in Excel, you will have to put aside the Mac versions of Excel. Either you get a Windows PC/laptop or create a Windows OS powered Virtual Machine on your Mac and install Excel on it. 
2. If you already use Excel on Mac and are okay with not being able to do PowerQuery/PowerPivot, then go for Excel 2016 Mac edition. It is better designed and has more features than the Excel 2011 Mac edition (the edition before the 2016 one as Mac has no Excel 2013 edition). Microsoft has re-added the missing Macro/VBA features people complained about when migrating from Excel 2011 Mac to Excel 2016 Mac, so no reason to stay with the Excel 2011 anymore.

Excel 2011 for Mac

When Microsoft did Excel 2010 for Windows, they also made Excel 2011 for Mac. Unfortunately, their capabilities are not equal. Microsoft tried to give them both the same base features but when you want to do more, like work with Macros/VBA, enable add-ins like Analysis Toolpak or Solver, work with external data sources or do more with your PivotTable then you'll see that the Mac version is somewhat limited compared to the Windows one.

One main thing to note if you are already familiar with the Windows version of Excel is that the File>>Options menu is Excel>>Preferences in the Mac versions.

And if you want to enable the Developer menu, you'll click on Ribbon (last item in the last row) in Excel Preferences window.

Lastly, the menu on the Excel 2011 for Mac can be very confusing. They are two menu layers that you'll find yourself cycling between to locate commands/features you want to use. And it is much worse if you are already used to where things are placed in the Windows version.

Excel 2016 for Mac

Microsoft must have heard the complaints about the Excel 2011 for mac and how much people wanted similar experience and UI to the Windows version. They really did well with the Excel 2016 for Mac one. The menu items are arranged in the same way as in the Windows version. There is still the two menu layers confusion but less confusing than in the Excel 2011. The look is also very cool and mirrors the Windows one.

The main issues are still the advanced tools. And now, with the advent of PowerQuery, PowerPivot, PowerView and many more advanced features in the Excel 2016 for Windows (Office 365 one, especially), the Mac version is looking a lot more like a watered down version. 

Also, just like the Excel 2011 for Mac, the File>>Options menu is accessible as Excel>>Preferences in the Excel 2016 for Mac version.

And those are the practical differences I find between the Mac versions and the Windows version. 

If you would like to read something more official from Microsoft, then head over to https://support.office.com/en-us/article/Compare-Excel-for-Mac-2011-with-Excel-2016-for-Mac-602a6c30-e6a6-47c5-9e0d-b16af397427a (for the comparison between the 2011 and 2016 editions of the Mac versions) and https://support.office.com/en-us/article/Compare-Excel-2016-for-Mac-with-Excel-2013-for-Windows-1b24f293-739c-4120-b884-893b6687e0d0 (for the comparison between Mac and Windows version of Excel).

What are your thoughts?

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.