Excel as a Simple Point of Sale application?

[john744]

Hello everyone........ My name is John and I'm new to this forum. I used to think I was pretty good with Excel til I came here and realized there's a lot under the hood that I never fully understood or realized was there. Having said that, I'm working on a little project for my cousin that I thought would be straightforward and simple. It's not, at least not for me. So after seeing the vast compliation of knowledge here, I thought I might see if I could garner a few tips and pointers to take me in the right direction. Any suggestion would be greatly appreciated.


I have a rather simple task which has become more of a challenge than I imagined. My application is similar to a Point of Sale (PoS) system, except it's very limited. To be specific, I'd like to use a simple PC running MS Excel 2003 with a Bar Code reader, numeric key pad, and receipt printer.

My cousin has a small cafeteria where he would like to accept Loyalty Cards from a single client (up to 500 cards) and essentially subsidize the purchase and offer a discount to the customer. The customer will pay 50% of the purchase price at the point of sale. The Client will subsidize 25%, paid weekly by summary statements. The cafeteria will essentially discount the purchae by 25% (excuse the pun - but he will eat the 25%). And it works in that order, that is to say a prurchase of $10 would result in the customer paying $5, the client paying $2.50, and the cafeteria absorbing (or discounting) the remaining $2.50.

My intitial thought was this is rather simple & excel should work just fine. Each transaction would be recorded on a seperate line or row (record).


Column 1 - Barcode: (TEXT Alphanumeric)
USER INPUT Essentially a card ID number, the app is waiting for input from the barcode scanner. The scanner would have a postamble TAB configured. Scanning the barcoded loyalty card would then enter the card ID data into column 1 and then tab to the next cell. The barcoded data need only identify the card for reporting - no customer look-up is necessary. (of course, that would be a nice feature, but beyond the scope of what I'm trying to do)

Column 2 - AMOUNT OF SALE: (Currency)
USER INPUT This variable amount would be entered by the cashier using a numeric keypad and then pressing the ENTER key. (I thought about this for a bit - Do I want ENTER (CR/LF) or do I want TAB) Since I only need 2 pieces of information, the barcode data and the amount, I hope ENTER (CR/LF) will be fine, returning to cell 1 on the next row.

Column 3 - Date/Time Stamp: (Date/Time)
NO USER INPUT Calculated field - pretty straight forward, the current date and time when data is entered into column 2. (would NOW() work?? In my feeble first attempts, ALL the Date/Time entries kept updating when a new record was entered)

Column 4 - Amount: (Currency)
NO USER INPUT Calculated Field =column2 (same value as column 2 just carried over)

Column 5 - 50% Customer: (Currency)
NO USER INPUT Calculated Field column4*0.5 (value in column 4 x 50%)

Column 6 - 25% Client: (Currency)
NO USER INPUT Calculated Field coulmn4*0.25 (value in column 4 x 25%)

Column 7 - 25% Cafe: (Currency)
NO USER INPUT Calculated Field column4*0.25 (value in column 4 x 25%)


After each transaction, a printed receipt is required (automatic function) on a standard 40 column receipt printer. The receipt to contain Date, Time, Barcode data (card ID), Total Amount of Sale (100%) and the Amount Paid (50%) by the customer. A header containing the Cafeteria Information and a Thank You Line! (Reprints.....REPRINT LAST ??.........hmmmmmm don't know about this or if it's needed)

A pretty front end displaying only the barcode and amount cells. I plan on using a used Radiant P1550 15" touch Screen display/cpu. The Pretty Front End is to keep it simple for the cashier.

I do see a need for an autosave function, perhaps after each transaction. I'd hate to lose a "batch" of entries due to ..................... whatever. And I'd have to make sure that I always enter data after the last entry so as not to overwrite and previous records ...............arghhhh!


Now, I realize this is not as secure and robust as it could or should be. It does not address the issue of multiple cashiers (log-ins), or correcting errors, audits, overrides, etc., etc. But my thought was to keep it simple. By using excel, generating statements and reports should be straight forward using the date/time stamps to define reporting periods (daily or weekly summary for billing), and summing the totals is easy. Although there is only 1 client currently interested in doing this, I thought I'd use a alphanumeric barcode with the first 3 alpha 1characters identifying the client followed by 6 numeric digits.


Thoughts ..... Suggestions ........... Is this way more complex than I imagined?