Search

# All articles in 'Financial Modeling' Category

## Loan Amortization Schedule in Excel – FREE Template

Published on Feb 20, 2024 in Financial Modeling, Learn Excel

Do you want to calculate loan amortization schedule in Excel? We can use PMT & SEQUENCE functions to quickly and efficiently generate the full loan amortization table for any number of years.

Continue »

## Top 10 Accounting KPIs and How to Calculate them in Excel?

Published on Aug 8, 2023 in Financial Modeling, Learn Excel

We can calculate any Finance & Accounting KPI values using Excel easily. In this article, I  am sharing the top 10 accounting KPI calculations. These are… Topics ? Net Profit Margin Net Profit Margin = Net Profit / Sales Positive profit margin indicates business is profitable while negative indicates the business is in loss. ? […]

Continue »

## Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Published on Oct 16, 2019 in Analytics, Financial Modeling, Learn Excel

• 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
Continue »

## Should finance people learn Power BI?

Published on Jun 14, 2019 in Financial Modeling, Power BI

I recently went to Sydney to conduct some training programs on Advanced Excel and Power BI. While I was there, I met my good friend Danielle, who runs Plum Solutions, a financial modeling consultancy & training company. We got talking about various things and the topic eventually turned to “finance people and Power BI”. We […]

Continue »

## Which power plan is best for me? Excel for everyday problems

Published on Nov 9, 2017 in Analytics, Financial Modeling

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.

Continue »

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

Published on Sep 13, 2016 in Analytics, Financial Modeling

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 »

## CP042: Financial Analysis & Modeling concepts – 101

Published on Aug 13, 2015 in Chandoo.org Podcast Sessions, Financial Modeling

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
Continue »

## Doing Cost Benefit Analysis in Excel – a case study

Published on Jan 28, 2015 in Analytics, Charts and Graphs, Financial Modeling

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 »

## Implementing Modular Spreadsheet Development – a walkthrough

Published on May 21, 2014 in Financial Modeling, VBA Macros

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

Continue »

## Modular Spreadsheet Development – A Thought Revolution

Published on May 7, 2014 in Financial Modeling

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 »

## Calculate CAGR (Compounded Annual Growth Rate) using Excel [Formulas]

Published on Apr 29, 2014 in Financial Modeling

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 »

## Introducing ‘Finance for Non-finance people’ training program

Published on Jan 10, 2013 in Financial Modeling, products

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 »

## Dressing Financial Statements – What Motivated Mr. Bean to Defraud Latte?

Published on Dec 11, 2012 in Financial Modeling

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??

Any Guess?

Take a cappuccino and I will give you a hint – How was Mr. Bean’s Bonus to be decided?

Continue »

## Macros for Automatically Implementing Modeling Best Practices

Published on Nov 29, 2012 in Financial Modeling, VBA Macros

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 »

## Financial Ratios – Cappuccino or Latte?

Published on Nov 21, 2012 in Financial Modeling

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 »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.