Thread: Need tutorial for basic sales spreadsheet

I run the medical equipment department for a small drug store. What I want to do is set up a spreadsheet that will keep track of sales of DME items in our store by month.

The columns would be as follows:

A. Item(or RX) # - (this is descriptive-to tell me what the item is)
B. Item category (A, B, C. etc)
C. Cost - what it cost the store to buy/replace it
D. Paid - the reimbursement from insurance or private pay - what we got for the item
E. Gross profit -column d minus column c
F. Margin percentage - the percent increase between what we bought it for and what we sold it for

That's the simple part. Now here's where it gets tricky:

I would like to be able to calculate an approximate NET profit taking into account the fees we are charged for submitting claims to insurance. There are different categories for the items, and the category will determine what kind of fee we are charged. These are the categories:

A - equipment primary/secondary claim - $5.50
B - equipment claim, primary insurance only - $3.00
C - drug primary/secondary - $8.25
D - drug - primary only - $4.50
E - no insurance claim (private pay) $0

So, depending on what letter I enter into the CATEGORY column, the spreadsheet should subtract the indicated amount from the GROSS PROFIT, and place the result in a new column, COLUMN G - NET PROFIT.

Is this even possible??

I would also like it to keep a running total (MTD as well as YTD) that I can glance at any time. The month to date and year to date should tell me the same thing as columns A-G above, but it should be a running total.

And, if possible, would like to be able to see an entire year on one spreadsheet, rather than having a new one each month. Problem is, there is no way of knowing how many rows I will need for each month. I might need to add rows if I run out of room.

Any advice, and/or point me in the direction of a good tutorial website, is much appreciated!

Sean Wildman

Do you want a mTD and YTD for each item or just for the store?
So there will need to be a column for Date and for Net Profit. Yes?

See attachment. Sounds like you want to learn (not sure of your current level) so here's what I did.

I protected the spreadsheets without password. You should always do that unless you are editing formulas so that you don't accidently screw up a spreadsheet.

1. I added a date column as it makes doing MTD easier.

2. I used a Table on a different sheet(which is referenced for Net Profit) using VLOOKUP. So if your return values change, just change them in the table and that will automatically modify the equations.

3. I have used Freeze Frames so that as the dates scroll down, your headers will always be visible.

4. I used the LIST functionality of Data Validation so you can choose from drop down menu for Category.

5. I used Conditional Formatting so that the borders always fill in for the next row when you start a new row. So there will always be a blank row in your form.

6. I used Dynamic named ranges so that as you add data, the MTD and YTD and Print Area will expand See http://www.contextures.com/xlNames01.html#Dynamic
With the way I set up the ranges, you CANNOT skip rows or that will screw up your MTD, YTD and Print Areas.

7. I set up Data Validation on Columns A and B so that you cannot skip rows. (Try entering something in those columns with skipped rows).

8. I added a calculation for Previous Month (just so you can compare)

9. I used SumProduct for Previous Month and MTD. Lots of information on that Function in this forum or on internet.

Is this what you are looking for?

Ack, having trouble uploading. Will figure out why and upload shortly

I had formulas in Columns F, G and H down to the bottom and had to clear them after row 20 to get the file to upload. You can grab all three columns starting in row 20 and drag down to make it a complete sheet. Let me know if you have questions.

Note: Just noticed that I didn't unprotect (unlock) Column A cells. You'll need to do that. Also, set it up such that you'll need to start a new sheet for every new year, so save 2010 as 2010 and then clear all the data and store as 2011 and so on.