All articles with 'data tables' Tag

Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,

• Introduction to IRR & XIRR functions
• Calculate rate of return from a set of cash-flows with XIRR
• Simulating purchase or terminal value changes with data tables
• Apply conditional formatting to visualize the outputs
• Common issues and challenges faced when using XIRR

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

One of the most dreaded courses during my under-graduation is Probability, Statistics & Queuing Theory. We called it PSQT. I struggled to understand the significance and concept of this course as I could barely concentrate in the class. We had a professor, who is probably a genius, but the moment he started the class, I would magically fall in to one of my after-noon naps. When I woke up, we are either in the middle of an elaborate t-test or going thru intricacies of a Markovian queue.

This was all 11 years ago. Later in life, I have embraced the world of probability & statistics. I still fear queues. May be I will get there one day. 😉

A good understanding of statistics & probability theory is necessary if you want to model complex real-life problems using Excel or similar tools. Naturally, Excel has several functions, features & supported add-ins to help you in this area.

Today, I want to share some of this with you. This article is broken down in to 3 parts.

1. Learning Statistics & Probability using Excel
2. Downloadable Excel Workbooks to understand
3. Full blown models & simulations in Excel

Few months ago, we learned how to create a project valuation model in Excel as part of our series on Financial Modeling in Excel.

My Project Evaluation Model had a limitation!! In one of the personal comments that I received, the reader pointed out an important problem!

This is a guest post written by Paramdeep from Pristine. Chandoo.org runs Financial Modeling School program in partnership with Pristine Careers. Visit Financial Modeling School to learn more and sign-up for our newsletter.

Greetings!

It’s been long time since we interacted on Chandoo.org. Actually I was very busy teaching the 105 awesome students for financial modeling in Excel. We all worked together to create some easy and some complex financial models. I found the journey to be quite exciting and enriching (From the feedback that I got, my students too didn’t find it bad either 😉 )

During the interaction, I found that a lot of students were looking for financial modeling around the project financing as well. So we thought why not introduce financial modeling for project finance.

In this post I will speak about some of the key aspects of a project finance model and why it can be different from modeling a normal company.

If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape!

How often have you thought of or been asked “I’d like to know what our profit would be for a number of values of an input variable” or “Can I have a graph of Profit vs Cost”

This post is going to detail the use of the Data Table function within Excel, which can help you answer that question and then so so much more.