Review
What is Forecasting?
What-if Analysis
Scenario Manager
Objective
Goal Seek
Data Table
Business Plan: Forecast
What is Forecasting
Decision Making Tool
Predicting future based on past and present data
This helps organization remain prepared for different situations
Types of Forecast
Judgement Forecast Example: New Product Launch
Quantitative Forecast Excel can be used because based on past data and numbers
Factors to be considered, while forecasting
Context of forecasting
Availability of Historical Data: What are the underlying dynamics and componen
Degree of Accuracy Required
General Scenario of when it is done
Demand Forecast: Can enable company to respond to changes in demand
Business Forecast: Predict outcomes, will the product be a success or not. (Inpu
Inventory Planning: This is done to preserve the quality of the product, ensure s
Statistical Forecasting: Model Based and Time Series Forecasting
data and numbers
amics and components
nges in demand
success or not. (Inputs should be taken from front line staff)
the product, ensure speedy delivery, flexibility towards change in demand.
Inputs
Income
Pocket Money 4500
Tutions 5000
Total Income 9500
Monthly Expenses Units Per Unit
Food 4 100 400
Movies 7 300 2100
Tution 2500
Internet 1500
Total Expense 6500
Savings 3000
Inputs Outputs
Units Costs
Sales Price 500 Revenue 125000
Units Sold(Customers 250 COGS 37500
Manufacturing Costs 150 Gross Profit 87500
Customer Acquisition 75
Marketing 18750
Fixed Costs Rent 20000
Rent 20000 Profit 48750
Net Profit Margin 39%
How to Get 50% Net Profit Margin?
Case 1 Sales Price
Case 2 CACost
How to Get Rs. 300000 Gross Profit?
Case 1 Sales Price
Case 2 Units Sold
How many Units to sell at Rs. 500 to Break Even?
Quantity 250
Unit Price 500
Fixed Costs 20000
Variable Cost(per unit) 250
Revenue 125000
Less: Fixed Cost 20000
Less: Variable Cost 62500
Net Profit 42500
42500
Unit Price
500
450
400
Make a Data Table of 7 x 7 Matrix
Quantity
200 250 300
Data Table of 7 x 7 Matrix
Case You have decided to start a business that provides in-home technical computer support to
You have seen national advertisements for a company that provides these services in other
of your dorm room, and you know plenty of students who have the necessary technical skil
more than the university pays under its work-study programs. To get up and running quickly
system, an advertising campaign, three vehicles, and tools. You would also want to have e
began to generate positive cash fl ows. All of this would require about $100,000, which is a
credit cards, against
your car, and from friends and family
You are now working on the fi nancial forecasts for the business. You plan to charge $45 for
for each additional 30 minutes. Since you expect that the typical house call will require 60 m
$70. You also estimate that monthly fi xed operating costs (FC), which include an advertising
salary for you, will total $3,000. Unit VC, including the technicians’ pay, gas, and so forth, w
depreciation and amortization charges (D&A) will be $1,000. Finally, you expect that after s
calls per month. Given this information, what do you expect the monthly EBIT to be in six m
Estimation of EBIT
EBIT = Revenue - VC - FC - D&A
Revenue $ 8,400.00
-VC $ 2,400.00
-FC $ 3,000.00
-D&A $ 1,000.00
EBIT $ 2,000.00
Financial Forecasting
As you prepare the fi nancial forecast for your computer-support business, you worry about
house calls on EBIT. You decide to examine how converting some fi xed costs to variable co
in the number of house calls. In a conversation with the manager at the radio station where
instead of paying $1,500 per month under a long-term advertising contract, you can get th
$1,000 of the total cost is fi xed and $600 is variable. That is, in a given month, if you used
$1,600, but you would also have the ability to reduce advertising costs to $1,000 by cutting
wonder how this contract would affect the sensitivity of EBIT to a decrease in the monthly n
Fixed Alternative
Advertising Advertising
with More with Fewer
Key Assumptions house calls Housing Calls
House Calls up to 30
min 45 45
Each Additional 30
min 25 25
Revenue from typical
Call 70 70
FC 3000 2500
VC 20 20
Alternative
Advertising Option 600
VC/Unit of
alternative
advertising option 7
Monthly D&A 1000 1000
Volume of Calls per
Month 120 90
Fixed Alternative
Advertising Advertising
Costs Costs
Revenue 8400 6300
Less: Variable
Cost(VC) 2400 2430
Less: Fixed Cost (FC) 3000 2500
Less: Depreciation
and Amortization 1000 1000
EBIT 2000 370
cal computer support to people in the community near your university.
es these services in other communities.You would run this business out
e necessary technical skills and would welcome the opportunity to earn
get up and running quickly, you would have to invest in a computer
would also want to have enough cash to keep the business going until it
bout $100,000, which is about all that you think you can borrow on your
ou plan to charge $45 for house calls lasting up to 30 minutes and $25
house call will require 60 minutes, you expect it to result in revenue of
hich include an advertising contract with a local radio station and a small
’ pay, gas, and so forth, will total $20 for the typical house call. Monthly
ly, you expect that after six months the business will average 120 house
monthly EBIT to be in six months?
ation of EBIT
al Forecasting
