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
XB-Diag
XC-Perf
XD-Part
LVL 1
sdrussAsked:
Who is Participating?
 
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?
OEM-Master-License-Cost-Query-from-.xlsx
0
 
AlanConsultantCommented:
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).

Thanks,

Alan.
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdrussAuthor Commented:
OEM-Master-License-Cost-Query.xlsx

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.
0
 
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?
0
 
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.
0
 
Roy CoxGroup Finance ManagerCommented:
I'll take a look later
0
 
sdrussAuthor Commented:
Roy, okay thanks.
1
 
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.
OEM-Master-License-Cost-Query--1-.xlsx
0
 
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?
0
 
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.
OEM-Master-License-Cost-Query--1-.xlsx
0
 
sdrussAuthor Commented:
Roy, how would I know that the data in the "Component Cost" sheet is in a table  -- other than you told me?
0
 
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.
OEM-Master-License-Cost-Query-from-.xlsx
0
 
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.
0
 
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
0
 
sdrussAuthor Commented:
One more update.  Probably reversing engineering-wise the "Contracts" sheet is my ultimate goal.
OEM-Master-License-Cost-Query-from-.xlsx
0
 
sdrussAuthor Commented:
Think we are going to get a close as we can get.  Really appreciate your time.  Thank you.
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
All Courses

From novice to tech pro — start learning today.