View Single Post
Old 05-07-2011, 09:28 PM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
Join Date: Apr 2011
Posts: 232
Talking Inventory and Sales Excel Spreadsheet Workbook: + Google Merchant Center Integration: Download and Try

_---___-----___ Since 1.0 is the most popular and practical I added the YouTube Video links here as well.----___--

Please Note: The first 3 videos are for the previous version 1.0 and are worth watching to learn how to use the spreadsheet in general. <<< Video 1 <<< Video 2 <<< Video 3 <<< Video 4 <<< Version 2.0 Begins here <<< Video 5 <<< Version 2.0 <<< Video 6 <<< Version 2.0 <<< Video 7 Bonus Sharepoint Video <<< Video 8 Bonus Sharepoint End Video

_---___-----___ NEWEST VERSION IS 2.0 has been released + Google Merchant Center Integration and Instructional Videos included ---_CORE RELEASE!!!----___--
See this thread... New 2.0 download is available at the bottom via zip file.

_---___-----___ CXM' ---_CORE RELEASE!!!----___--
Hi Everyone,

I worked super hard to make a very robust inventory sheet that can do the following.

1. Input your daily sales per Item number - Which should be useful via Ecwid Exports - Input is done per an ongoing monthly basis
1a. Input sales and auto populate Description, supplier, unit cost, price of unit, and quantity sold

2. Input of Sales updates inventory automatically

3. Input Where item was sold i.e. from what store i.e. Store A; Store B; ect... And receive live data updates to as where you are selling the most items from i.e. Store B sells the most.

4. Receive specific sales information per unit item such as how much has sold and what are inventory levels and what is the running total of revenue generated per unit item.

5. Input purchases per item and have it automatically adjust inventory

6. Input Losses per item and have it automatically adjust inventory.

7. Receive nice statistical information from monthly sales in item quantity and in revenues generated...

8. Much much more...

9. Free from ME TO YOU... Please appreciate and give credit to CXM <<

10. Please help with ongoing improvements and suggestions you would like to incorporate into future releases.

11. This spreadsheet inventory and sales workbook is for a year basis. I.e. 2011 sales


What I would like to incorporate next. I would like to be able to incorporate this spreadsheet along with data extracted via Ecwid sales coming from an API CSV file.

I would like to automate through a macro application or VB application inputing items drawn from a database of inventory based upon SKU's we implement into a .CSV or .TXT file.


Next upcoming upgrades:

I will set up .txt files to have the data drawn from the file to keep inventory item SKU's at all times.

I will automate some procedures such as automatic date and time tags for inventory sales purchases and losses updates.

Perhaps make a Youtube tutorial on how to use the spreadsheet

Perhaps write a tutorial on how to use the spreadsheet.


File format = Excel 2010


Please vote that you like and I will keep at this.

Thanks and your help or input is appreciated. hope this helps many.


~~ ``` UPDATE *** June 2, 2011 *** ``` ~~
~~~ Version Master Inventory and Sales ~~~

1. Cleaned up formula's to work with new Tables format. Instead of calling $A$3:$B$5... Now, there is a simplified and more intuitive format that calls on tables such as Sales per month i.e. Sales in January... January, Revenue in January, etc... EXAMPLE [ =SUMIF(January[Item],A2,January[Sales]) ]

These tables now can be filtered and mined for easy to access data. i.e. Pivot tables and specifically PowerPivot Next. Download the free app. Don't worry these are not data tables that bog down your spreadsheet.

2. Added a Profit Table in the Master Sales Worksheet (per user request) This simply takes the cost of the unit and subtracts the sale price. This is in Table Format

3. Added an Overall Monthly Sales Profits to the Middle coloumn area of the sales page so that one can see quickly the monthly sales over the year in terms of profit.

4. Added Sales Profits to each and eveyr month so one can see the aggregate as they are adding in daily sales.

5. Created a Cell Range Reference named [ Master_Inventory_ITEMS ] with an IfERROR function that states "ADD Inventory" if additional inventory is needed from the Master Inventory Sheet.

