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
Sales Training Exercise
Sales staff who are not trained properly are usually not very productive, and this is not good for either the staff or...
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 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 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 to Forecast Revenue Growth
Forecasting revenues is the first step in a variety of financial analyses, including widely used valuation models, such as the discounted cash...
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....
How Do I Use Excel's GROWTH Function?
Excel's Growth Function helps when you need to predict future values. Learn how to use this powerful function to calculate retirement savings...
How to Use Microsoft Excel's FORECAST Formula
Learn how to use Excel's FORECAST function to predict a value for Y using a specific value of X, based on known...
How to Forecast Business Trends
A major task for business owners is forecasting business trends for their company. Companies knowing how to forecast business trends will be...
How to Do a Business Forecast
Forecasting the future is essential when launching a new business or maintaining an established business venture. Business forecasting requires that you keep...
How to Calculate Sales Forecasting
Sales forecasting techniques use sales data from past years to predict a company's future performance. Sales forecasts allow companies to anticipate their...
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 Microsoft Excel to Calculate Seasonal Indexes
Microsoft Excel is an excellent tool for organizing periodic data and then using the data to calculate seasonal indexes.
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...
Sales Team Building Exercises
Sales can be a stressful job, full of highs and lows from sales made and lost, and pressures related to consistent performance...