Excel Spreadsheet Design

Need idea and example to setup MS Excel spread sheet to calculate costs, of software licenses for (4) different database components based on number of processors in each system.  Also need to calculate these licensing cost associated with (5) different contracts. There are approximately 60 systems.  Need to organize the following data by contract.

     Example data:

Name      System-Type      Processors      Licenses             Contract
SyS-A            M                       6                     3                   B5
SyS-B         MM                     12                     6                   L2
SyS-C            L                     12                     6                   L4
SyS-D          EN                     12                     6                   L5
SyS-E            F                          6                     3                   L6

Product          Quantity             List Price      Discount Price        Support      Cost
XA-Adv               49                        3,550                1,420                     312          1,732
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Sdruss,

Looks like you have already laid it out, so presumably that format works for you.

What exactly is not working?

Maybe best to post a copy of the actual workbook (change numbers or names if confidential), and identify what you want to calculate, where you want it, and how to do that (the latter being in words, assuming you need help with the formulae).


Roy CoxGroup Finance ManagerCommented:
You need one table of component parts and their costs, similar to the small tables in your post.

Then on another sheet designed as a costing sheet that populates the prices, etc using VLOOKUP and yo enter the quantities and formulas calculate the cost
sdrussAuthor Commented:

I would like to some how combine the 3 sheets in the workbook into a master single sheet.  Need to present to bosses who have very limited attention span.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
To start with the data needs to be in a table format,i.e no completely empty rows or columns within the data.

What is the relationship between the sheets?
sdrussAuthor Commented:
Roy, can you explain or give an example of table format --I'm not an experienced spread sheet guy.  Only second spreadsheet I have attempted in my life.  Hope this helps.  Looking for a final report, that presents total cost on each contract to purchase product licenses.

The relationship between sheets:  

          1)  Sheet 1 is probably the grand totals for each contract (e.g. contracts being, B5, L2, L4, .....) .  I say probably because I don't know if there is a better method to present the information

          2)  Sheet 3 determines how many of each product (e.g.  SA-Adv, XA-Diag ...)  on what systems

          3)  Sheet 2 calculates the cost of each licenses based on the number products required on systems.
Roy CoxGroup Finance ManagerCommented:
I'll take a look later
sdrussAuthor Commented:
Roy, okay thanks.
Roy CoxGroup Finance ManagerCommented:
I'm not exactly clear what you mean, but this is what I think you need.
Components Sheet
Components cost prices are listed in a table format in this sheet.
Costings Sheet
The quantities of each component required is entered into  the table on this sheet, the green cells contain formulas to lookup the cost of components and multiply by the quantity required.
sdrussAuthor Commented:
Thank you Roy!  I am very confused, but I will take sometime and try to decipher.  With your example do I get total costs of all licenses required for each contract?
Roy CoxGroup Finance ManagerCommented:
I'm not sure exactly what you are wanting but I've added some notes. Post back with specific questions and I'll be pleased to help.
sdrussAuthor Commented:
Roy, how would I know that the data in the "Component Cost" sheet is in a table  -- other than you told me?
sdrussAuthor Commented:
Roy, I updated sheet 1 and renamed as Contract, what I had before wasnt correct.  Hope this new information adds some clarity on what I need.  Need tally per contract.
Roy CoxGroup Finance ManagerCommented:
Sheet1 in the new workbook has not been renamed.

I still do not understand what the data on that sheet is. What is B5, etc referring to and where does it fit in the costings?

Roy, how would I know that the data in the "Component Cost" sheet is in a table  -- other than you told me?

In that sheet B5 to C9 is the only data that I can see to use. I assume it refers to the cost per component. The unused columns seem to simply be applying a discount.

It is referred to as a table because it has a header row defining the column contents and there are no completely empty rows or columns within the data.
sdrussAuthor Commented:
Roy, got it .... it is a table because it has header and no empty rows.  Thanks for you patience.   The data on the "Contracts" sheet associates which system belongs to which contract (e.g. this are contract names, B5, L1, L2, etc..)OEM-Master-License-Cost-Query-from-.xlsx
sdrussAuthor Commented:
One more update.  Probably reversing engineering-wise the "Contracts" sheet is my ultimate goal.
Roy CoxGroup Finance ManagerCommented:
I'm not really sure what the relationship is with the contracts like L2-Contract

I have added formulas to pull the costs that you have entered manually. Should each row in the contract columns contain a value from the costings?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdrussAuthor Commented:
Think we are going to get a close as we can get.  Really appreciate your time.  Thank you.
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.