In other words... For people who will be adding inventory over time need to only do it on the Master Inventory page via the "ITEM" column... This will place the item PLU / SKU on the Master Sales page's "ITEM" Column AUTOMATICALLY. No need to change / update inventory in both places... just master inventory worksheet.

5a. This brings me to another point. A big problem / mind bending / Excel won't do it as far as I know is the ability to change prices on the fly. The table I have created is very elaborate and will save you a ton of time... So believe me when I tell you this is the best way you have to do the following.

**** IF you give a discount i.e. on Product MX270 in order to keep the workbook organized and not confused i.e. tally everything correctly (TRUST ME) then simply do this. Put the discount on the end of the item number as a new item on a seperate row. What does this mean?

So on a new line you will add MX270D5 for unit cost it will be the same i.e. $6.00 and for the price you will simply add this =(original retail price)*.(whatever the opposite percentage discount is i.e.... >>> .95 = 5% off) In summary you will add MX270D5 : Unit cost $6.00 and Retail =14.95*.95 {14.25 or something like that}... This shows up now automatically for everywhere you put the discounted item in.

Now there is only one major flaw with this. Updating inventory. Ok well I suggest use the inventory of what the original product is i.e. if the inventory was 6 just do the start with 6 and keep it moving. Sorry there is nothing I could think of to alleviate this without messing something else up. I will ask around though. If you don't give discounts then Don't worry about this.

as in forum if the last part didn't make sense.

6. I added color to the table and cleaned them up a bit.

I believe a few / couple other things but I can't think of them right now.

Please give feedback.

This version is pretty ready to go for production... lol excel works so don't worry about my version number.


Next version I will ask around for any features I am missing and clean up a few more things... thanks.


~~~~~``````*****UPDATE FULLY WORKING VERSION 1.0 RELEASE ~~~~~~````******

Fully operational CXM's Master Inventory and Sales Workbook CORE rewrite.

**If you have downloaded any previous versions DOWNLOAD THIS AND START NEW...

All new conceptual difference for this worksheet with same extrapelating information for sales and inventory for each item you sale.

***Most important update. Now, the concept of adding in sales per month is accomplished by doing every transaction seperate. Enter in sales as they come in per that day.

Example: If you sale widget 123 go to month June and enter in widget 123 and place whether or not a discount was given by % off i.e. 5% off... >>> Final Price is given and then proceed to mark if it was a sale Yes or No. Once a sale Yes is marked the inventory for the item will be reduced by 1. "-)

This update allows for a few issues / problems I was dealing with in my previous versions to no longer be an issue / problem. YEAHHHHH

Now, you can track sales not just by a preprogrammed price of sale but on the fly updates for what discount was given per each item sold.

All information is accurately refelected in monthly sales # / Revenue and / Profit for each month for each item.

**Other updates includes two new columns for each month including Discount % and Final Price... Again, once the sale is entered in for an item all you have to do is choose a discount from blank or 0% to 50% off. You can add whatever other numbers to the list per your discretion.

**Changed font size to mostly everything to 9pt

**Many calculation and formula rewrites.

**Cleaned up and mess and overall look and style of workbook

**Erased worksheet 35 which was for nothing and had no purpose but practicing equations.

****Again, enter each sale / transaction as it happens and not a quantity for sales i.e. 6. Makes sense right? Only 1 item can be sold at a time. Unless you are doing wholesale. Purchases of wholesale and losses still work the same however.

***Future updates will include working with other worksheet / csv / .txt information to make the workbook more robust. As well, I will begin to incorporate some powerpivot to make workbook even more robust.

Please enjoy and provide feedback.
Attached Files
File Type: zip CXM' (95.8 KB, 14650 views)

Last edited by SoBe Couture; 01-13-2014 at 06:23 AM. Reason: Added YouTube Video Links for 1.0
The Following 4 Users Say Thank You to SoBe Couture For This Useful Post:
Christian Matthias (01-13-2014), Foodie (03-07-2013), Nail Art Market (05-20-2014), Rick G (04-20-2015)