• What is the maximum profits we can make?
• What is the best way to schedule employees in shifts?
• What the best combination of tasks we can finish in a given time?

You might have heard about Excel Solver tool while trying to find solutions to questions above. If you have never used Solver or have little idea about it, then this post and video are for you.

What is Excel Solver really?

Excel Solver can solve problems for you. That simple!

For a given problem, excel solver can run various permutations and combinations and find out best possible solution for you. It is like goal seek, but better & awesomer.

Excel Solver Tutorial:

It is tricky to explain what solver does in text. So I made a short video (13 min). In this video you can learn,

• What is solver really?
• How to use it – a simple example.
• Finding the next month with 5 Fridays, 5 Saturdays and 5 Sundays using Solver

Watch the Excel Solver Tutorial video:

Few Tips while using Solver:

• Define your problem first: In order to make the best use of solver, you need to define your problem very clearly and model it using Excel. This comes with practice.  Start by modeling sample problems you find in work / life and you will be able to master this art.
• Tweak Solver Settings: Mess with solver settings by clicking on “options” button.

Additional Resources on Excel Solver

• Excel Solver Introduction and examples from Microsoft
• Excel Solver Examples from Vertex42
• Linear Programming with Solver from Economics Network
• Excel Solver Tutorial & Examples [PDF] from Standford University

What is your experience with Solver?

First I must confess that I am still a partial solver virgin. I have used it during my MBA to solve some optimization problems. Then I never had the opportunity to use it while working. I still struggle to set-up models and find required solutions thru Solver. That said, I think solver is an excellent tool and very powerful.

What is your experience with solver like? Please share tips / ideas with us using comments.

Lets say you have data as shown below to left and you need to show zebra lines whenever the value changes (see right).

Your home work is simple. Just figure out how to write conditional formatting rules to add zebra lines.

The data set is here.

Go..

PS: Problem inspired from this post on Chandoo.org forums. Don’t cheat.
PPS: Here is some beautiful help on conditional formatting.

What our mouth-watering chart will look like when its done:

Ingredients:

Some data, Few check-boxes, IF formula and a dash of espresso

Instructions for preparation:

1. First get your data. Make sure its clean and arranged neatly, like below, in the range B4:E11.
   
2. Since our data has 3 series (sales, profits and number of customers), we will take 3 check boxes and place them somewhere on our worksheet.
   Insert check boxes from developer ribbon / forms tool bar (tip: show developer ribbon in excel 2007)
   
3. Now, we want the check boxes to tell whether to show or hide a particular series of data in the chart. So, link each check box to one cell, say C13, D13 and E13.
   
4. We will use IF formula to roast our data based on what the check boxes say. So, create a similar table and load it with IF formulas like this:
   =IF(C$13,C4,NA())
   
5. Finally, make a chart with the data in this new table you created.
6. Put everything together and neatly arrange with your favorite colors and labels.
7. Serve hot and see your boss drool.

Download the prepared chart:

You can download FREE dynamic chart template and serve it instantly.

More recipes on dynamic charts:

• Select & show one chart from many
• Make a chart that grows as you add data
• Dynamically group related events in a chart
• More Dynamic Charts

Do you use dynamic charts?

I like dynamic charts a lot. They provide a wealth of information in a compact form. I use them whenever possible, especially in dashboards and analytical outputs.

What about you? Do you use dynamic charts often? What techniques do you use when implementing dynamic charts? Share your experience and tips using comments.

That is, you have data like the numbers shown to right, with a surprising bump during Christmas time.

Now, how do you go about making a chart?

I can think of 4 options:

1. You make a chart as – is
2. Use log scale for vertical axis (axis option > check logarithmic scale)
3. Split the axis in to two: This is the most trickiest technique. I have used 2 charts and aligned them neatly. Thankfully Peltier Man comes to rescue. Read broken y-axis to know how to do this.
4. Let the bars reach sky: You can of course, set axis max to an arbitrary value, just so that all the small values are clearly displayed and let the higher values literally cut thru plot area.

