Excel Workbook Inventory & Sales Mangagment for Multiple Store Fronts: Download and Try

[xtianus]

Hi all, I have created a nice workbook template for my business... However, I have never created anything so extensible. One question right off the bat I have is the practicality of using it for many items.

For the experts out there would you be able to tell me if using anywhere from 1000 - 5000 items would be feasible? ^ As well, would entering up to 1000 - 5000 sales per month be feasible? I guess what I am looking for is what is too much data before the workbook becomes unstable? or is that not possible..

****HERE IS FILE LINK ---- http://www.box.net/shared/8zazvsv021 ****-----HERE IS FILE LINK----

****HERE IS FILE DESCRIPTION***PLEASE PROVIDE FEEDBACK****

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.

 

[Akashwani]

Hi xtianus,

Welcome to MrExcel.

If you are going to be summing values in the same cells from Jan-Dec, you may want to add 2 new sheets, First, Last. Put First before January and Last after December.

http://www.mrexcel.com/forum/showthread.php?t=434023

You can then use a formula like this....
=SUM(First:Last!A1)
Instead of....
=Sum(January!A1,February!A1,March!A1,etc,etc)

If you have a Master sheet you may want to convert it to a Table.
Seeing as you have Excel2010, it may be a good idea to download Power Pivot......
http://www.microsoft.com/downloads/e...displaylang=en

http://www.youtube.com/user/bjele123...14/ms149Qi2PVA

I'm not an Expert but I wouldn't have thought 5000 items was excessive.
I guess it would depend on how many volatile formulas you had.

By the way, I don't think many people will download your file as it may contain some naughty Macro. If it is Macro free, then state what format it is in.

Just a few thoughts.

Good luck with your project.

Ak

 

[xtianus]

This feedback I truly appreciate. No there is no Macro I won't to build one but I am not too sure how I would go about approaching the need and use for it.

As well, I tried to use the 'January:December'! trick but that wasn't working... I didn't know First:Last would work... Why is that what is needed? Shouldn't it run through from the First=January to Last=December??? That at least explains why it wasn't working...

When you say "Master Sheet" and converting to a table... What do you mean exactly? Like having the inventory needed from SKU numbers into a master CSV or .TXT file?

Also I will check out the Power Pivot information... Can you give a quick synopsis of how it would help my project... ?

perhaps I will figure it out.

lastly, with the Macro stuff... would you be able to help with that? I was thinking to create an app like form - perhaps Access would be better for that - so that information could be added in in a very formatted and convenient way.

Thanks so much

Again there is NO Macro as of yet.. and it is in Excel 2010 format.

 

[Akashwani]

Hi,

You need to insert a New sheet before January and call it First.
You need to insert a New sheet after December and call it Last.
You can then use the Sum function to sum all values between Sheets First & Last in a particular cell, eg... =sum(First:Last!A1) This will Sum all values in A1 of all the Sheets between Sheets First & Last.

Click on one cell on your Master Sheet, click Insert, Click Table, Click OK.
The data you have is now in a Table. When you add a new line of Data, ALL the formulas and formatting will be added to the new line.
You can refer to your Table in formulas, instead of =SUMIF('Master'!$A$2:$A$500,A2,'Master'!$D$2:$D$500)
You would have....
=SUMIF(Table1[ID],A2,Table1[Value])

Table1 is the name of the Table. You can change this.
[ID] is the name of column A
[Value] is the name of column D

When you change one Formula in a column the rest will automatically change.

I don't write or understanding VBA so I wont be able to help you there. When you have a question post it on here with a clear explanation of what you have, what you want to do and your expected results. A sample of your data before and after is very helpful to those trying to assist.

Good luck, I think you have a long road ahead of you

Ak

 

[xtianus]

Here is the Update for June 2011

Sorry I can't edit my own post.

http://www.box.net/shared/0rrpurgmzv

*************************************************
As well, here is one major problem I have had that took me a while to try and figure out how I can "make a workaround" for the problem...

The problem is this. When I have an item 123 I list that in my inventory list as Item 123... the unit price and retail price is listed there so that it autopopulates on other worksheets when information is needed likes sales / revenues / profits etc.

However, the bad part and part I can't seem to do anything about (fix) is when a discount is applied to an item.

Item 123 cost 5 and is sold at 10 but sometimes there is a discount applied. I can't change the total price of 123 because discount is not applied all the time.

The workaround for this is I have decided to list the inventory as 123 123D5 and 123D10 and 123D15 for 5% / 10% / 15% discounting respectively. This alleviates the problem per se but has two blaring errors.

1st = The worst part is all of the added inventory... whatever the amount of items I have I will have to multiply the discounts available so in this case by 3. That is a little painful.

2nd = The inventory control is very accurate for this setup however now if I have the additional items on the inventory sheet they will each have to have their own inventory amount. I would atleast like to put the inventory of the additional items to look after the original items inventory. Would anyone be able to help with that?

To alleviate the second problem I was wondering if I could have "sub rows" to the main items rows and use only a cell reference to call upon the inventory of the main items cell.

Hope this helps anyone and please let me know what else I can do to make this workbook even better. Even with the couple flaws it is the most robust workbook I have seen out there on inventory control.

ie.. would powerpivot help this workbook out? if so can someone give detail?

DIRECTIONS OF UPDATE...

____________----------------____________------------------_______

~~ ``` 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.

 

[xtianus]

any help?

 

[Akashwani]

Hi,

Sorry I can't help you further with this.
What I think you need to do is start a new thread for one problem that you have and once you get that sorted move on to your next problem. In your post give show an example of your data and the expected result.

Sorry I couldn't be of more help.

Good luck.


Ak

 

[xtianus]

Hey Everyone *** Major Update and Revision here. I fixed all of my concerns and problems. This is probably the most robust and complete sales and inventory Excel workbook out there.

Here is the direct link http://www.box.net/shared/myqej0z9ip6xjnhklkbb

Here is the update information.

____________________________----------------________________-------

~~~~~``````*****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. <!-- / message --><!-- attachments -->

 

[kumanah]

Xtianus,
i have seen your worksheet, it is wonderful. I want to us it for my retail stationery store. Is there no way i can copy and paste sales that had taken place before today(let's say January-June) before continuing with the current month

 

[kumanah]

Xtianus,
The workbook is fantastic, but just a little thing to add. Sales quantity must be entered since more than one unit of an item can be sold per time. For instance, Bic Pen can be sold 20pcs at once. See how this can be built in and the spreadsheet will become perfect.

Nice work
Kumanah