View Poll Results: Do you like this worksheet and would you like tutorials and updates?
Yes - Thanks so much - keep it coming 20 90.91%
No - why would I need this 1 4.55%
I would like tutorials via youtube or pdf 2 9.09%
I would not care to have tutorials 1 4.55%
Multiple Choice Poll. Voters: 22. You may not vote on this poll

Reply
 
Thread Tools Display Modes
  #1  
Old 05-07-2011, 09:28 PM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
 
Join Date: Apr 2011
Posts: 237
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.
http://youtu.be/rlP3jrRDfgM <<< Video 1
http://youtu.be/uEPfP7n_Mwg <<< Video 2
http://youtu.be/ISNDkflemHw <<< Video 3
http://youtu.be/0Se8n00f4CE <<< Video 4 <<< Version 2.0 Begins here
http://youtu.be/7uFRcHOPWE8 <<< Video 5 <<< Version 2.0
http://youtu.be/r0SibSyD4sI <<< Video 6 <<< Version 2.0
http://youtu.be/P7oa_8vTE-o <<< Video 7 Bonus Sharepoint Video
http://youtu.be/WSz0XtI1biw <<< 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... http://www.ecwid.com/forums/showthread.php?t=7818 New 2.0 download is available at the bottom via zip file.

_---___-----___ CXM's-Master-Inventory-and-Sales_v1.0.zip ---_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 V0.1a.zip ~~~

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's-Master-Inventory-and-Sales_v1.0.zip (95.8 KB, 6936 views)

Last edited by SoBe Couture; 01-13-2014 at 06:23 AM. Reason: Added YouTube Video Links for 1.0
Reply With Quote
The Following 3 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)
  #2  
Old 05-09-2011, 01:43 PM
alan's Avatar
alan alan is offline
Junior Member
 
Join Date: May 2011
Posts: 1
Thumbs up Superb...Thank you

Works great man, just what i was looking for. However, adding a "profits" cell next to "overall monthly sales volume" in each month might be handy. I know this is probably pretty easy to do. Just have to subtract the sales minus the cost. Another thing, you mentioned something about adding sku's. In my case, i use my own upc #'s. Would it be too hard to add (maybe a vlookup function) in the master inventory sheet, so when i scan an item, it checks if i have it, and if not, adds it to the inventory sheet? Thanks for the great work. Would appreciate any hints or comments.

Alan
Reply With Quote
  #3  
Old 05-11-2011, 03:33 AM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
 
Join Date: Apr 2011
Posts: 237
Default

Quote:
Originally Posted by alan View Post
Works great man, just what i was looking for. However, adding a "profits" cell next to "overall monthly sales volume" in each month might be handy. I know this is probably pretty easy to do. Just have to subtract the sales minus the cost. Another thing, you mentioned something about adding sku's. In my case, i use my own upc #'s. Would it be too hard to add (maybe a vlookup function) in the master inventory sheet, so when i scan an item, it checks if i have it, and if not, adds it to the inventory sheet? Thanks for the great work. Would appreciate any hints or comments.

Alan
I am updating to a new version... Be on the look out. I have updated the formulas to be in tables so that data will be much more "mineable" and easier to follow.

As well, I have added your profit sheet information.
Reply With Quote
  #4  
Old 05-27-2011, 09:59 AM
Wmir's Avatar
Wmir Wmir is offline
 
Join Date: Apr 2011
Posts: 21
Default

Amazing.. will try it out
Reply With Quote
  #5  
Old 06-02-2011, 05:46 AM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
 
Join Date: Apr 2011
Posts: 237
Default

Update comeing in thirty mintues... let me know what you all think. thanks
Reply With Quote
  #6  
Old 06-02-2011, 08:29 AM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
 
Join Date: Apr 2011
Posts: 237
Default

Update Added. Version 0.1a.zip

Let me know what you all think... Thanks.
Reply With Quote
  #7  
Old 06-04-2011, 12:54 PM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
 
Join Date: Apr 2011
Posts: 237
Default

LOL,

I see people are downloading the old version. I don't want to erase the old version but perhaps the fact it is not first it causing a problem so I am going to erase it for now and repost the old version afterwards to it will be on the bottom and not the top.

Please provide feedback for newest version.
Reply With Quote
  #8  
Old 06-17-2011, 08:13 PM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
 
Join Date: Apr 2011
Posts: 237
Thumbs up

Final Workable version... coming out soon.
Reply With Quote
  #9  
Old 06-18-2011, 03:17 PM
SoBe Couture's Avatar
SoBe Couture SoBe Couture is offline
Senior Member
 
Join Date: Apr 2011
Posts: 237
Default

BOOOMSHOCKALOCKA

CXM's CORE Version 1.0 Master and Sales Inventory Excel Workbook is here.

An Ecwid Exclusive for the next 48 hours...

Redid a lot! Re-worked the formula's and calculations to "perfection."

Quickbooks who?

Please downloand V1.0 and enjoy. Read above for version update and instructions. Will come out soon with youtube videos explaining how to use and customize.

"-)
Reply With Quote
  #10  
Old 06-18-2011, 03:26 PM
WoodYouLike's Avatar
WoodYouLike WoodYouLike is online now
 
Join Date: Aug 2010
Posts: 276
Default

Is it just me, but where can I download this?
__________________
Owner/manager of Wood You Like Ltd, independent retailer of quality wooden flooring in rural Kent UK
Creator of E-guide "Selling Online Basics"
Reply With Quote
Reply

Tags
excel, inventory, sales, workbook, worksheets

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 11:55 AM.
Powered by vBulletin® Version 3.8.6. Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.