Sari-sari Store Inventory Excel

Updated 27 March 2024

Download Link

Sari-sari Store Inventory v.3.1

v.3.1

minor change:

-included new metric

===================================================================

Updated 16 March 2024

Download Link

This is a zip file. Please use Microsoft Excel 2016 or above for better compatibility and enable macros.

Sari-sari Store Inventory v.3.0

v3.0

major changes:                            

-removed Nabenta Sheet. This is a redacted version.                         

-the download has blank file for less hassle with removing samples              

-Info Sheet changes removing unnecessary infos                                     

-CashFlow Sheet summary reporting above was created for easy access to business status                       

I enabled comments again below. Mag-ingat lang po sa mga spammer sa comment section. Di ko po sagot kung me magbigay sa inyo ng link o file sa baba at ma-virus ang inyong computer. It's between you and whoever is advertising their services. Wala po akong pananagutan. Andyan po sa taas ang pinakalatest download file link natin. Pipindutin na lang at idodownload. Medyo busy po ako kaya di ko na magawan pa ng video tutorials ang bagong file natin. Pagpalain nawa tayo ng Poong Diyos.              

===================================================================

Updated 13 November 2022

Please note that Microsoft Excel became slow with conditional formatting, as lots of links to such can attest. Therefore don't just delete blank rows with conditional formatting to speed up our excel. The download link below has 2401 rows and you need to edit module 1 vba if you will add or delete table rows. Automatic price changing would no longer work if changes are not the same on both vba and table rows. Hint: You can always use Excel 2010 which is fast.

====================================================================

Updated 25 February 2022

 Download Link:

(Please download and open with Microsoft Office Excel. You can view it on Google Sheet, but it has macros and formulas made for Excel)

Sari-sari Store Inventory v2.1

My apology to my readers. I didn't know Microsoft One Drive introduced expiration in their online file links since 2017. Now the link above goes back to Google Drive again. Thank you Google Drive. Sorry for the inconvenience since I am not maintaining old links below which might not be accessible anymore. Only Sari-sari Store Inventory v2.1 link is being maintained for now. History of the old links are reflected all in the above present link.

 

v2.1

minor changes:

-included auto formula of IDAM(Inventory Days Average Multiplier)

-corrected Nabenta sheet note instructions

 

v2.0

major changes: 

-Main Sheet added ChangePrice button vba for automation

-Sheet reorganization Info, Main, bentaID, CashFlow, CashInBank, AccountsP&R, LoadTuboCalc, CoinWeight, Nabenta

-Info Sheet includes auto generating Top Earners, Gross Earnings, etc.

-Nabenta Sheet can be used for manual POS but is optional

-Lots of notes and explanations added

 

system requirements:

-Microsoft Office Excel 2010 or above with vba and macro enabled (file originally made with Office 2010 Excel)

 

 ===================================================================

Updated 16 Feb 2018 

Note: I have disabled comments since your emails are seen by others publicly. I was intending to have discussions from here but what happened was that others requested that I email my Inventory Excel to their posted email. Do note that you can always download whatever you need from here upon the links provided and protected by Google Drive. No need to request from me anything. I did so in order that you understand what you are downloading and use it appropriately. My apology to those who are advertising their own Inventory here. But if you find it difficult to understand my discussions below, I may opt to do another video tutorial in the future and disscuss everthing from there, God willing. Thank you very much. My apology to those who would want a video on how I made the Excel File itself. I just knew you want such, and am organizing my time and effort to do such a video in the future, God willing.

Highlight Video of my New Product Price Inventory (please enable macros) and Cash Inventory dahil wala kayong Point Of Sales
*please download and open with Excel
**this is free, I just ask some understanding as to its being untidy since you'll understand how you can develop and create one of your monitoring system yourself as to what you need

***I was not able to include in this video how debugging dependencies to avoid accidentally changing records so please watch my old video below for that.

In my last reading of a BIR pamphlet, hiding a cash register not duly approved by BIR is an offense.

Now there are tutorials out there to make even your video cam a barcode scanner and excel as the barcode manager.

However that is too much technical work for Juan. My next concern for that will be the speed at which we're discussing here convenient store should be. Scanning a one peso chocolate is somewhat hilarious too. Then I had the idea to just create a touch screen based apps for android where I would just tap the chocolate name and it's accounted already. But how about bulk buyers where they're in a hurry, but you still have to search the name, touch the screen, and put the quantity bought? Good riddance I am not going to give you these solutions. For small to medium scale businesses perhaps a good affordable Point of Sale system is what you need, and I tell you to grab that instead of plunging to headaches of technical researches that can drought your patience tank. Sari-sari Stores in the Philippines are considered micro or even nano on the other hand. For Restaurant owners or RTW or those that has less inventory and quantity of goods, I think you should choose from among free android apps that's suited for you (some have picture capabilities) since one or three touch on the screeen is all you need to record the transaction or barcode scanner using the android camera.

But for Sari-sari Store, click here to download my "tubo" inventory or gross income excel calculator and a lot more (if you care for big data analysis blah blahs). (For non-Filipinos, you can request me for an all-english headings).  Below are sample data entry video. Near the end there's a debug activity for a good file saving and not be change later by dependencies or formulas.

The .xlsm file have samples inside you can cut before you start your data entry, then reencode the OrderID column to reflect the opposite side row numbering which can help aligning your data. It is natively excel and can be easily edited for your additional needs too.

OtherID sheet will have to be updated if you want some more other data analysis blah blahs.

For additional questions about the the .xlsm file I am giving here for free, you can email me. But let's discuss things here on an answer per question basis so that others can catch up too. You can nevertheless create two price list one for per pack and one for per piece selling if you're that patient. Mostly however like I do, I always indicate and record per piece price since my store is just micro or nano, that's why this is suited for micro Filipino Sari-sari Store owner who are tech inclined.


