Need help with improving a planning worksheet.

[NihilRex (OP)]

  I need some help with two different projects to make a useful Aurora planning tool, which I will share freely.  (Yes, I get the irony.)

• Turning my single use Squadron PreProduction Planning Sheet into a more general tool.
• combining my sheet with someone else's Missile Planner, I got from http://aurora2.pentarch.org/index.php/topic,5855.msg60061.html#msg60061

  My sheets currently allow you to :

• Enter several component types attributes for reference.
• Enter missile Range\WH\Sensors\Speed for reference
• Calculate combined missile salvoes for Time on Target for different speed missiles.
• Enter counts of up to 18 different components for up to 3 different classes of ships that shre those 18, and get the total number of each component to prebuild, and
• Given a %of your total industry available, the percent to assign to each component to have them all finish on time. This one allows you to vary the # and cost of each component, but not yet the # of each kind of ship. (4,4,1 currently)
• Enter the desired end facilities of a colony, cargoship capacity and ship numbers, and find out how much minerals to ship in to have enough to build it all, as well as how many of each mineral in each trip. This is basically done.
  

  Currently, Im having trouble turning these disparate sheets into one Planning tool. Can anyone provide advice\help?

  What Id like to end up with is that you enter components as you design them, select each in a dropdown listing for each ship, somehow save those ship invoices, then go to the squadron sheet, enter how many of each ship, and get the preplanning #s and percents, with a spot to enter on-hand numbers for each component to cut down on overproduction, while also incorporating a version of the missile planner.

File is in Excel 2010 format, zipped.

[NihilRex (OP)]

No comments?

[DuraniumCowboy]

I am busy in the short term, but I like messing around in excel.  Give me a little time, and I'll look it over and let you know what I think.  Later - DC

[ComradeMicha]

I'll also take a look at what you got here this weekend, of course without any promises. 

Generally speaking, are you okay with macros or do you need this in pure excel functionality (possibly including pivot tables)?

[ComradeMicha]

Okay, I put together a first version. 

Features:

• Add up to 1000 components
• Add up to 100 ship classes
• Define component usage per class
• Calculate # of factories to use to build enough components taking into account components at hand

I don't think there's a really easy way to do the dropdown thing.  I tinkered around a bit but without macros / pivot tables, it's just messy.  Frankly, you'd be better off using a DB like MS Access for that.

This seems to be the most efficient way to do the component production calculation.  I would keep the component details (tracking speed etc) in a separate tabsheet, as they add nothing to the production planning part.  What could be integrated is the missile and salvo planner.  Maybe I'll look into that later.

Please tell me what you think and whether I should keep going in that direction.

[ComradeMicha]

Well, I was in a good Excel mood tonight so I also added the missile/salvo calculator. 

It does the same as your old version, but you can calculate up to a thousand different types of missiles for a salvo.   It automatically determines the slowest missile in the salvo and calculates the delay for all faster missiles.   I also added a section for manually adjusting the delay, so you can fine-tune your salvos for more complex scenarios.   It will then tell you how long the missile will be in flight (to compare it to endurance) and when it will arrive if both your task force and the target remain on course and at current speeds. 

What is missing is multi-stage-missile separation time calculation and some estimation on how many missiles are expected to hit dealing how much damage (ignoring point defence). 

I guess I could also redo the terraforming stuff but I am much too tired right now

Good night folks!

PS: Of course I only changed the last two worksheets, "builds" and "missile and salvos".  All other sheets may be deleted without breaking anything there. 

[NihilRex (OP)]

Have not made much testing progress, as I am out of town, but wow, looks awesome!

[NihilRex (OP)]

Added a %Available modifier, and a group column.  The group Column makes sorting so much easier.

The Reference sheet is for entering components so you can know what something is without having to load it into the ship editor, and also holds a table of the minimum terraforming needed to make Sol bodies breathable.

Let me know what you think!

[Cocyte]

Too bad I'm still using excel 2000, else I'ld gladly give a hand instead of going on updating my sheet

(I just updated the missile section to allows multiple sensors configuration - and mostly accurate sensor range calculations)

EDIT : updated it again to add component pre-build for up to 3 different ship classes at once - you might be interested to check it out to makes yours more generic