Exercises for Sales Forecasting With Excel

Written by sara huter
  • Share
  • Tweet
  • Share
  • Pin
  • Email
Exercises for Sales Forecasting With Excel
Excel is a cost-effective software tool for small businesses. (sales text green down image by Nicemonkey from Fotolia.com)

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 per cent 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 per cent 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 per cent 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.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.