Corporate desktop and laptop refresh model
Assumptions and Background
Introduction
This tool allows the executive team to gauge whether chosen projects will fit team capacity
It's intended to be used primarily as a rough estimating tool, not as direct resource allocation. Several possible models are provided.
Prerequisites
None. No macros or special add-in functions are used.
You need to gather information and sizing estimates on your proposed projects first
You need to confirm the rough sizing (in hours) for each category of project, and enter this information in the "Estimating Code Values" block
Steps
1) Enter/confirm your assumptions in the assumptions block of the spreadsheet tab you've chosen as your desired model. Green fields need entry.
2) Determine the total number of available headcount in each relevant area and enter that into the green fields
3) Play "what if" with choosing projects, per your other criteria (e.g., ROI, risk, etc.) to make sure the total portfolio does not exceed capacity
Maintenance
This spreadsheet is simply a template, and will require modification for your situation
Weaknesses/Still To Do
Background and Assumptions
08/12/2019 Page 1
432974792.xlsx
Total
Hours for Include in Hours for
Estimate Project Final Total? Final Sum
All Proposed Projects
Project Artichoke S 200 x 200
Project Banana M 700
Project Cauliflower L 1,500 x 1,500
All estimated Included
projects projects
Projects presented & estimated 3 2
Total hours 2,400 1,700
Total work months 17.9 12.6
Total heads needed for period 6.0 4.2 OVER CAPACITY
Heads on hand (approximate) 4 4
Hours available from heads on hand 1,613 1,613
Assumptions:
Estimating Code Values
Code Work Hrs
S 200
M 700
L 1,500
Other Assumptions:
Productivity % 80%
Work hrs/mo 134
Months/period 3
Classmates Online Confidential 08/12/2019 Page 2
432974792.xlsx
Total Include
Hours for in Final Hours for
Arch Dev QA Operations Project Total? Final Sum
All Proposed Projects
Project Artichoke S M S M 500 x 500
Project Banana M M S M 650 x 650
Project Cauliflower M L L L 2,600
Project Dillpickle S M S S 350 x 350
Total hours estimated 500 1,400 950 1,250 4,100 1,500
Total hours included 300 600 150 450
Total work months included 2 4 1 3
Total heads needed 1 1 0 1
Heads on hand (approximate) 2 4 3 3
All estimated Included
projects projects
Projects presented & estimated 4 3
Total hours 4,100 1,500
Total work months 30.5 11.2
Total heads needed for period 10.2 3.7
Heads on hand (approximate) 12 12
Hours available from heads on hand 4,838 4,838
Assumptions:
Estimating Code Values
Code Work Hrs
S 50
M 200
L 800
Other Assumptions:
Productivity % 80%
Work hrs/mo 134
Months/period 3
Classmates Online Confidential 08/12/2019 Page 3
432974792.xlsx
Total Include
Hours for in Final Hours for
Arch UI design Dev QA Operations Project Total? Final Sum
All Proposed Projects
Project Artichoke 4 2 3 5 4 2,100 x 2,100
Project Banana 2 3 4 3 2 1,200 x 1,200
Project Cauliflower 2 3 3 3 2 950
Project Dillpickle 2 3 4 3 2 1,200 x 1,200
Total hours estimated 800 850 1,500 1,500 800 5,450 4,500
Total hours included 700 600 1,250 1,250 700
Total work months included 5 4 9 9 5
Total heads needed 2 1 3 3 2
Heads on hand (approximate) 2 2 3 3 2
All estimated Included
projects projects
Projects presented & estimated 4 3
Total hours 5,450 4,500
Total work months 40.6 33.5
Total heads needed for period 13.5 11.2
Heads on hand (approximate) 12 12
Hours available from heads on hand 4,838 4,838
Assumptions:
Estimating Code Values
Code Work Hrs
1 50
2 100
3 250
4 500
5 750
Other Assumptions:
Productivity % 80%
Work hrs/mo 134
Months/period 3
Classmates Online Confidential 08/12/2019 Page 4
