Intermediate Spreadsheets With Microsoft Excel

  



Welcome

  1. Microsoft Excel Spreadsheet Templates
  2. Indeed Intermediate Spreadsheets With Microsoft Excel Answers
  3. Intermediate Spreadsheet Proficiency With Microsoft Excel Answers
  4. Free Excel Spreadsheet Templates
  5. Working With Microsoft Excel Spreadsheets
  6. Intermediate Spreadsheets With Microsoft Excel

Apr 19, 2021 An intermediate Excel test is used for jobs where candidates will need to work with Excel at a higher level. Along with the skills assessed on the basic test, tasks may include: Creating more sophisticated formulas such as IF and LOOKUP. Formatting cells – conditional formatting, text columns, adjusting page and print layouts. Best Certification Course: Excel Skills for Business Specialization (Coursera) An.

This web site supports Intermediate Microeconomics with Microsoft Excel, originally published by Cambridge University Press, 2009.
The updated Excel files on this site remain compatible with the first edition.


Second Edition (2020)

The second edition (2020) is freely available as an open educational resource.

MicroExcel.pdf (8MB) Click to download and open with a pdf reader.

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. How to Delete Blank Rows in Excel — The Right Way Written by co-founder Kasper Langmann, Microsoft Office Specialist. There's something inherently irritating about spreadsheets with blank rows. Excel sets out your data so nicely—and then its hard work is interrupted by blank rows. It seems unjust.

The second edition has additional content and updated screenshots and instructions for Excel 2019, but remains compatible with older versions of Excel.

About the Book

This book is based on the idea that there is a particular framework used by economists to interpret observed reality. This framework has been called the economic way of thinking, the economic approach, and the method of economics.

This book is different from the many other books that attempt to teach microeconomics in three ways:
• It explicitly applies the recipe of the economic approach in every example.
• It uses concrete examples via Microsoft Excel in every application, which enables the reader to manipulate live graphs and learn numerical methods of optimization.
• The majority of the content is in the Excel workbooks which the reader uses to create meaning.

You learn by doing, not by reading.

About the Author

Humberto Barreto is a Professor of Economics and Management at DePauw University. Born in Cuba, he earned his Ph.D. from the University of North Carolina at Chapel Hill. Professor Barreto has lectured around the world on teaching economics with computer-based methods, including Cuba, Brazil, Canada, Scotland, Spain, Poland, India, Burma, Japan, and Taiwan. He was a Fulbright Scholar in the Dominican Republic and has taught National Science Foundation Chautauqua short courses using simulation. He has received several research and teaching awards. He has written numerous articles and books on using Excel to teach economics (including introductory level material, micro, macro and econometrics). He offers an annual workshop for faculty on teaching economics. Visit Teaching Economics with Excel for more information.

Acknowledgments and Thanks

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.

Microsoft Excel Spreadsheet Templates

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:

  • 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.

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

Indeed Intermediate Spreadsheets With Microsoft Excel Answers

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

Microsoft

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.

Intermediate Spreadsheet Proficiency With Microsoft Excel Answers

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?

Free Excel Spreadsheet Templates

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.

Working With Microsoft Excel Spreadsheets

Relevant other skills: Programming

Intermediate Spreadsheets With Microsoft Excel

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.