What is better than Excel for costing a project?

[Ampa]

I am trying to cost my board-game project...

• There are four items to be manufactured - a board, a box, some cards and the rule book.
• Each of these items can be made in a variety of ways - eg a rigid box or a carton box, a folding board or a flat board.
• Each method has fixed costs and variable costs, depending upon number of units manufactured
• Each of these items can be made by a number of companies.
• Each company has their own minimum order size, perhaps 500 or 1000 units.

I need to collate all this information, so that...

• It is clear which component(s) should be made by each company.
• What the unit cost will be.
• What the total project cost will be.

I am using Excel, since it is the only spreadsheet that I have any experience with, but I am startled at how convoluted it is to present this complex set of variables in a meaningful, and approachable way; and how difficult it is to swap the numerous different options in and out of the equation.

It may well be that I do not know the most effective ways to use Excel, or it may be that there is a better tool for the job; after all I can't possibly be the first person to be engaged with this type of costing operation!

Can any DCers suggest...

• A tutorial / example that shows how to use Excel to simplify my task.
• A better piece of software for solving this problem.

Thanks,

Ampa

[AndyM]

- It is clear which component(s) should be made by each company.

Is this is really one analysis done four times (once for each item) or are there potential savings by using one manufacturer for more than one item?

- What the unit cost will be.
- What the total project cost will be.

As you said, this depends on variables, particularly volume.  Are you trying to find the least expensive total outlay, placing no more than the smallest minimum orders?  Or are you willing to spend a little more to minimize your unit cost ordering larger quantities when there is a volume discount? 

Excel is the tool I would use for this sort of analysis, but the utility of Excel does depend on how much time and experience one has with the tool.

Yours is the sort of project that could provide for a good learning experience if you have the time and inclination.

[katykaty]

You're doing two things here.

Firstly, looking at a set of data from several different perspectives. Pivot tables are ideal for that. There are lots of good tutorials on how to use pivot tables - for example http://www.ozgrid.com/Excel/excel-pivot-tables.htm or http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx

Secondly, testing 'what ifs?' for different unit sizes For this, the Scenario Manager is what you want. Again, lots of guidance on the web, have a look at http://www.contextures.com/xlScenario01.html

[Ampa]

Thank you both for the reassurance that I'm using the right tool - now I just need to learn how to use it!

I am sure the tutorial will prove useful.

Still finding the Excel interface rather clunky though!

For example... I want to name a cell "MyTotal", which I can do easily by entering the name, in the cell reference box top left. So far so good, but now I need to rename it. Rather than simply edit the name in the cell reference box, I have to...

1. Switch to the Formula tab on the ribbon.
2. Open the Name Manager.
3. Select the name I wish to edit, and click Edit (or double click)
4. Make the change - "MyTotal" to "MyTotalNoTax"
5. Click OK
6. Click Close

Why couldn't I simply edit the name in the cell reference box, where I entered it in the first place? After all I can edit formulas that way.

And even worse, if I do try to edit the name in the cell reference box it appears to work, but has in fact created a second reference to the same cell under the new name. I mow have formula referring to the old name, but only the new name is displayed when I select the cell.

Surely a simple pop-up could have asked me whether I wanted to rename or create a new name, rather than employing a six step multi-gui procedure for a very simple operation?

Ampa

[AndyM]

Switch to the Formula tab on the ribbon

Ah "ribbon", which means you are using Excel 2007.  I'm not yet (some day) but it doesn't sound like the range naming procedures have been improved.  Too bad.