Exercises for Sales Forecasting With Excel

Save

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.

References

  • Photo Credit sales text green down image by Nicemonkey from Fotolia.com
Promoted By Zergnet

Comments

You May Also Like

Related Searches

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!