more-arw search

Q&A Forum

Best design of a spreadsheet for layering of four different projects into one projection timeline

I am in the process of building an Excel spreadsheet to project cash flow for four distinct projects that will make up one cash flow projection. The product to be delivered for each project is the same, but the number of units of product delivered to each project each week and the total number of units delivered to each project will differ.

Each project has a different start-date (as yet unknown) and the units of product need to be ordered with a six-week lead time. Each project has a different duration. When projects occur simulaneously we will need to smooth production as best as possible to keep deliveries to each project steady.

Has anyone designed a similar spreadsheet who might have some helpful hints on how to optimally design this, or a good formula or two that you found particularly helpful? Do I build four different projections and then aggregate them or is there a better way to do this, perhaps by arranging everything in a lookup-table format? 

I'm just hoping to get some ideas from anyone who might have taken on this sort of project in the past, to see ways in which I might approach it.

Answers

Mark Von Der Linn
Title: Principal
Company: www.VDLconsulting.com
(Principal, www.VDLconsulting.com) |

You could do this using a database approach with pivot table reporting, but that gets a bit advanced. The simplest approach is probably to build a template that accommodates all projects. Make 5 copies. Use one of them to consolidate values from the other 4. Keeping them all identical in terms of rows/columns makes building/managing much easier and more reliable. Look into Consolidate function (Data menu in Excel 2007). Keeping them identical is easier if you use multi-tab-editing (select all tabs when adding a row, for example). Using Range names for things will also help with keeping your formulas tidy and accurate.

2393 views

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.