Some features of the .xlsm file:
-can compute per year/day gross income
-can analyze top/low selling products, top/bottom earners via sorting
-with some additional entry can account for back orders and discounts and freebies at year end
-can compare prices of similar goods among dealers
-can display price adjustment according to 12% BIR suggested price interest (so that we don't complain that we're paying high taxes when we're not earning much)

Weakness:
-doesn't incorporate expenses computation (you have to deduct your business expenses with your gross income)
-isn't an exact inventory since it may not account for "tingi" basis just per pack selling only or whatever you indicate

Take note that at closing time you have to create another excel to record your cash, expenses, and purchases just for monitoring purposes every once in a while or at targeted periods to capture a sales generalization or averaging. Such will help you have a real sales inventory where you can multiply by your earning's percentage to forecast earnings per year and plan ahead how much money you will get from the store to be deposited in bank as personal income, of course less your daily expenses.

Nevertheless we have unburdened ourselves of such an added process of detailing it too much like clicking on the android or computer for every transaction or listing detailedly by hand every bought goods. Such is taxing that modern man had unburdened himself from it and created the modern POS system. More so for Juan who more often owns the family business or is the lone store owner and employs no one to help him that such additional work is too much. Such taxing accounting system is best left at the hand of automation so that Juan can focus on his better relationship with customers.

For all that, here is an allinone excel not-so-detailed listing of sales to do that. (Here is a more detailed sales listing if you can for a while or whatever.) Download it to see the formulas. The combo boxes drop down list autocomplete may or may not work, please redo. I used Microsoft Excel always so I don't know if it has compatibility issues with other datasheet like Libre and Kingsoft. It's somehow messy but you'll get my idea (email me for a video explanation if you want), which includes inside smart, globe, load income calculator combined with the higher income from other sales, randomizer to create receipt for BIR bookkeeping if your store is too fast paced that buyers aren't asking for receipt anymore, and another sheet that compares listing of sold goods with cash count at day's end (it means you can't perfectly record every sales transaction given the nature of a sari-sari store fast paced setting) to monitor if cash is lower than your listing which can mean that something is wrong like theft, wrong cash payment to purchases, or a missing purchase receipt has not been recorded. Falsification of receipts amounts to an offense and more if what you're recording is lower than your real sales. And so is not issuing receipts per day. So the real legal offense is not to have receipt issued for those who asks, or BIR did a surprise visit and you're caught without receipt issues and books, or your issued sales and purchases are way lower than real. It is the right of buyers to have receipt when they buy, so better issue those always especially if you're not a fast paced store or you're a specialty store like computer sales, boutique, etc.

So where is the morality here, since we're also discussing from a spirituality standpoint. Well, POS system for nano stores is yet being developed. Just like we like to have electric cars to do away with smoke, but we'll have to wait. There is no exact accounting as my CPA Uncle tells me. But do your part. You can lessen buyer's burden by offering lower priced goods with less income for you of course if you're declaring lower sales. Precisely maybe you want to earn higher for improving your building and giving your customers better services like adding sitting area, or cooler and dust free storage and environment to protect goods, so it's like giving back. You can tithe of course and give to charities to amend for paying less than your real sales. The reality is that if you mark up your goods higher than the competition, no one will buy from you, and you'll have no choice but to close the store. It's what some insensitive BIRs always suggest which is to close you're store if you're not earning much to pay them correctly. So a moral dilemma is to mark up highly and pay correct taxes which ends up closing the store, or mark up competitively but declare lower sales which saves your store from closing.

Anyways, this line of argument always ends up for a need for a POS system obligatory for all. And anyways, for the sake of example, a toothpaste sachet still has to arrive at it's intended end user along the way from the manufacturer, wholesaler, etc. down to the end user without incurring so much tax and mark up, thereby empowering Juan to buy it, all the way so that nothing would smell funny. There are even states that carry such burden so as to feed the well being of their citizen, not taxing basic necessities. If we can by private and by government cooperation we can alleviate the hard earned piso of Juan even the system isn't that perfect.

So the conclusion is that without a POS everything is just an estimation. And of course we tried to create an excel system that does without a POS estimating maybe with about 90% or above accuracy. With a side note that a POS system like android affordable with scanner should be developed for nano sari-sari stores all over the Philippine island, of course uniformedly. I mean no one will buy from a store with additional BIR burden percentage when another near store doesn't have that. So if a POS system Philippine style will work it should be obligatory for all. And we're not yet painting the story with those who set up POS system with BIR permit that declares less than the real sales...so the dillema continues...and sorry to say that it's not within my radar's capability to discuss anymore...

O Lord teach us to build better systems that gives justice to all, temper us to not hoard so much profit at the expense of others, and teach us to live modest lives satisfied with what we earn. Jesus have mercy!


Q&A:

1. Why should I bother entering data to computers? It won't even increase my income.

It may not increase your income but it would save you from loosing your customers due to high pricing or from loosing your mind due to too much computational work with meager income not even enough to buy you food.

2. Is BIR Philippines really enforcing the mark up pricing?

No, but they are asking for a higher tax return always as they see fit according to the capacity-to-buy tax system, which they are transfering their power to collect from buyers. That's how I understand it from the BIR speakers. And so we, as sellers, are forced to increase our earning to make up for it.

3. Should I then exactly markup all my products with 12%.

No, but you can average your mark up, and if it is higher than 12%, it means you don't need to increase the price of your fast moving goods (which may have lower than 12% markup most probably) just to increase your earning from it since your other goods are marked up higher than 12%. I've added these monitoring on bentaID sheet.