Download these Examples:

I have put all these 4 chart examples in a workbook so you can see the underlying technique. Go ahead and download it here:

Excel Charting options when you large and small values [XLS]

What would you do?

I seldom deal with data that has this kind of outliers. But I am sure you get such data once in a while. How do you go about charting then? Share your ideas and implementations using comments.

More Charting Principles & Techniques.

Related discussion on Chandoo.org forums – Lots of small values but few extreme values – how to present it?

What is the most weird, unusual, out of the world thing you have used excel for?

I will go first.

• I have used excel to generate and clean a list of tags for this blog.
• I have used excel to keep track of which movies I have seen in the IMDb Top 250 list.
• I have used excel to simulate monopoly board game, just so I can play it better than my wife
• I wrote a small macro to send updates to twitter.
• I used excel to create stunning screen mockups and wowed my managers and clients.

While none of the above are batshit crazy, they are unusual and opened new avenues for exploration and learning for me.

What about you?

What unusual things you have done using excel? Share using comments. I am all ears.

Hi Channdoo,

I would like to express my gratitude for sharing with me using Excel to create dashboard !

I have spend 2 days over the weekend and learn as much as I could and came up with a dashboard for my company Sales and Marketing Meeting !..

Attached is the print screen…

Without you, this is not Possible !!!

Thanks so much !!! The more I learn, the more I know what I dont know !!!

Readers like Francis inspire me most. Despite doing all the work himself, he showed extreme kindness in emailing and making me happy.

Naturally, I replied to him and asked “if he can share original excel file with dummy data so rest of our readers can learn from what he produced.”

Francis agreed for this and shared a version of dashboard with me.

The Travel Site Dashboard Francis Made:

Here is a screenshot of the dashboard Francis made.

[view high res]

Video Review of the Dashboard in Action:

The image doesnt do justice to the awesome work by him. So I made a short video review of the dashboard (6 min). Watch it below:

[See it on YouTube]

Techniques used in this Dashboard:

Francis used a variety of techniques in making this dashboard. Some of the important ideas are,

• Using a bullet chart to show current performance vs. last year (tip: making bullet charts in excel)
• Showing a sortable KPI grid to provide on-demand analysis (tutorial: making KPI dashboards in excel)
• Showing key messages as text at the top (idea: use tweetboards instead of dashboards)
• Adding a month selection option at top (formulas: INDEX and OFFSET)
• Dynamic charts that are connected to drop-down boxes (reference: dynamic charts in excel)

How to Make Dashboards in Excel?

Here is our how to make dashboards in excel page. I am sure it will blow your mind. Go check it out.

Download the Dashboard

Click here to download the source file of this dashboard and play with it. [mirror]

Thank you Francis

Thank you so much for sharing your awesome work with all of us Francis. You inspire me and our readers to go that extra mile.

Send your tips / downloads for Reader Awesomeness Week:

You too can participate and share what you know by simply filling up online form. Be awesome. (details here)

This is Part 1 of 6 on Financial Modeling using Excel

In this and next 5 posts, we are going to learn how to build a financial model to do project evaluation using Excel. The 6 parts of this tutorial are,

1. Introduction to Financial Modeling
2. Building a layout for Project Evaluation Model – Best practices
3. Building Inputs and Assumptions Sheet
4. Building Projections for Project Evaluation
5. Modeling the Cash Flow Statement and Projections
6. Putting it all together – Final Project Evaluation Model
7. Join our Financial Modeling Classes

What is financial modeling?

Financial modeling is creating a complete program/ structure, which helps you in coming to a decision regarding investment in a project/ company. Now this could be on a simple piece of paper or in excel. The advantage with excel is that, even if you have calculation speed and accuracy like me (this is one place where I am like Einstien!), then also you would be able to come to the right conclusion!

