Initial Set up (10 to 15 minutes in total)

  1. Enter Business Information
  2. Enter Product Categories & Locations
  3. Enter Products in Products sheet
  4. Enter Product Prices in Prices sheet
  5. Enter Partners' info in Partners sheet
  6. Review and set up Invoice sheet
  7. Review and set up Purchase Order sheet
  8. Set Currency

 

 

Step 1. Enter Business information 

Enter our Business Information in the Settings sheet. This information will be used to auto-populate in our invoice and purchase orders.


Step 2: Enter a list of product categories. 

This is helpful in analyzing our business performance later. Please enter groups in which you would like to categorize your products.


We have entered two product categories for this demo.


Enter inventory/warehouse locations.



Step 3: Enter the list of Products in Products sheet.

Let’s enter 4 products and its related information.


  • ID: Unique identification of product. This has to be unique. Please do not repeat the same ID or leave the field blank.
  • NAME: Name of the product
  • DESCRIPTION: Description of the product, as needed in our business.
  • STARTING INVENTORY: This is the quantity of the product we have when we begin using the template. This is entered only once and does not have to be updated daily. To keep it simple for this demo, I have entered 0 as starting inventory.
  • RE-ORDER POINT: The quantity of product at which you would like to replenish by ordering.
  • UNIT: This is how we measure this specific product. This can be used to display on the invoice.
  • CATEGORY: Product category to which this product belongs.
  • TRACK INVENTORY: In our business, if we have products we don’t want to track inventory, we can just enter NO. If we want to track inventory, just leave it blank. By default, inventory will be tracked.
  • TAXABLE: In our business, if we have products that are not taxable, we can enter NO. If tax is applicable, just leave it blank. By default, tax will be applicable.

 

Step 4: Enter product prices in Prices sheet.

We will enter the purchase and sales prices in the Prices sheet. These prices will auto-populate on the orders later and help save us a lot of time.


We would enter the Product ID, Effective From Date and then the two prices. The Effective From Date represents the date when the prices became effective. In this example, I am beginning to use the template on May 1, 2016. So, I will enter ‘01-May-2016’ as Effective From Date.

Purchase Price is the price we pay to purchase a product from our suppliers. Sales Price is the price at which we sell the product to our customers.

If a product's price changes in the future, 1) enter a new row for the product, 2) enter the date when new price becomes effective as Effective From Date and 3) enter the effective purchase and sales prices. Note: please enter both purchase and sales price even if only one has changed.

 

Step 5: Enter Partner information 

In the Partners sheet, we will enter the list of our suppliers and customers together. In case a partner is both supplier and partner, you will still enter that partner only once. Partner Name should be unique here. Do not leave it blank.


 

Step 6: Set up Invoice Sheet


Invoice sheet is the printable invoice that can be automatically created for any sales order. This sheet is very customizable to suit your needs.

First, let’s update the logo. Right click on the existing logo and click on Change Picture.


Then, choose your logo image from your computer and click on Insert. Now, your logo will appear on the invoice when printing.


To change the size or other attributes of the logo image, press Ctrl+1 or click the Picture Tools -- Format ribbon. 

Then, let’s review the fields that are displayed on the invoice.



There are 4 key sections in the invoice: Customer Info (8 fields), Order Info (5 fields), Product Info (8 fields) and Order Total Info (6 fields).   If all the fields shown by default will meet our needs, we do not have to change anything. We can move to the next step.

All these four sections are customizable.

  • If you do not need a certain field, just click on a field name and press the Delete key.
  • If you need to choose another field instead, click on the field name. You will see a drop down menu to choose from a list of available fields. Just select the one you need.

Let’s save the file by pressing Ctrl+S. Now, everytime when we create our invoices, it will have the information we need. It’s just that simple.

 

Step 7: Set up Purchase Order Sheet


Purchase Order sheet is the printable purchase order that can be automatically created for any purchase order. This sheet is very customizable to suit your needs, just like we did with the Invoice sheet. The only difference is that the default set of fields shown in Purchase Order slightly varies from Invoice.

First, let’s update the logo. Right click on the existing logo and click on Change Picture.



Then, choose your logo image from your computer and click on Insert. Now, your logo will appear on the purchase order when printing.


To change the size or other attributes of the logo image, press Ctrl+1 or click the Picture Tools -- Format ribbon. 

Then, let’s review the fields that are displayed on the Purchase Order.



There are 5 key sections in the PO: Our Business Info, Supplier Info (5 fields), Order Info (5 fields), Product Info (8 fields) and Order Total Info (6 fields).  If all the fields shown by default will meet our needs, we do not have to change anything. We can move to the next step.



All the four sections (except Our Business info) are customizable.

  • If you do not need a certain field, just click on a field name and press the Delete key.
  • If you need to choose another field instead, click on the field name. You will see a drop down menu to choose from a list of available fields. Just select the one you need.

Let’s save the file by pressing Ctrl+S. Now, everytime when we create our purchase orders, it will have the information we need.

 

Step 8: Set up Currency symbols


By default, the template displays US$ currency symbols. However, it is set up to easily apply any currency symbols as needed.

We can choose a sheet where we want to apply currency symbols. Let’s take Invoice sheet.

Press Ctrl+G to open the Go To dialog box.


 

Find the name ‘CURR’ and click OK.

Excel will now select all the cells for fields UNIT PRICE, UNIT DISCOUNT, TAX, AMOUNT AFTER TAX and the Order Total section fields.

This is assuming you have not modified the invoice fields. If you have changed or re-ordered the fields, then, please select the appropriate cells you need to apply currency symbols. 

 

Press Ctrl+1 to open the Format Cells dialog box.


Select ‘Currency’ in the Category and then choose the symbol from the drop down. Click OK. This will now apply the chosen currency to all the selected cells in the Invoice sheet. Please follow the same procedure in other sheets if you need to see currency symbols.

The calculations in the template are not impacted by currency symbols. This is purely for visual presentation.