Microsoft Excel is a software tool for forecasting and budgeting. Predicting or forecasting sales is important so that a business owner can determine whether his products will be profitable after subtracting expenses from sales. Forecasting sales also helps in cash budgeting. Sales forecasting using Excel can help estimate sales growth, dependence on other products, seasonal sales and impact of discounts. Excel makes the process easy and reliable.
Forecasting Sales Growth
If you know that sales for a product will increase at a certain percentage, forecasting sales is a simple process of calculating the growth rate and total sales for each month.
Try this exercise: Last month's sales figure for coffee machine repair company "Coffee Bits" was 1,000 units. Sales are expected to grow 3 percent monthly. Use Excel to calculate next month's sales.
In Excel, enter formulas without spaces or thousand separators (commas). Remember to start a formula with an "=" sign. For example, input the next month's sales formula as "=1000*(1+0.03)" and press "Enter." The result should be "1030" or 1,030.
Forecasting a Product's Relationship to Another Product
When one product is dependent on another product's sales, forecasting sales is a matter of quantifying the relationship between the two products, and calculating sales based on the forecasted sales of the related product.
Try this exercise: Assume that one of the products "Coffee Bits" sells is a specific part for a high-end Italian espresso machine. Therefore, sales and repair orders for the Italian coffee machine will drive sales for one Coffee Bits product. For example purposes, assume that 500 Italian coffee machine units will be sold next month and that Coffee Bits sells 1.1 parts on average for each coffee machine sold. Forecast the sales for this part for the next month.
In Excel, input "=500*(1.1)" and press "Enter." The result should equal 550.
Forecasting Sales for an Expected Price Reduction
Companies often attempt to increase sales by reducing the price. Sometimes they hope to clear their inventory to prepare for new products.
Try this exercise: Assume that the Italian espresso manufacturer has a new model coming out this year and wants to clear inventory by selling the espresso machines at steep discounts. This will result in increased sales for Coffee Bits. For example purposes, assume that the discount will result in a 50 percent increase in sales from a normal level of a million units. Calculate sales due to the discount.
In Excel, input "=1000000*(1+0.50)" and press "Enter." The result should equal "1500000" or 1.5 million.
Forecasting Seasonal Sales
Many product sales follow a seasonal pattern. For example, flannel sheets tend to sell well in the winter months, but not in the spring and summer. Similarly, some products, such as boxed chocolates, sell better during the holiday season as gifts.
Try this exercise: Assume that most of the Italian espresso machine sales occur during the holiday season. It would be reasonable to assume that the parts sold by Coffee Bits will have their highest sales a month prior to the holiday season sales period for the espresso machines. For example purposes, assume that Coffee Bits expects to sell 12 million units over the next twelve months and that 80 percent of these sales are expected to happen in August, September and October. Calculate the sales in these months.
In Excel, enter the following formula to calculate holiday season sales: "=12000000*0.80/3" and press "Enter." The result should be "3200000" or 3.2 million.
- Photo Credit sales text green down image by Nicemonkey from Fotolia.com
How to Forecast Revenue Growth
How to Forecast Returns on Equity Using Output Growth; How to Forecast Sales with Excel; How to Do a Business Forecast; How...
Sales Training Exercise
Sales Training Exercise. ... Sales forecasting is an estimation process businesses use to determine the amount of achievable sales revenue.
How to Forecast Sales with Excel
Forecasting is more of a science than an art with advent of computer programs that have the ability to accurately predict future...
How to Make Graphs for Forecasting Sales in Excel
Being a psychic isn't necessary for predicting future sales. All you need is your past sales data and Excel. Excel will do...
How to Use Excel for Forecasting
Business forecasting has transformed from the art of common sense and educated guesses to the science of critical business planning. It is...
How to Forecast Business Trends
How to Forecast Business Trends. ... Financial forecasting is a valuable exercise because it helps the management team project how much cash...
How to Use Microsoft Excel to Calculate Seasonal Indexes
A seasonal index is a measure of seasonal variation--a change to a variable, such as profits or sales volume, ... How to...
How to Use Excel for Exponential Forecasting
When a young company is forecasting its potential growth, the revenue projections rarely follow a straight line. Most companies expect that as...
How to Forecast Sales
Sales are the most important aspect of any business. Forecasting sales is both a science and an art. The ability to accurately...
How Do I Use Excel's GROWTH Function?
How to Calculate EPS Growth Rates Using Excel. Earnings per share (EPS) growth is an important factor to investors. ... How to...
How to Use Microsoft Excel's FORECAST Formula
The Forecast function provides users with a way of predicting a future value based on existing values. ... How to Forecast Sales...
How to Do a Business Forecast
In a nutshell, forecasting requires an ability to think strategically to ensure the viability and growth of your business. ... How to...
How to Calculate Sales Forecasting
Sales forecasting depends heavily on estimates. ... How to Use MS Excel Forecast Formula. Microsoft Excel is a powerful tool for analyzing...
How to Calculate Sales Margin
Your sales margin is an indication of how profitable your business is. The higher your sales margin, the more profitable your business....
Sales Team Building Exercises
Exercises for Sales Forecasting With Excel. Microsoft Excel is a software tool for forecasting and budgeting. Predicting or forecasting sales is important...