123 Office Supplies Manufacturing is the leading office supplies manufacturing in this industry. We strive to provide top quality products at a reasonable price. All of our distributors are provided a net price for all of our products for use on day to day sales to their end user customer. However, if they are offered the opportunity to fulfill a large order, 123 Office Supplies will review the details of the order and offer project pricing. The customer will still be invoiced for their day to day net pricing. However, they can submit a monthly invoice for review to obtain a product credit for the difference between their day to day pricing versus the project pricing. It is the responsibility of our Inside Sales team to review, analyze and process the appropriate credits each month. This tutorial is to be used by the Inside Sales team as a reference on how to process the claimback credits. In this example, ABC Distribution has submitted their January 2019 invoice to obtain credit for the materials they sold at the Bowling Green Elementary project pricing.
You will need the following items before you begin:
3. Email or Fax Machine
5. Copy of the Customer Invoice
6. Access to customer pricing and project pricing
By the end of this tutorial, you should be able to properly process a claimback credit.
Disclaimer:This tutorial is for the sole use of training the Inside Sales team at 123 Office Supplies Manufacturing. All of the names, products and prices have been created for this training purpose only. This tutorial in no way holds any companies or persons liable for any pricing or credits. All photos and videos used in the tutorial are to be used by the creator of this tutorial, Abby Essex only. No permissions are granted for use of any of the material in this tutorial by anyone other than Abby Essex. In addition, Abby Essex cannot be personally liable or legally responsible for any outcomes produced from the use of this tutorial.
Step 1: Open Excel
In the bottom left-hand corner of your desktop, click on the windows icon. From your Windows Start Up Menu, type in the search bar "excel." Double click on the Excel icon that pops up on the menu screen.
Step 2: Open a New Excel Spreadsheet
Once Excel opens, the new and recent spreadsheets list will appear. Select "blank workbook" by double clicking on the first icon. Excel will open your new workbook. You will begin in first cell, A1 (shown highlighted).
Step 3: Add Column Titles
In the first cell, A1, type the first title, "Date of Invoice" and press enter. Using your mouse, click on cell A1 again. On the line between A1 and A2, use your cursor to double click so that the cell stretches out to hold all of the words. Continue adding the column titles and stretching each column to fit the whole word. Next, highlight all of the column titles by clicking on the first cell and dragging the highlighter to the last cell with words. In the menu ribbon, select the bold icon and the underline icon.
Step 4: Pricing Information and Customer Invoice
The customer pricing spreadsheet is sent to all Inside Sales employees by email each month. Access your email to pull up the customer pricing spreadsheet attachment for reference. Make sure to confirm the correct customer tab is showing. Next, print a copy of the customer invoice. It may be received by email or fax from the customer. Confirm that the invoice pricing requested is active on the customer pricing list. In this example, ABC Distribution is referencing a credit request for the Bowling Green Elementary project. On the pricing spreadsheet, the ABC Distribution tab does show active pricing listed for this job.
Step 5: Copy Invoice Information to Spreadsheet
Next, type in all of the necessary data from the customer invoice. Once that information has been added, select all of the cells under titles "Net Price" to "Credit" by clicking on the first cell and then holding down left mouse button and dragging to the last cell in the last column. This will highlight the entire area. In the menu ribbon, click on the $ icon. This will now make all of the numbers that you type a dollar amount.
Step 6: Add Pricing
From the customer pricing spreadsheet, add the day to day pricing and the appropriate project pricing.
Step 7: Add "Difference" Formula
To add a formula, start by clicking into cell F2. Type = and then click on cell D2. Next, type - and then click on cell E2. Press enter. This will enter the difference between the day to day price for paper and the project price for paper. Next, click on the + sign in the bottom right corner of cell F2 and drag it to cell F6. This will auto populate the same formula, but it updates to the appropriate difference per line.
Step 8: Add Credit Formula
To add the credit formula, start in cell G2. Type = and then click on cell F2. Then, type * and the click on cell C2 and press enter. This will automatically add the price difference multiplied by the total quantity that was ordered. The difference between the day to day price and the project price for paper is $22.00. ABC Distribution purchased 20 cases of paper for the Bowling Green Elementary project in January. The total credit owed for the paper purchase is $440.00. Next, click on the + sign in the bottom right corner of cell G2 and drag it to cell G6. This will auto populate the same formula, but it updates to the appropriate credit per line.
Step 9: AutoSum Total Credit
Start by clicking into cell G7 and then on the menu ribbon, click on the AutoSum icon. This will highlight the credit column. Press enter and the total of all the credit cells will populate. This it the total credit that is owed to ABC Distribution for this claimback credit.
Step 10: Save
From the File menu, select Save As. Then, in the next menu select This PC. With the desktop highlighted on the left, type in the file name and click save.
Step 11: Email and FIle
The last step is emailing the document to the credit department. You can also send a copy to the customer for their files as well.
Step 12: Video Tutorial
Please watch this video for a step by step recording of this claimback credit process.
Step 13: All Done!
Thank you for taking the time to follow this tutorial on how to process monthly claimback credits. I hope the details provided were easy to follow steps that you will be able to use for future reference.