Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Tutorial needed for sales spreadsheet


  • Please log in to reply

#1
Sean Wildman

Sean Wildman

    New Member

  • Member
  • Pip
  • 2 posts
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
  • 0

Advertisements


#2
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts

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.
  • 0

#3
Sean Wildman

Sean Wildman

    New Member

  • Topic Starter
  • Member
  • Pip
  • 2 posts

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.

  • 0

#4
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
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]
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP