Stacked Bar/Column Chart with Indicator Arrows – Advanced

Stacked Bar/Column Chart with Indicator Arrows – Advanced

Lets take last weeks Stacked Bar/Column Chart and add some high-performance steroids.

Continue »

Formula Forensics No. 041 – Convert a Roman Numeral to a Number

Formula Forensics No. 041 – Convert a Roman Numeral to a Number

Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.

Continue »

Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Last week, I asked you to share an analysis problem that you couldn’t solve in Excel. We got quite a few very interesting problems in comments and email. In this post, let me explain how to solve Sara’s copy shop problem using Excel.

What is Sara’s copy shop problem?
Thanks to Caroline who posted this problem.

Sara wants to open a copy shop. Each copier costs $5,000 per year to lease. The rent & other fixed costs per month are $300. There is a $0.02 variable cost per copy. Each copier can print up to 100,000 copies per year. She plans to charge $0.11 per copy from her customers. Sara estimates that the demand can be any of the 4 values – 500, 1000, 1500 or 2000 copies per day.

  1. Build a model to estimate profit per given number of copiers & demand values
  2. Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)
Continue »

Stacked Bar and Indicator Arrow Chart – Tutorial

Stacked Bar and Indicator Arrow Chart – Tutorial

Learn how to develop a Stacked Bar chart with Indicator Arrow in this Tutorial

Continue »

Tell me about an analysis problem that you couldn’t solve with Excel?

Time for a quick show & tell.

Tell me about an analysis problem that you couldn’t solve with Excel?

It can be because you didn’t know how to solve the problem or Excel isn’t the tool for it or any other reason.

Go ahead and speak up. Post your tricky analysis problems in the comments section.

Continue »

18 ways to turn analysis projects into a nightmare

18 ways to turn analysis projects into a nightmare

Every week, we read news about failed analysis projects. If you listen carefully, you can hear the grunts, screams and curses of thousands of analysts all over the world about their analysis nightmares.

At Chandoo.org, we talk a lot about best practices for data analytics. So today, let’s peek in to the dark side and understand the mistakes that can turn your analysis project into a nightmare.

There are 3 parts in any analysis project

To understand these worst practices in analysis world, first let’s break analysis projects in to 3 parts.

  • Requirements
  • Data Structure
  • Tools & Construction

Let’s deep dive in to each area of the analysis projects to see what can go wrong.

Continue »

Hourly Goals Chart with Conditional Formatting

Hourly Goals Chart with Conditional Formatting

A while back I developed a solution to a Chandoo.org Forum question, where the user wanted a 4 level doughnut chart where each doughnut was made up of 12 segments and each segment was to be colored based on a value within a range. If the values changed he wanted the chart to update, Conditional Formating like:
This post looks at how this was achieved.

Continue »