All articles in 'Financial Modeling' Category
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.
- Build a model to estimate profit per given number of copiers & demand values
- Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)
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
Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”
So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power? A light bulb moment indeed.
Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:
- Regular incandescent bulbs (the kind Hogwarts currently uses)
- Compact Fluorescent Light bulbs (CFL)
- Light Emitting Diode bulbs (LED)
Now your job is to do a cost benefit analysis of these options and pick one.Continue »
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.Continue »
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.Continue »
Lets talk about how we can use Excel to calculate Compounded Annual Growth Rate (CAGR for short).
What is CAGR? What does it signify?
Let us say you are the CEO of ACME Inc. You have been selling various widgets since 2009. In your latest annual report you want to tell your shareholder at what rate you have been growing ACME Inc. sales. The figure are,
- 2009 – $150 Mn
- 2010 – $125 Mn
- 2011 – $160 Mn
- 2012 – $174 Mn
- 2013 – $195 Mn
- 2014 – $210 Mn
Now, if you see the growth rates, they are all over the place. Right from -16.67% to 28%. But you want to report a single annual growth rate.
This is where CAGR (Compounded Annual Growth Rate) comes handy.Continue »
Dear readers & supporters of Chandoo.org,
I am very glad to announce that our brand new online course – “Finance for non-finance people” is now available for your consideration. Please take a few minutes to read this short message to understand what this program is & how it benefits you. If you are ready to join, please click here.
What is Finance for Non-finance people course?
This course aims to teach financial fundamentals & introduce you to the world financial analysis in a no-nonsense way.
We start by introducing financial analysis and the basic jargon. Then we talk about 3 important components of any company’s finances – Balance Sheet, Income Statement & Cash flow Statement.
Then we discuss about various analytical techniques like ratio analysis, valuation mechanisms, break-even analysis.
Finally we explain advanced accounting concepts like long-lived assets, depreciation, explore trend analysis and unit economics.
In a nut-shell, this course takes someone with no finance background and makes them proficient in world of finance.Continue »
Did you know What Happened at Last Coffee Day?
Mr. Bean “dressed up” the financial statements and was caught in the fraud. But he was the CEO of Latte! So why did he commit fraud in his own company??
Take a cappuccino and I will give you a hint – How was Mr. Bean’s Bonus to be decided?
Read on to know more…Continue »
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.Continue »
A Quick Question for you!
Don the hat of a financial wiz today. What I have for you are the financials of two companies: Cappuccino and Latte – Two dot com companies (Sometimes they also make revenues ;-))
Which is better – Cappuccino or Latte? ( Hint: It’s a trick question! 😉 )
It may seem obvious that Latte is performing better (Higher the revenue, the better the performance!). Sometimes a single source of data does not speak the full story!Continue »
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…Continue »
How was your weekend? We had lovely weather. So spent most of the time playing with kids, running some errands & relaxing.
I just want to give you a quick update about our Financial Modeling School. We will be hiking course fees for this program starting tomorrow midnight (Tuesday, 17 July – 11:50 Pacific Time).
If you have been considering to join this program, please go ahead and enroll now to save money.
I have a quick announcement to make. As you know, we run 2 online courses on Financial Modeling.
1) Financial Modeling School
2) Project Finance Modeling School
After running them for almost 2 years, we are now planning to hike the fees by $50. See below table to know about new fees.Continue »
So what’s the cash with Facebook? Last time, we announced the launch of a new module on getting the cash flow statement in our financial modelling course and also discussed the procedure to create the cash flow statement. I had shared with you the template for financial statements for Facebook. I had asked you to implement the steps and create the cash flow model for Facebook!
We know that the devil is in the details. Last time we discussed the process and in this post, we would implement the cash flow statement for Facebook using the indirect method.Continue »
If there was a challenge in any Finance 101 course at any B School, I think Creating the Cash Flow Statement would score the max. When I was pursuing my MBA, the biggest enigma for me was to go from the Balance Sheet and Income Statement to the Cash Flow statement via the indirect route. I could never get it right!
There were two challenges, the bigger was obviously unclear concepts plus I didn’t know how to play with Excel! I have worked hard on both and let me show you, how they can be used to get the right cash flow statement in no time!
Actually the concept is simple and excel makes it run on steroids!Continue »