[Sean Wildman]

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

[Advertisements]

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??

Yes... if 'letter in category column' = a do this; if 'letter in category column' = b do this; otherwise do that; etc.

Check out "IF" statements.

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.

Running totals can be made by dates.

[Vino Rosso]

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??

Yes... if 'letter in category column' = a do this; if 'letter in category column' = b do this; otherwise do that; etc.

Check out "IF" statements.

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.

Running totals can be made by dates.

[Sean Wildman]

Running totals can be made by dates.

So, I would create a MTD column and tell it to calculate the total from another column, for only entries with 8/1/10 - 8/30/10 ? And the same for year to date?

How do I solve the problem of being able to see a YTD total on the same spreadsheet as every month? Think this may just be a page formatting issue. Like, is there a way to put the "totals" area (MTD and YTD) off to the side in their own frame, and have that part stay on the screen no matter how far down I scroll?

Don't know if I said this yet, but I'm using Excel 2010.

Edited by Sean Wildman, 10 August 2010 - 04:06 PM.

[Vino Rosso]

As ever with Excel, there are various ways to solve a problem... one way that might solve your problem is to use SUMIF. SUMIF(range,criteria,sum_range).
So with the dates in column X and the items to total in column Y, =SUMIF(X:X,>=mm/dd/yy AND <=mm/dd/yy, Y:Y) [depending on your date settings]