title: Optimization Problem on Excel: How many project can be conducted over a year?
body: <p>I'm probably pushing Excel to its limits, but here's the problem I'm trying to solve.</p>
  <p>I have a firm with the following inputs for one year:</p>
  <ol>
  <li><p>N project (Pi) to conduct. Each project is broken into activities (Aj(Pi)). For example, a project can be &quot;building a house&quot; and activities can be &quot;construction&quot;, &quot;plumbing&quot;, &quot;electricity&quot;...
  Each project has an estimated total of man-days per activity. Which means I basically have a table in Excel:</p>
  <p>P1 A1 4 md</p>
  <p>P1 A2 10 md</p>
  <p>P1 A3 5 md</p>
  <p>P2 A1 20 md
  ...</p>
  </li>
  </ol>
  <p>Projects are sorted from most important to least important.</p>
  <ol start="2">
  <li><p>M employees (Ei) who can work on several projects. Each employee is part of a single community (Cj) and is assigned total available man-days for a year (usually around 220 md). Which means I have a table in Excel:</p>
  <p>E1 C3 220</p>
  <p>E2 C1 210</p>
  <p>E3 C2 219
  ....</p>
  </li>
  <li><p>Each community (Cj) is linked to several activities its members can perform. For example, the community &quot;electricians&quot; is linked to activities likes &quot;Electricity&quot;, &quot;Lighting&quot;, &quot;Inspection&quot;...Which means I have a table in Excel:</p>
  <p>C1 A1</p>
  <p>C1 A2</p>
  <p>C1 A3</p>
  <p>C2 A2</p>
  <p>C3 A1
  ...</p>
  </li>
  </ol>
  <p>My question is:</p>
  <p>A) Given my employees and their abilities, how many projects can I conduct over the year? And what's the corresponding allocation?</p>
  <p>B) Is it possible to do this on Excel?</p>