Instructor: Christopher MurphyTake your skills to the next level - learn how to do advanced formulas and functions, filter and sort data, and create charts.T. At the Excel preliminary plus, pre-juvenile plus, juvenile plus, intermediate plus, novice, junior and senior levels, the top six skaters per section will earn an invitation to compete in the Excel Series Final. Skaters at the excel beginner through intermediate levels receive an open invitation to compete in the final.
This Intermediate Microsoft Excel 2016 training class is meant for students who want to advance their skill set by learning to work with advanced formulas, lists, and illustrations. Students will also work with charts and advanced formatting including styles.
[The outline for this course can be viewed at this link: https://www.webucator.com/microsoft-training/course/intermediate-microsoft-excel-2016-training.cfm]
Audience profile
This course is intended for students who have little or no familiarity with Microsoft Excel 2016 or more experienced Excel users who want to learn the topics covered in this course in the 2016 interface.
We often get questions on the difference between Microsoft Excel intermediate skills and Microsoft Excel beginner skills. Many of our students who think they have beginner Microsoft Excel skills have intermediate level Microsoft Excel skills and vice versa. There is this ongoing debate on what constitutes intermediate level Excel skills and what constitutes beginner level Excel skills.
So we, at GraduateTutor.com, have decided to end this debate and clearly define what constitutes intermediate level Microsoft Excel skills and what constitutes beginner level Microsoft Excel skills.
TEST YOURSELVES
We have outlined this set of 10 Excel exercises that will test your Excel skills. If you can tackle 8 of these Microsoft Excel exercises, then you are firmly in the intermediate Excel skills camp. If you can tackle 3 or more, we think you are beyond beginner level. If you can tackle less than 3 of these exercises, you can rate yourself as a beginner in Microsoft Excel.
Microsoft Excel Intermediate Challenge 1
This Microsoft Excel Intermediate challenge is about importing data into Excel and working with text data. The challenge is to find and pull out data within a cell and to segregate and join text data. You should be able to do this in under 10 minutes if you have intermediate level Microsoft Excel skills.
Import the list of Governors from Wikipedia and answer the following questions:
Excel Intermediate Online
- List the governors alphabetically according to their first names.
- List the governors alphabetically according to their last names.
- List all names in UPPER case letters.
- Which of these governors has the longest & shortest names?
- List all names with the first alphabets of each word in upper case letters.
- How many days, months, years have the governors been in office?
- List which of these governors has had the longest & shortest tenure.
- What is the average tenure of Republican governors? Democratic governors?
- How many Democratic & Republican governors do we have?
- How many governors started their tenure in 2012 & 2013?
Microsoft Excel Intermediate Challenge 2
The IF function in Microsoft Excel is one of the most powerful functions in Microsoft Excel. There are however a variety of ways to use the IF function (and a number of substitutes too). This Microsoft Excel Intermediate Challenge is about modeling a specific scenario in Excel.
I have opted for a phone plan with the following features. Please model the computation of my monthly invoice in Microsoft Excel. Show the amount payable for each month in the yellow cells. You should be able to simply drag and drop the contents of the first cell and have the monthly invoice estimated in the other cells colored in yellow. Feel free to add more columns to the right and make assumptions should you need to.
- I pay $5/month to retain my phone number (my basic number fee).
- If I use the phone during a month (incoming or outgoing), I pay $10 in addition to my basic number fee (above).
- If my total minutes used does not exceed 500 minutes a month, my per minute fee for minutes below 500 minutes is $0.1 per minute.
- My lower rate minutes can be rolled over for 3 months.
- If my total minutes exceeds 500 minutes a month, my per minute fee for minutes above 500 minutes is $0.2 per minute.
- If I use the phone only for incoming calls in the month, I get a $20 credit.
- If I use the phone only for outgoing calls in the month, I get a $25 credit.
- My bill cannot be negative.
- Credits cannot be rolled over.
Microsoft Excel Intermediate Challenge 3
Here is a dice game you can play. Roll two dice at each throw.
- If the sum of the outcomes is 5 or 6, the game is lost.
- If the sum of the outcomes is 2 or 9, the game is won.
- If it is not either of the above, roll the dice again.
- If we continue for 3 moves without losing, the game is won.
Excel Intermediate Quiz
Please model this game in Microsoft Excel. Empty cells should be blank. Now you can play this game with or without dice.
Microsoft Excel Intermediate Challenge 4
Mark Twain said that you cannot escape death and taxes. So it would be useful to model taxes. Try modeling your taxes. Here is a simple corporate scenario if you don’t want to deal with the gloomy subject of your taxes today (note that I am using random numbers to compute revenues and Cost of Goods Sold (‘COGS’). You should have the taxes computed correctly in the yellow cells each time a new random number is generated.
Microsoft Excel Intermediate Challenge 5
The next Microsoft Excel Intermediate challenge is to compute the net proceeds from sale of an asset by a company. The computation of the net proceeds from sale of an asset by a company is something you will usually learn in your first accounting class. We have tried to explain it simply here.
For this challenge, only the inputs in yellow can be changed. Economic life can be any value that has a MACRS depreciation schedule (3, 5, 7, 10, 15, 20 years). You could also consider having an error message if the useful life is anything other than 3, 5, 7, 10, 15, 20 years. The key Microsoft Excel challenge here will be to automatically pick the MACRS depreciation schedule based on the useful life of the asset.
Microsoft Excel Intermediate Challenge 6
This Microsoft Excel Intermediate challenge is about modeling a mortgage cash flow. This is an exercise you will typically encounter in your first finance class. You are provided the price of the home, the down payment, the duration of the loan in years and the interest rate for the loan.
Please model the mortgage payments over the life of the loan, specifically showing the interest and principal amount of each payment over the life of the loan. Please round up the monthly interest payment to the nearest dollar. The inputs to the model are only in the yellow cells. Everything else should automatically get filled in based on the formula you place in the cells.
Microsoft Excel Intermediate Challenge 7
Pivot tables and Pivot charts are definitely one of the most handy tools you will encounter in the Microsoft Excel world for data analysis. Mastering Pivot tables and Pivot charts will save you hours over your 2 year MBA program and probably hundreds of hours over the rest of your career.
The data below is of tourist traffic to a holiday town in Napa Valley. Use the data below to tease out patterns with pivot tables. Patterns may include factors such as nationality, gender or age groups. Can your pivot tables and charts at least answer the following questions:
- Which country provides the highest and lowest revenue?
- Which country provides the highest and lowest AVERAGE revenue?
- Do men or women spend more?
- Which age group spends the most and the least?
- Which age group in different countries spends the most and the least?
- Is this holiday town more attractive to older men or older women?
Microsoft Excel Intermediate Challenge 8
Scenario analysis and what-if analysis are helpful in understanding various possible outcomes in a given situation. The chart below has sales data on 10 employees of a small firm. If the firm pays a bonus of 1% on sales above $500,000, 2% on sales above $750,000 and 3% on sales above $1,000,000, what would the bonus be this year? If the firm introduces no new products and you assume that the sales will stay the same next year, how high can the commission on sales above $1,000,000 be if you want to give out a total bonus of $100,000 to all employees combined? If you expect the sales to increase by 10% on average for each employee, how high can the commission on sales above $1,000,000 be if you want to give out a total bonus of $100,000 to all employees combined?
Microsoft Excel Intermediate Challenge 9
You are given the following data on unit sales of a handcrafted pen.
- What number of units must be sold to break even for the year (have no profits or losses)?
- The selling price per unit and material cost per unit are highly variable. What combinations of selling price per unit and material cost per unit are profitable for the firm? Please fill in the profit for the year in the yellow cells for various combinations of selling price per unit and material cost per unit.
- Please have Microsoft Excel fill in a green color for the various combinations of selling price per unit and material cost per unit that are profitable.
Microsoft Excel Intermediate Challenge 10
Supper Mix produces three varieties of energy products for athletes called ProMix, EnergyMix and VitaMix. The primary base products are almonds, peanuts and grapes. Each energy product must contain the minimum required calorie count. The maximum available quantities of almonds, peanuts and grapes are listed below and the minimum commitments for each product made to wholesalers are also provided. The calorie count for each pound of ingredient and its cost per lb is also provided.
Please provide the optimal mix of ProMix, EnergyMix and VitaMix that is to be made.
Congratulations on getting this far!
Please count how many of this set of 10 Excel challenge exercises you have been able to do successfully. Be generous to yourself. If you can tackle 8 of these Excel exercises completely, then you are firmly in the Microsoft Excel intermediate skills camp. If you can tackle 3 or more, we think you are beyond beginner level. If you can tackle less than 3 of these exercises, you can rate yourself as a beginner in Microsoft Excel.
Prepared for your dream job? Is it consulting, private equity or investment banking?
Different jobs or roles within each company may require different levels of Microsoft Excel skills. Job requirements list either intermediate Microsoft Excel skills or beginner Microsoft Excel skills as a requirement for the job. Candidates often do not know what constitutes intermediate level Microsoft Excel skills and what constitutes beginner level Microsoft Excel skills. Sometimes recruiters themselves do not know what constitutes intermediate level Excel skills and what constitutes beginner level Excel skills! We hope that this set of Microsoft Excel exercises will clarify what intermediate Microsoft Excel skills are and beginner Microsoft Excel skills are.
We believe that you need at least intermediate level Excel skills to start as an analyst or in a consulting, private equity or investment banking role (of course this is in addition to the industry/domain specific skills you will need). So if you are gunning for any of these high profile jobs, get cracking on building your Microsoft Excel skills. Browse through this list of Microsoft Excel resources to kick-start your Excel skills training. Of course, we will be happy to provide you private tutoring in Microsoft Excel. We believe this is the fastest way to learn Excel.
PS: What constitutes advanced Microsoft Excel skills?
What about advanced Microsoft Excel skills? What constitutes advanced Microsoft Excel skills? We believe that you will be in the camp of those with advanced Excel skills if you are comfortable working with Excel Macros and VBA, combining Excel with other tools, understanding how to speed up Microsoft Excel models, protecting Excel worksheets, etc.
Relevant other skills: Programming
In addition to learning Microsoft Excel, also consider learning some programming languages. A large part of the use of Microsoft Excel was data analysis of different types and levels. A the volume of data increases, more of the data analysis is being done on programming languages such as R and Python. So do consider adding some programming skills if you believe your career could involve data analysis in the future.