Offline Business Models in Excel - Practical Guide

In this lecture I will go briefly through the main assumptions for the model that we will work on in Excel. I will also go through the first sheet in which I generate the sales

We begin with little intro into the course as well as some general info on what the course will be about

A little bit about me and my experience

Here I will show you what to do if a blurry image appears

Here I will show you how to find additional resources attached to the coruse like Excel files, presentations, links etc.

In the second section I will discuss the business model of Retailers. I will show you the main challenges they are facing, KPIs that matter in Retail, examples of companies. Since more and more Retailers operate also on-line I will also show you the details of e-commerce business model. I will show you how to model traditional retail as well as e-commerce in Exce

I will start by showing main challenges in Retail

I will show you in this lecture what KPI I the retail business model driven by. This will help you understand the modeling in Excel done in next lectures

Here I will show you how to model the Retailer business in Excel.

In this lecture I will show you how we will estimate the fixed and variable costs in production

In this lecture you will get insight into the business model of different e-commerce companies. E-commerce businesses group is very diversified so we will try to show the similarities and the differences between concepts.. You will learn here the definition of this business model, see some examples of companies using this business models. You will also understand what really matters in this sort of business, what KPIs you should look at and what values you can expect.

In this Lecture I will go through a business model of e-commerce and show you how you can model the behaviour of the customer, acquisition methods and margins in Excel. Attachted to this lecture you will find the file that I show in the video so dowload it and go through it. You can use it to model the e-commerce business you are analyzing

We can be talking about 3 different models in B2C services

• Services performed at specialized place no production involved
• Services performed at specialized place with some production
• Services performed at customer place

I will discuss the differences between them in this lecture

I will shortly go through things that matter in the case of B2C Services performed at specialized place no production involved such as hairdressers, beauty salons and other

I will shortly go through things that are important in other B2C Services such as restaurant chains, services performed at the customer place etc.

In the next lecture I will show you the main drivers of the B2C model and on the basis of this we will create a business model in Excel. I will concentrate on the example of restaurant chain

In this lecture I will show you how to estimate the Gross Margin using sales and production costs

Excel gives you great opportunities to calculate whether your idea makes sense or not. For this you will use the knowledge you have gathered in the previous lectures on your concept, location and your customers. Excel is obviously less emotion than you and the model you will create here can be used as a cornerstone of your business plans. It shows also to banks and potential investors what is your cash flow. We start with a simple model so you can understand it and learn how to use it.

In this lecture we make more advanced model. We introduce the traffic parameter and show how it is converted first into people that are engaged (stop and enter your restaurant) and later into customers (they buy something from you). Here we will be operating on 2 customer groups: first one eats at the restaurant; second one comes for take-aways. For both customer groups we calculate separate conversion rates and Average ticket (ATV). We still here show you a business model for a 1 restaurant

Finally we show you how to model in Excel a restaurant chain.

Next we will move on to business model of B2B services. They behave a little bit differently than the B2C service. I will show you the main challenges they are facing, KPIs that matter in B2C service, examples of companies. At the end I will show you how to model consulting business in Excel.

I will shortly go through things that are important in other B2B Services.

Here we will talk abou the model of service company and consulting firm. It is an introduction to modelling it in Excel

Before I show you the model in Excel I want to present you in this lecture the overall logic

I show you in the lecture the model of the consulting firm in Excel. After modifying it you can use it for your purposes (we attach the Excel files to the lecture). This model will let you check whether your business idea makes sense.

In this section we will move to products. The first one to be discussed are FMCG products. I will show you the main challenges they are facing, KPIs that matter in B2C service, examples of companies. As an example I will show you how to model in Excel Cosmetics business.

I will shortly go through things that are important in other FMCG.

I will show you how we will approach modeling FMCG business model in Excel

Now it is time to estimate the cost of sales by channels and cost of marketing

In this lecture I will show you how to model the head office costs

In this lecture I will show you how to estimate the Net Margin using Gross Margin and the cost of sales and marketing

We have also take care of head office costs to get to profit and loss account

Finally we estimate the profit and loss account and we can try to draw conclusion on the business

We wil finally close with a full profit and loss statement

On the other scale are products for which the brand is not that important and that are simply selling well known, not differentiated product. I will show you the main challenges they are facing, KPIs that matter in B2C service, examples of companies. As an example I will show you how to model in Excel production of plywood

I will shortly go through things that are important in Commodities

Here I will show you the drivers for offline business models

Here I will show you what model we will build for commodity business model and what are the assumptions

In this lecture we will discuss how to model production costs in FMCG

We move on to modeling the Gross Margin in commodit

Now it is time to add the cost of sales and marketing

Now we can calculate the net margin

We cannot forget the head office costs in our clacuations

FInally we can calculate the P&L for commodity

In this section we will model SMCG businesses in Excel. We will use 2 different approaches. In this lecture I will show you what you can expect in this section

I will shortly go through things that are important in other SMCG.

Here I will discuss the main drviers for SMCG business model and how they are interlinked

We will start withteh first business model

In this lecture I will go briefly through the main assumptions for the model that we will work on in Excel. I will also go through the first sheet in which I generate the sales

In this lecture I will show you how to estimate the Gross Margin using sales and production costs

In this lecture I will show you how to estimate the Net Margin using Gross Margin and the cost of sales and marketing

In this lecture we will also add the estimation of Working capital which may be crucial in Cash Flow estimation

We will now try to model SMCG assuming that they are trying to switch from sale of product to sale of services. We will see how the profit loss statement will change due to change in the business model

More and more SMCG businesses are considering switching from just selling the product and loosing track of the customer to selling service which usually, helps you increase your link with your customer base and earn more at the same time

Here I will introduce the case study of a smart phone producer that wants to shift from product sales to service sales. We will be solving the case study in the next few lectures

In this lecture I will show you the solution to previously introduced case study

We will use the case that we have discussed so far to see how we can use it and model the full profit and loss statement of SMCG business operating as a provide of services