All articles with 'modeling' Tag

We had to switch power providers soon, so I started reviewing the options. There are heaps of providers in New Zealand and each offer a ton of different plans. Some offer welcome bonus or credit worth up to $ 200. Other offer straight forward rates. Some others offer discount if you sign up for both electricity and gas with them. So how do you decide which one is better for you?

Using Excel of course.

The result is awesome. I ended up saving more than $1000 with a simple model. Puzzled? Curious? Check out this short but powerful video tut.

Imagine you run an office furniture company. You want to stop reordering two brands of furniture – Relaxer (a type of chair) and Boca Top (a type of table). You currently have 20,000 Relaxer chairs and 5,000 Boca Tops. These are valued at $200,000 and $100,000 respectively. When sold, they will yield $100,000 and $25,000 gross profit. You are hoping to sell them off in 2 or 3 years. You forecast that we can sell off these as per some yearly schedule.

You need to analyze this and prepare a cash flow model.

Let’s learn how to answer such open ended questions using various analysis techniques in Excel.

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)

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 45th session of Chandoo.org podcast, let’s get in to Monte Carlo simulations.

What is in this session?

In this podcast,

• Quick personal updates – 200km BRM and book delay
• History of Monte Carlo simulations
• Monte Carlo simulations – an example
• How to do simulations in Excel
  • Formulas
  • VBA
  • Data Tables
• Using data tables to run simulations – case study – estimating Pi value
• Things to keep in mind when setting up your simulation models
• Resources on Monte Carlo simulations in Excel
• Conclusions

Here is a tricky problem often faced by accountants and finance professionals: Let’s say you have 5 customers. Each of them need to pay you some money. Instead of paying the total amount in one go, they paid you in 30 small transactions. The total amount of these transactions matches how much they need to pay you. But you don’t know which customer paid which amounts. How would you reconcile the books?

If you match the transactions manually, it can take an eternity – after all there are more than 931 zillion combinations (5^30).

This is where solver can be handy. Solver can find optimal solution for problems like this before you finish your first cup of coffee.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 42nd session of Chandoo.org podcast, Let’s talk about money. We are going to learn about various concepts that are vital for doing financial analysis and building models.

What is in this session?

In this podcast,

• Quick announcement about Awesome August
• 5 key finance concepts
  • Time value of money
  • Compound interest
  • Risk free rate of return
  • Net Present Value – NPV
  • Internal Rate of Return – IRR
• Case study – Uber vs. Your car
• Conclusions

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 19th session of Chandoo.org podcast, lets talk about modeling best practices.

What is in this session?

I am very happy to interview my good friend, blogger, author, excel trainer & business-women – Danielle Stein Fairhurst for this session. I first met Danielle when I went to Sydney, Australia in April 2012. Our friendship & collaboration grew a lot in the last 2.5 years. She is a great speaker & trainer. This episode is loaded with her trademark style commentary, explanation & tips for better modeling. I hope you will enjoy it.

In this podcast, you will learn,

• Introduction to Danielle & her work
• 6 Tips for Best Practice Modeling
  • Write consistent formulas
  • Avoid hard-coding
  • Smart referencing
  • Ditch the bad habits
  • Document assumptions
  • Format & label things
• Resources for learning more

This article is written by Michael Hutchens from Best Practice Modelling.

In the first article on Modular Spreadsheet Development, we got a high level overview of Modular Spreadsheet Development principles. This article discusses the practical implementation of these principles in Excel.

From my experience using Modular Spreadsheet Development over the past decade, there are three increasingly-efficient methods of implementation in Microsoft Excel:

1. Manual implementation;
2. VBA automated implementation; and
3. Commercial add-in implementation.

This article provides a comprehensive overview of each of these methods and a summary of their advantages and disadvantages.

This article is written by Michael Hutchens from Best Practice Modelling.

This article provides a high level overview of Modular Spreadsheet Development principles. In next part the implementation of these concepts will be discussed.

Modular Spreadsheet Development – An awesome concept

I want to share a concept with you so awesome that once you understand it you may never use Excel the same way again.

This concept, called Modular Spreadsheet Development, makes it possible to build spreadsheets exponentially faster while reducing the risk of errors and making spreadsheets much easier to understand.

This concept is not completely new, but I’m writing this article because the spreadsheet modelling world would be a much better place if it was more commonly adopted.

In the first part on our Modeling Best Practices series, we learned 5 best practices to follow. This article shows how to automatically implement the best practices using macros.

Our guest author, Myles Arnott discusses what Best Practice Modeling is and offers 5 practical, easy to implement tips to get you started.

Best Practice Tip #1: Apply a modeling life cycle
Best Practice Tip #2: Give structure to your spreadsheets
Best Practice Tip #3: Make cells consistent based on their behavior
Best Practice Tip #4: Use similar formulas
Best Practice Tip #5: Build error checks in to your models

Read on…

In the second installment on Excel Spreadsheet Risk Management, Myles takes a look at How companies can manage spreadsheet risk?.

He tells us, how we can set a formal governance framework to reduce or mitigate spreadsheet risk. Go ahead and read. And please tell us how your company manages spreadsheet risk in comments.

Can I automate 500 Columns of Data Analysis without VBA?

Absolutely!

Excel super function – Data Table to the rescue.

After reading this post, you will be able to process data like a Data Analysis Super Hero !

Slicers are my new favorite feature in Excel. Introduced in Excel 2010, Slicers are like visual filters.

Now, we can use slicers creatively to make an interactive scenario manager in Excel, as you can see below. We will learn how to create this in Excel in today’s post.

You take an apartment on rent at $1000 per month and the owner puts an escalation clause saying 10% increment each 3 years. How do you model this in excel? In this tutorial we understand how escalations at certain frequency can be implemented using the mod function in excel. What is the mod() function Simply […]