Problem
PinCast, a growing social media start-up, has just received $30M from a venture capital firm to grow its business. While large, the new infusion of cash is less than they had hoped for given their long "wish list" of projects they wanted to fund. The table below lists the projects PinCast was hoping to fund, along with their corresponding investment cost and projections of the present value (PV) they would produce if funded (in millions of dollars). The young management team needed to decide how best to allocate the available funds to the various projects on their wish list.
Project # Description PV ($M) Cost ($M)
1 IT infrastructure upgrade - 55 27
2 New product development 1 - 14 8
3 New product development 2 - 12 7
4 New product development 3 - 5 3
5 Advertising campaign 1 - 18 8
6 Advertising campaign 2 - 18 10
7 Site acquisition - 33 20
SHOW EXEL TABLES FOR SOULTON. PLEASE SHARE EXCEL FILE OR PHOTO OF SOLUTIONS
I. Suppose PinCast wants to maximize the total present value of the projects they fund using the $30M of new funding. Assume for your analysis that projects can be partially funded, e.g. if PinCast wants to fund only 50% of the IT upgrade, they can do so at a cost of 0.5 × 27 = 13.5 and doing this will generate a present value of 0.5 × 55 = 27.5.
Develop a spreadsheet optimization model to determine the mix of project investments that maximizes PinCast's total PV.
II. In the table provided in the answer sheet, provide the investment mix recommended by your model and the total present value of this mix.
III. Suppose now that a project cannot be partially funded; for example, if Pin-Cast wants to fund the IT upgrade, they must fund the full $27M cost, else they must pass on this project and allocate no money to IT upgrades (e.g. investment is either 100% or 0% in each project). Modify your optimization model to determine the mix of projects that maximizes PinCast's total PV.