Who does Financial Modeling?

Anybody dealing with any decision related to money (wish there was somebody who did not require that!). If you are involved in financial decision making/ planning related to large corporate, then you would definitely need financial modelling day in and day out. Financial modeling is a mandatory activity for investment bankers, bankers, project finance persons, equity research folks, PE & VCs.

What are the steps in building a financial model?

• Data collection – This is where the real front end banker works. She goes to the client, collects the data like revenue, growth, investments, need for money, etc.
• Back of the envelope calculations – Now most of the corporate guys are very hunky dory about their business. They usually think that their idea is one of a kind and is going to generate loads of mullah. Banker would sanitize their thoughts and try to figure out, if the business makes sense. Initially the thinking and analysis is very simple,
  • Does the corporate guy look sober to me?
  • How soon can I get my investment back?
  • Is the revenue that he is projecting seem greater than the market size?

  You usually need no industry knowledge for this. Just keep your eyes and ears open and put your thinking cap on!

• Structured approach to thinking – Once the basic numbers seem reasonable (they make business sense and seem to be true as well), you have to dig deeper! This is where you need a complete financial model. And the first real step to doing that is to think of a structure of analysis. Thankfully finance has some basic theories in place and you can rely on them to proceed:
  • Cash is the king – The more cash is generated (from the operations of the business) the better is the business
  • Money today is better than money tomorrow – technically this can be called Time value of money. But it does not matter!

  Practically these are the fundamental building blocks of analysis and you have to start thinking in these terms for the analysis

• Deciding on a Layout – Now when you start to put this plan in excel, the starting point is deciding on a layout for your financial model. Usually the following questions need to be answered:
  • How much information/ data would your model have? If its going to be large, then it might make sense to break the model in multiple sheets
  • What kind of assumptions would your model make? For better readability, we would try to keep assumptions in a different heading than the calculations and the final conclusion
  • Different fonts/ formatting for assumptions and other parts might enhance readability as well
  • Create logical modules for your model
    • Keep your P&L, Balance Sheet, cash flow statement, etc. separate
    • Even in P&L, keep the revenue generation separate from costs

One Advice for Aspiring Financial Modelers

Speed is the Key: Finally when you are dealing with tons of information, you can easily get bored. If you remember a bunch of keyboard shortcuts, then it would be very beneficial! Here are some resources to get you started:

• Comprehensive list of Excel Keyboard Shortcuts: Lists 97 shortcuts organized by functionality and usefulness.
• Favorite Shortcuts as voted by readers: Keyboard shortcuts shared by our readers.
• Using Mouse Effectively – Part 1 & Part 2: If you are going to use mouse, you better be productive at it.

An Example Financial Modeling Case

Now to make your life easy, we have done the first two parts for you! There is a case (In the attached PDF file) that lists a situation and some of the data you might need to organize your thoughts. The decision is simple, would you advice Mr. Samar to invest in design Y?

Download the case pdf from here [pdf].

Once you have read the case and analyzed the numbers try creating a model for evaluation. Remember to make the layout of your model in such a way that navigation becomes easier.

What Next?

In the next installment of this tutorial, learn how to create a layout / template for our project evaluation model. We also discuss about some of the best practices to follow when building models in excel.

Join our Financial Modeling Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

Please click here to learn more about the program & sign-up.

Share your Experience:

Do you build financial models? What is your experience like? What process do you follow and how do you plan the model? Do share your ideas and methods thru comments.

Added by Chandoo:

Thank you Paramdeep & Pristine:

Many thanks to Paramdeep and Pristine for making this happen. I am really enjoying this series and learning a lot of valuable tricks about financial modeling.

If you like this series, say thanks to Paramdeep. I am sure he can take any amount of appreciation without choking.

Join our Newsletter

if you are new here, consider joining my newsletter, because I can send you updates when new articles are posted (plus you get a cool e-book with 95 excel tips, free)