Financial projections in a business plan normally start with the sales forecast. Sales refers to the monetary amount from the sale of goods or services in which the business normally trades. This sales forecast spreadsheet will help you to calculate total sales for use in our financial projections template.
Our article on sales projections gives further details of the sales forecasting methods used for a product or service business.
Sales Forms Printable Business Form Templates in DOC format. Get all of our printable, customizable business forms for $47 as an instant download. Click any business form template to see a larger version and download it. Credit Application. Daily Sales Log. Livestock Bill Of Sale. Price Increase Announcement.
The sales forecast spreadsheet, which is available for download below, helps estimate sales for the next five years. The excel spreadsheet can deal with up to four unit based and four non unit based products or services. Unit prices can be uplifted for each of the five years by a fixed percentage to allow for inflation and price increases.
Using the Sales Forecast Spreadsheet
The spreadsheet is available for download below and is used as follows:
Enter units information
Enter the product name and an estimate of the number of units of the product which the business plans to sell for each of the next five years. If you simply want to uplift each year based on the previous year, a formula can be entered into the cell instead of a numeric amount. For example, for Product 1, if you wanted Year 2 (cell E4) to be 5% higher than Year 1 (cell D4), in cell E4 you would enter =D4*105%, and copy this formula across to each cell up to cell H4.
Enter the unit price information
Enter the unit price of each product for Year 1, and the uplift percentage required for the following four years.
The uplift is the expected percentage change in the unit price of the product over the year, for example, if the unit price for Year 1 is 12.00 and the expected uplift is 2%, then the price in Year 2 would be 12.00 x 102% = 12.24, and the price in Year 3 would be 12.24 x 102% = 12.48 and so on until year 5. If you anticipate the prices falling, then enter a negative percentage.
The sales forecast spreadsheet calculates the unit prices, the monetary sales value for each product, and the total product sales for each of the five years.
Enter the other sales information
For completeness, the sales forecast spreadsheet allows the sales of up to four non unit based products to be entered. The amount to enter is the total monetary value of each type of sale for each year. This allows a business to enter supplementary sales such as service income, or rental and hire income etc. to establish a complete picture of the total sales of the business for each year.
Finally, the sales forecast spreadsheet calculates the total other sales and the total sales of the business for each of the next five years. This sales forecast can now be used as the starting point in our financial projections template.
Sales Forecast Spreadsheet Download
The sales forecast spreadsheet is available for download in Excel format by following the link below.
Notes and major health warnings Users use this sales forecast spreadsheet at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is a sales forecast excel template that you might use when sales forecasting in Excel. It is purely illustrative of a sales forecasting tool. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the sample sales forecast template, please let us know and we will try to fix it.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.
This Inventory and Sales Manager Excel template is suited for managing inventory and sales if you are running a small business of buying products from suppliers and selling to customers. (Retail/Wholesale).
This retail inventory excel template will assist in knowing the inventory levels of each product and understanding which products to re-order. Also, you can quickly view the purchases/sales patterns over time and the best performing products.
For Retail businesses, a new version is availableRetail inventory Tracker (Free)
For Manufacturing businesses, Manufacturing Inventory Tracker Excel Template (Free)
For Rental businesses, Rental Inventory Tracker Excel Template (Free)
FREE DOWNLOAD
If you use Windows and Excel 2010 (or above version) – Retail Inventory Excel Template (Windows)
Sample File with some sample Data for reference – Sample File – Retail Inventory Excel Template
If you use Mac and Excel 2011 (or above version) – Retail Inventory Excel Template (Mac)
This version does not have the slicer based Report sheet. Instead, Pivot tables are provided in separate sheets.
FEATURES
Enter and manage up to 2000 different Products
Set custom re-order points for each product
Simple and Easy data entry
Know current inventory levels of each product
Identify the products to be re-ordered
Know if the sale orders can be fulfilled
Easily understand the sales and purchase patterns (monthly and cumulative)
Quickly see your top customers and suppliers
Identify your best performing products
Know how the different product categories contribute to sales
Easily retrieve and view your order details
VIDEO DEMO
Click here to watch the video on YouTube.
HOW TO USE THE TEMPLATE
Enter Products
Enter list of products and re-order points in the Products worksheet
Product Category: This allows you to categorize products. If you have numerous products, categorizing similar products together can help in understanding product performance.
Re-order Point: Amount that you set for each product, where when the current inventory level hits that amount, you will place a new purchase order to replenish inventory. (For more, read Re-Order Point Article in Wikipedia)
Enter Orders
Enter the line items for all the orders (both purchase and sale) in the Orders_and_Inventory worksheet
If you have any existing inventory when you start using the template, enter them first. You can then continue to enter your new orders (purchase and sales) as they happen. The template will then give you accurate count of your inventory.
Order Number: This Order number is not used in the template to calculate anything. This has been provided for you to track your orders easily. You can filter the Orders table by choosing specific order number to see all the items in that order. If your systems generate any order numbers, you can enter them here. If you don’t have any such systems, you can create your own. The only recommendation is that you should have a unique order number for each order.
Order Type: There are two types of Orders: Purchase and Sale. When you place an order to acquire products from suppliers, it is called a Purchase order. When your customer places an order to buy products from you, it is called a Sale.
Order Date: For Purchase orders, this is the date when the order is placed by you to your supplier. For Sale orders, this is the date when the order is placed by your customer to you.
Expected Date: For Purchase orders, this is the date when the inventory becomes available for you to sell. For Sale orders, this is the date when the inventory will leave you to the customer.
Partner: For Purchase orders, your supplier is the Partner. For Sale orders your customer is the Partner.
Quantity: Number of units of products. The unit can be any numeric value. Even if your unit is not whole numbers, you can still use the Quantity field.
Unit Price: In Purchase orders, this is the cost of buying one unit of the product. In Sale orders, this is the revenue from selling one unit of the product.
Amount (Calculated field): (Unit Price X Quantity) = represents the amount of money. In Purchase orders this would be money leaving you and in Sale orders, this would be money that customers pay you.
Inventory Availability (Calculated field): This is the quantity (number of items) of the product available in inventory as of the Expected Date.
View information about overall inventory availability
Current Inventory of a product = (Total Purchases of Product – Total Sales of Product) as of today
Products Available: Number of Products where the current inventory level is greater than 0.
Quantity: Total Number of items of all Products currently available
Products to Re-order: Number of Products where the current inventory is less than or equal to the re-order point
Order Items that cannot be fulfilled (Current): Among the orders where the fulfillment date is less than or equal to today, number of line items in orders where the available inventory is less than the Sale quantity
Order Items that cannot be fulfilled (Future): Among the orders where the fulfillment date is in the future, number of line items in orders where the available inventory is less than the Sale quantity
View details of one specific product
Choose a product from the drop down and see details of that specific product.
Pending Purchase Quantity: Quantity in the Purchase Orders that are expected to be available in the future
Pending Sale Quantity: Quantity in the Sale Orders that are expected to be delivered in the future
View products to re-order
If there are line items that cannot be fulfilled or if there are products to re-order, take actions appropriately
View Report
View the Report worksheet to understand the purchase/sales trends and also to identify the top performing products and most valuable suppliers/customers.
Since there are pivot tables and charts, please refresh the data by pressing Ctrl+Alt+F5 or going to DATA ribbon and selecting Refresh All. This updates the charts with your new transactions.
The report sheet has slicers (filters) at the top.
Amount and Cumulative Amount by Month
Quantity and Cumulative Quantity by Month
Amount distributed across Product Categories by Month
Quantity distributed across Product Categories by Month
Amount Distributed across Partners
Product Ranking based on Sales Amount or Quantity
If you find the template useful, please share with others. If you have any feedback, please share it in the comments below.
RECOMMENDED RETAIL INVENTORY TEMPLATES
Retail businesses with one warehouse location Retail Business Manager Excel Template
Retail businesses with up to 10 warehouse locations, Retail Inventory and Sales Manager Template