Cash register spreadsheet

[tsameti]

First of all apologize if this is not the right place to ask for this sort of help.
Can anyone help creating this simple spreadsheet.
I have created one myself but I can make it work just the way I want it.
In the end I had to simplify it to the point where I enter almost all data except for calculation and even that stops if the item number is higher than 2.


=IF(B35=1,B35*27.5,IF(B35=2,B35*27.5))
This is as far as I went and I cant make graphics work either, as for now I simply change color on Item QTY manualy to know what was sold.

 

[oldbrewer]

put this formula into C35

=27.5*b35

because

=IF(B35=1,B35*27.5,IF(B35=2,B35*27.5,"something else"),"something else") is a silly way to acjieve the same thing....

unless you define what you want to happen if say B35 = 99

 

[tsameti]

I created this file a while ago and I think there was a reason behind that formula because I wanted to have a drop down menu in Item section, where by choosing the Item it would choose the right price. I couldn't make it work but I left the formula and by looking at it your comment makes sense.
So there should be a formula if Item is parcel tape, sale price is 1.75*quantity-cost price*quantity=running profit.

item	quantity	sale price	cost price	profit	running profit
parcel tape	1	1.75	0.85	0.9	0.9

<colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

however the "item" is selected, a lookup returns the price, naturally if I buy 2 the price is price * 2

on a real cash register there is a button to press when all items purchased have been entered. this would be a macro to fill out cost price and profit columns and to move cursor th next blank cell ready for the next customer

 

[tsameti]

oldbrewer, I don't know excel, this is why I asked for help. I do know that I need to use formulas and macros this is why I'm here.

If it's too much of a trouble just say so because your post above doesn't help.

 

[oldbrewer]

Friend - I am trying to design you a foolproof cash register simulation - see next post in 30 seconds...

you will not need to know excel and formulas but I advise you try to understand what is happening...

 

[oldbrewer]

BWORDS

AWORDS

ALIST

BLIST

BIROS

BWORDS

ARTPAPER

BOOKS

ARTBOOK

BIROS

AIRBRUSH

BANNERS

CELL A1 IS A DROP DOWN CHOOSING EITHER AWORDS OR BWORDS

E1:E2 =

STOCK

HERE BWORDS HAS BEEN CHOSEN

CELL B2 IS A DROP DOWN SHOWING THE LIST OF BWORDS FROM WHICH BIROS HAS BEEN CHOSEN

IF AWORDS HAD BEEN SELECTED IN A1 THEN B2 WOULD BE A DROP DOWN OF THE AWORDS

IDEAL FOR A CASH REGISTER WHERE ONLY IN THIS CASE BIROS WOULD APPEAR ON CUSTOMER PURCHASE LINE

COST AND PROFIT MARGIN WILL BE ADDED ONCE THE SALE COMPLETE MACRO BUTTON IS HIT

if you are happy so far we can take it further….

<colgroup><col><col span="3"><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[oldbrewer]

BWORDS						AWORDS		ALIST		BLIST
	BIROS					BWORDS		ARTPAPER		BOOKS
								ARTBOOK		BIROS
								AIRBRUSH		BANNERS
					CELL A1 IS A DROP DOWN CHOOSING EITHER AWORDS OR BWORDS		E1:E2 =	STOCK
					HERE BWORDS HAS BEEN CHOSEN
					CELL B2 IS A DROP DOWN SHOWING THE LIST OF BWORDS FROM WHICH BIROS HAS BEEN CHOSEN
					IF AWORDS HAD BEEN SELECTED IN A1 THEN B2 WOULD BE A DROP DOWN OF THE AWORDS
					IDEAL FOR A CASH REGISTER WHERE ONLY IN THIS CASE BIROS WOULD APPEAR ON CUSTOMER PURCHASE LINE
					COST AND PROFIT MARGIN WILL BE ADDED ONCE THE SALE COMPLETE MACRO BUTTON IS HIT
					if you are happy so far we can take it further….
date	sale #	item	sale price	cost price	profit	running sale price	running profit	running cost	%profit on sales	running %profit on sales
01/07/2016	1	biros	2.99	1.75	1.24	2.99	1.24	1.75	41.5	41.5
01/07/2016	2	artbook	9.99	4.5	5.49	8.48	6.73	6.25	55.0	51.8
01/07/2016	3	banners	3.75	1.85	1.9	10.38	8.63	8.1	50.7	51.6
			each time you hit the sale complete button one or more rows representing purchases will be added to your daily shhet

<colgroup><col><col span="5"><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[tsameti]

Thanks.

Cash register will be used only for home use purposes and is designed for me to keep the track of the item range and see weekly profit/cost.

Let's say I have 4 different Items for sale at the moment. Would I still need AWORDS and BWORDS?

Instead I could choose sold Items from drop down menu (if it can be done by using keyboard, I believe data entry will be faster).

The spreadsheet you replied with in the second post, is something similar I have in mind. I can't picture it being more simple.

 

[tsameti]

I reckon this formula will work for me.

date	sale #	item	sale price	cost price	profit		running profit
01/07/2016	1	biros
01/07/2016	2	artbook
01/07/2016	3	banners

<tbody>
</tbody>

I dont want to inconvenience you by writing down extra formulas with percentage as I might not be using them.

Instead I wanted to see the graph of Items sold throughout the year and see the trend what's in demand and what months/weeks are busy so I can have enough supply for the future.


Cheers.

 

[oldbrewer]

the dependent drop downs were because I thought you were a shop

how many sales of how many items per day

no problem doing profit analysis it is automated