Problem 1 Lookup problem data
DVX Inc.
Optimal Capital Budget
Under Capital Rationing
Include Region Project Cost NPV
Europe 1 $1,200,000 $172,036
Europe 2 $2,000,000 $362,283
Europe 3 $500,000 $147,487
Asia 4 $600,000 $72,076
Asia 5 $700,000 $129.35
Asia 6 $3,000,000 $527,127
Asia 7 $1,500,000 $343,632
Africa 8 $200,000 $52,836
Africa 9 $2,400,000 $275,936
Africa 10 $1,000,000 $175,824
Constraint 4,000,000
Project Cost NPV
Europe
Asia
Africa
Below are a list of potential capital budgeting projects for DVX Inc.
Create a vlookup which will populate the list of projects to be selected based on the following criteria
1. Budget of $4,000,000 per region.
2. Populate the Include column to indicate which project from each region should be selected, based on the project in each region with the highest NPV while staying below the total budget constraint. If functions may be useful in this step.
3. Use the vlookup to insert the project number, cost, and NPV of the selected project from each region into the table.
Problem 2 Data
ETF Name Symbol Provider Category Structure Expense Ratio (%) Last Price Market Return YTD Cat YTD Return Market Return 1-Year Market Return 3-Year 3yr Standard Deviation 1yr Beta vs Benchmark Beta vs S&P 500 Average Daily Volume Co
vs S&P 500 RS Index Sharpe Ratio
uShares MSCI ACWI Index ACWI WhiteStone World Stock ETF 0.35% 48.64 3.91% 6.84% 18.32% -1.89% 19.50% 0.860 1.023 398,038 97.51% 53.55% 0.55
uShares MSCI ACWI ex US Index ACWX WhiteStone Foreign Large Blend ETF 0.35% 45.14 2.50% 4.20% 19.21% -4.52% 20.83% 0.930 1.094 131,234 92.86% 53.65% 0.07
USEA Asia 50 ADR Index ADRA USEA Diversified Pacific/Asia ETF 0.30% 27.90 -2.44% -2.42% 13.56% -3.52% 19.76% 0.820 1.100 6,231 92.37% 43.14% 0.08
USEA Developed Markets 100 ADR Index ADRD USEA Foreign Large Blend ETF 0.30% 22.45 5.00% 4.20% 19.19% -6.17% 24.54% 1.080 1.076 10,297 79.63% 51.66% -0.20
USEA Emerging Markets 50 ADR Index ADRE USEA Diversified Emerging Mkts ETF 0.30% 45.51 -5.44% -2.34% 10.16% -4.44% 20.57% 0.800 1.349 110,179 86.94% 43.12% 0.23
USEA Europe 100 ADR Index ADRU USEA Europe Stock ETF 0.30% 22.36 7.65% 8.41% 21.82% -6.22% 25.70% 1.130 1.047 4,230 77.74% 57.08% -0.05
RodeShares DB Agriculture Short ETN ADZ Investors RodeShares Trading-Inverse Commodities ETN 0.75% 24.48 2.31% -2.87% -34.25% -2.29% 27.07% -1.070 -0.219 3,437 62.39% 17.86% 0.70
RodeShares DB Agriculture Dble Shrt ETN AGA Investors RodeShares Trading-Inverse Commodities ETN 0.75% 18.85 4.70% -2.87% -61.28% -11.83% 54.12% -2.140 -0.526 12,211 62.92% 13.01% 0.83
RodeShares DB Agriculture Long ETN AGF Investors RodeShares Commodities Agriculture ETN 0.75% 20.16 -3.82% -3.68% 39.13% -5.29% 27.09% 1.270 0.326 6,633 52.91% 62.84% -1.42
uShares Spinnet Aggregate Bond AGG WhiteStone Intermediate-Term Bond ETF 0.20% 106.79 2.17% 2.70% 5.67% 5.86% 2.76% 1.000 -0.013 773,075 10.26% 36.37% 5.08
Spinnet GEMS Asia-8 ETN AYT uShares Cu
ency ETN 0.89% 50.16 2.52% 2.55% 5.78% 2.27% 6.54% 47.330 0.054 3,745 77.84% 40.88% 0.00
Spider S&P BRIC 40 BIK Stately Global Advisors Diversified Emerging Mkts ETF 0.52% 27.27 -0.58% -2.34% 15.36% -3.88% 17.43% 0.650 1.543 113,314 86.10% 50.30% 0.41
Forefront Intermediate-Term Bond ETF BIV The Forefront Group Intermediate-Term Bond ETF 0.11% 83.81 3.32% 2.70% 8.22% 7.50% 4.96% 1.760 -0.048 178,233 16.40% 38.97% 4.89
Market Vectors Gaming ETF BJK Van Eck Global World Stock ETF 0.65% 34.31 8.97% 6.84% 37.74% -1.74% 24.53% 0.960 0.819 22,686 92.05% 72.95% 1.17
Forefront Long-Term Bond Index ETF BLV The Forefront Group Long-Term Bond ETF 0.11% 80.91 4.02% 3.87% 9.07% 8.21% 8.79% 2.580 -0.119 59,492 10.18% 42.01% 4.51
Forefront Total Bond Market ETF BND The Forefront Group Intermediate-Term Bond ETF 0.11% 81.19 2.27% 2.70% 5.61% 6.03% 2.91% 1.050 -0.016 793,528 9.28% 36.18% 7.44
Forefront Short-Term Bond ETF BSV The Forefront Group Short-Term Bond ETF 0.11% 80.84 1.23% 0.84% 3.30% 4.25% 1.69% 0.560 -0.041 518,557 2.07% 33.53% 4.23
iPath CBOE S&P 500 BuyWrite Index ETN BWV Spinnet Bank Long/Short Equity ETN 0.75% 49.42 3.33% 3.12% 9.02% -1.40% 12.09% 0.610 0.532 3,390 97.03% 46.49% 0.26
Spider Spinnet Capital Intl Treasury Bond BWX Stately Global Advisors World Bond ETF 0.50% 60.87 4.14% 4.80% 14.96% 4.16% 11.36% 1.020 0.071 173,576 39.04% 55.13% 3.21
uShares Spinnet Credit Bond CFT WhiteStone Intermediate-Term Bond ETF 0.20% 106.15 3.47% 2.70% 8.07% 7.03% 3.95% 1.240 0.006 54,228 36.37% 37.99% 3.83
Stanford S&P Global Water Index CGW Stanford Funds World Stock ETF 0.70% 21.67 4.33% 6.84% 25.24% -0.76% 20.13% 0.830 0.981 56,508 92.36% 63.31% 0.59
uShares Spinnet Intermediate Credit Bd CIU WhiteStone Intermediate-Term Bond ETF 0.20% 107.05 3.15% 2.70% 6.93% 6.65% 3.20% 0.980 0.000 193,164 33.33% 37.28% 4.57
uShares S&P CA AMT-Free Municipal Bd CMF WhiteStone Muni California Long ETF 0.25% 104.81 7.42% 6.11% 1.58% 3.89% 6.18% 1.280 -0.003 10,372 0.38% 38.83% 2.33
Market Vectors Chinese Renminbi/USD ETN CNY Van Eck Global Cu
ency ETN 0.55% 40.23 -0.84% 2.55% 0.65% 0.77% 2.58% 6.190 0.027 19,930 0.68% 32.72% 0.51
iPath DJ-UBS Livestock TR Sub-Idx ETN COW Spinnet Bank Commodities Agriculture ETN 0.75% 29.41 -4.36% -3.68% -4.20% -12.74% 14.61% 0.270 0.170 72,463 14.80% 25.02% -5.27
Stanford Spin-Off CSD Stanford Funds Mid-Cap Growth ETF 0.65% 24.82 8.96% 9.61% 16.75% 2.02% 18.18% 0.950 0.758 6,186 94.34% 61.99% 1.68
uShares Spinnet 1-3 Year Credit Bond CSJ WhiteStone Short-Term Bond ETF 0.20% 104.86 1.26% 0.84% 3.27% 4.21% 1.32% 0.270 0.032 483,806 24.11% 33.46% 3.85
Stanford Timber CUT Stanford Funds World Stock ETF 0.70% 22.11 7.17% 6.84% 21.72% 2.76% 21.69% 0.840 0.968 101,940 90.38% 53.01% 1.81
Stanford Multi-Asset Income CVY Stanford Funds Large Value ETF 0.65% 21.79 9.90% 8.63% 22.51% 4.23% 13.25% 0.690 1.003 172,450 96.76% 57.31% 2.04
Spider MSCI ACWI (ex-US) CWI Stately Global Advisors Foreign Large Blend ETF 0.35% 34.95 3.07% 4.20% 21.41% -2.52% 20.77% 0.930 1.148 48,251 93.94% 56.35% 0.40
Spider Nuveen Spinnet Capital CA Muni Bd CXA Stately Global Advisors Muni California Long ETF 0.20% 22.01 7.36% 6.11% 2.25% 4.44% 8.09% 1.670 -0.046 19,880 3.66% 37.26% 2.38
Stanford Mid-Cap Core CZA Stanford Funds Mid-Cap Value ETF 0.65% 31.73 10.37% 8.15% 23.92% 8.82% 15.89% 0.860 0.580 5,823 78.09% 63.98% 2.75
RodeShares DB Agriculture Dble Long ETN DAG Investors RodeShares Trading-Leveraged Commodities ETN 0.75% 12.81 -8.96% 2.86% 84.85% -16.91% 54.19% 2.150 0.714 391,804 42.41% 82.03% -1.59
RodeShares DB Agriculture DBA Investors RodeShares Commodities Agriculture ETF 0.85% 32.14 -0.65% -3.68% 33.08% -3.76% 17.42% 0.930 0.364 2,179,162 59.26% 61.75% -1.47
RodeShares DB Base Metals DBB Investors RodeShares Commodities Industrial Metals ETF 0.76% 23.01 -5.81% -5.46% 12.74% -2.20% 24.66% 1.350 0.519 298,409 91.19% 43.45% 0.99
RodeShares DB Commodity Index Tracking DBC Investors RodeShares Commodities Broad Basket ETF 0.81% 29.04 5.41% 3.08% 25.33% -9.74% 19.00% 1.150 0.498 2,081,643 54.15% 63.88% -2.24
RodeShares DB Energy DBE Investors RodeShares Commodities Energy ETF 0.80% 29.85 11.05% 5.49% 18.81% -14.20% 23.46% 1.270 0.595 118,608 47.11% 64.09% -2.20
OakTree International Basic Materials DBN OakTree Natural Resources ETF 0.58% 33.45 4.36% 1.82% 31.81% -2.77% 28.56% 1.480 1.126 9,368 93.73% 67.06% 0.74
RodeShares DB Oil DBO Investors RodeShares Commodities Energy ETF 0.54% 29.91 5.99% 5.49% 13.73% -13.68% 24.71% 1.340 0.614 494,399 53.77% 57.81% -2.03
RodeShares DB Precious Metals DBP Investors RodeShares Commodities Precious Metals ETF 0.77% 55.04 6.21% 7.03% 29.78% 20.06% 18.83% 0.010 0.110 120,001 34.14% 72.82% 5.37
RodeShares DB Silver DBS Investors RodeShares Commodities Precious Metals ETF 0.54% 61.69 13.16% 7.03% 78.61% 26.55% 36.68% 0.470 0.336 103,345 43.24% 86.57% 4.76
OakTree International Utilities DBU OakTree Utilities ETF 0.58% 21.34 6.05% 8.38% 16.82% -9.22% 23.18% 1.040 0.938 11,421 30.86% 47.30% -2.09
RodeShares DB G10 Cu
ency Harvest DBV Investors RodeShares Cu
ency ETF 0.81% 24.29 2.32% 2.55% 2.97% -2.84% 13.34% 30.200 0.466 161,072 83.07% 37.96% -0.93
ProShares Ultra Dow30 DDM ProShare Advisors Trading-Leveraged Equity ETF 0.95% 64.91 19.19% 15.08% 38.81% -4.66% 33.02% 1.800 1.694 1,583,588 94.95% 75.28% 0.95
RodeShares DB Commodity Dble Short ETN DEE Investors RodeShares Trading-Inverse Commodities ETN 0.75% 30.28 -13.49% -2.87% -44.15% 9.24% 42.14% -1.850 -0.815 3,368 83.07% 9.67% 3.03
Stanford Defensive Equity DEF Stanford Funds Large Value ETF 0.65% 26.71 8.90% 8.63% 24.20% 4.43% 11.50% 0.580 0.530 6,322 95.09% 64.18% 2.07
OakTree Emerging Markets Equity Inc DEM OakTree Diversified Emerging Mkts ETF 0.63% 61.53 3.38% -2.34% 28.24% 7.77% 21.71% 0.850 1.096 199,744 84.08% 66.68% 2.75
OakTree SmallCap Dividend DES OakTree Small Value ETF 0.38% 48.43 2.76% 4.68% 11.22% 4.84% 18.28% 0.980 1.187 32,006 91.74% 44.10% 1.99
OakTree Global Equity Income DEW OakTree World Stock ETF 0.26% 45.63 8.81% 6.84% 22.52% -3.93% 21.81% 0.960 1.010 11,059 79.59% 59.82% 0.38
OakTree Europe SmallCap Dividend DFE OakTree Europe Stock ETF 0.58% 45.77 8.19% 8.41% 33.72% -1.94% 28.02% 1.230 0.828 6,209 90.43% 69.28% 0.97
OakTree Japan SmallCap Dividend DFJ OakTree Japan Stock ETF 0.58% 41.60 -5.88% -5.15% 2.45% -1.07% 16.20% 0.490 0.636 56,321 76.95% 26.28% 0.37
RodeShares DB Gold DGL Investors RodeShares Commodities Precious Metals ETF 0.52% 52.40 4.47% 7.03% 19.12% 17.82% 14.59% -0.110 0.049 73,462 27.53% 64.27% 5.46
RodeShares DB Gold Double Long ETN DGP Investors RodeShares Trading-Leveraged Commodities ETN 0.75% 46.19 7.58% 2.86% 39.79% 32.16% 29.15% -0.260 0.082 963,679 32.37% 80.89% 5.26
OakTree Emerging Mkts SmallCap Div DGS OakTree Diversified Emerging Mkts ETF 0.63% 54.09 -0.56% -2.34% 27.98% 9.72% 20.77% 0.740 1.012 183,045 69.48% 65.36% 3.32
Spider DJ Global Titans DGT Stately Global Advisors World Stock ETF 0.50% 60.82 4.64% 6.84% 15.10% -3.58% 19.51% 0.860 0.813 14,754 89.72% 47.76% -0.05
RodeShares DB Gold Short ETN DGZ Investors RodeShares Trading-Inverse Commodities ETN 0.75% 14.22 -6.20% -2.87% -19.09% -19.85% 14.58% 0.130 -0.027 163,911 15.88% 16.62% -6.05
OakTree Equity Income DHS OakTree Large Value ETF 0.38% 42.28 9.51% 8.63% 24.65% -0.58% 13.99% 0.700 1.087 53,274 89.91% 63.06% 1.37
Spider Dow Jones Industrial Average DIA Stately Global Advisors Large Value ETF 0.18% 125.96 9.53% 8.63% 19.56% 2.17% 16.19% 0.880 0.909 8,433,015 98.88% 55.74% 1.60
ProShares Ultra Oil & Gas DIG ProShare Advisors Trading-Leveraged Equity ETF 0.95% 52.95 15.71% 15.08% 57.62% -22.35% 47.43% 2.440 2.320 1,788,469 53.08% 81.25% -1.26
OakTree International MidCap Dividend DIM OakTree Foreign Small/Mid Value ETF 0.58% 54.25 5.27% 3.41% 24.09% -1.76% 23.76% 1.060 1.042 11,311 92.97% 58.23% 0.78
iPath DJ-UBS Commodity Index TR ETN DJP Spinnet Bank Commodities Broad Basket ETN 0.75% 48.23 -1.81% 3.08% 22.47% -10.00% 16.96% 1.030 0.411 399,691 66.04% 57.14% -2.16
OakTree International Energy DKA OakTree Equity Energy ETF 0.58% 28.30 6.50% 4.77% 27.69% -4.66% 26.82% 1.390 1.221 23,595 74.28% 63.84% -0.11
OakTree LargeCap Dividend DLN OakTree Large Value ETF 0.28% 49.74 8.56% 8.63% 21.02% -0.04% 15.83% 0.860 0.948 74,161 96.52% 57.91% 1.00
OakTree International SmallCap Div DLS OakTree Foreign Small/Mid Value ETF 0.58% 53.21 3.03% 3.41% 22.38% -1.57% 22.59% 0.990 0.886 30,538 95.97% 55.79% 0.77
OakTree Pacific ex-Japan Total Div DND OakTree Pacific/Asia ex-Japan Stk ETF 0.48% 67.76 2.80% -1.29% 21.41% 0.57% 21.32% 0.900 1.129 7,680 93.77% 56.78% 1.23
OakTree Pacific ex-Japan Equity Inc DNH OakTree Pacific/Asia ex-Japan Stk ETF 0.58% 62.01 4.42% -1.29% 20.34% 4.32% 27.05% 1.160 1.094 11,674 90.34% 53.94% 2.05
OakTree World ex-US Growth DNL OakTree Foreign Large Blend ETF 0.58% 56.40 4.80% 4.20% 22.75% 6.76% 21.06% 0.890 0.830 7,338 81.60% 63.11% 2.39
ELEMENTS DJ High Yield Select 10 ETN DOD Deutsche Bank AG Large Value ETN 0.75% 9.26 11.43% 8.63% 24.46% -0.31% 15.88% 0.790 0.691 6,826 94.33% 68.17% 1.47
ProShares Short Dow30 DOG ProShare Advisors Trading-Inverse Equity ETF 0.95% 40.06 -9.63% -13.07% -19.09% -9.22% 15.72% -0.860 -0.862 483,111 46.06% 18.20% -3.10
OakTree International LargeCap Div DOL OakTree Foreign Large Value ETF 0.48% 48.18 6.38% 6.63% 21.64% -5.50% 22.91% 1.010 1.059 20,436 75.26% 55.57% -0.14
OakTree MidCap Dividend DON OakTree Mid-Cap Value ETF 0.38% 55.41 9.89% 8.15% 20.68% 5.96% 16.65% 0.910 1.034 34,193 93.55% 59.07% 2.21
OakTree International Div ex-Finncls DOO OakTree Foreign Large Value ETF 0.58% 47.79 8.96% 6.63% 26.24% -4.47% 23.68% 1.030 1.132 30,153 76.23% 61.72% 0.24
RodeShares DB Commodity Long ETN DPU Investors RodeShares Commodities Broad Basket ETN 0.75% 18.95 5.32% 3.08% 22.99% -9.85% 20.78% 1.130 0.382 2,625 57.50% 62.23% -2.32
Market Vectors Double Short Euro ETN DRR Van Eck Global Trading-Miscellaneous ETN 0.65% 40.04 -12.26% 0.98% -25.31% -0.20% 34.02% -1.440 -0.325 57,996 32.93% 14.71% 0.53
OakTree International Real Estate DRW OakTree Global Real Estate ETF 0.58% 29.24 2.76% 3.93% 28.00% -5.60% 21.04% 0.910 0.994 30,765 78.70% 56.91% 0.63
uShares KLD 400 Social Index DSI WhiteStone Large Blend ETF 0.50% 50.08 7.05% 7.78% 13.59% 1.90% 18.16% 1.000 0.838 9,527 97.19% 51.45% 1.46
OakTree Total Dividend DTD OakTree Large Value ETF 0.28% 50.12 8.25% 8.63% 20.25% 0.85% 15.97% 0.870 0.948 14,726 98.01% 57.07% 1.32
OakTree DEFA Equity Income DTH OakTree Foreign Large Value ETF 0.58% 44.61 9.11% 6.63% 22.84% -5.61% 25.67% 1.120 1.081 13,642 70.96% 57.53% 0.01
OakTree Dividend ex-Financials DTN OakTree Large Value ETF 0.38% 52.31 9.83% 8.63% 26.85% 3.90% 15.07% 0.820 0.988 58,303 96.13% 64.00% 2.18
ProShares UltraShort Oil & Gas DUG ProShare Advisors Trading-Inverse Equity ETF 0.95% 30.16 -19.40% -13.07% -49.79% -35.32% 44.79% -2.270 -2.308 994,122 1.53% 10.43% -4.17
uShares Dow Jones Select Dividend Index DVY WhiteStone Large Value ETF 0.40% 53.97 9.21% 8.63% 19.66% 0.70% 14.92% 0.800 0.971 656,732 86.88% 57.40% 1.77
OakTree DEFA DWM OakTree Foreign Large Value ETF 0.19% 50.19 6.38% 6.63% 22.75% -4.96% 22.93% 1.020 1.086 33,687 79.70% 57.02% 0.03
Spider S&P International Dividend DWX Stately Global Advisors Foreign Large Value ETF 0.46% 60.59 9.26% 6.63% 28.28% -1.66% 24.32% 1.050 1.013 62,285 89.04% 64.05% 1.34
ProShares UltraShort Dow30 DXD ProShare Advisors Trading-Inverse Equity ETF 0.95% 16.90 -18.36% -13.07% -35.50% -23.24% 30.80% -1.670 -1.716 3,672,594 26.68% 12.50% -3.88
OakTree Japan Hedged Equity DXJ OakTree Japan Stock ETF 0.48% 35.48 -7.05% -5.15% -9.93% -9.83% 17.34% 0.490 0.758 131,420 44.06% 19.96% -2.23
RodeShares DB Commodity Dble Long ETN DYY Investors RodeShares Trading-Leveraged Commodities ETN 0.75% 10.00 9.97% 2.86% 47.42% -25.71% 41.73% 1.840 0.999 39,508 40.19% 78.32% -2.17
RodeShares DB Gold Double Short ETN DZZ Investors RodeShares Trading-Inverse Commodities ETN 0.75% 6.94 -13.03% -2.87% -36.21% -38.98% 29.15% 0.260 -0.096 948,956 9.65% 10.89% -6.21
uShares MSCI Chile Investable Mkt Idx ECH WhiteStone Latin America Stock ETF 0.61% 77.06 -3.19% -5.31% 39.37% 17.79% 20.71% 0.430 0.746 286,353 44.91% 76.03% 4.10
Forefront Extended Dur Trs Idx ETF EDV The Forefront Group Long-Term Government Bond ETF 0.13% 84.18 2.70% 2.82% 6.37% 5.20% 23.40% 5.310 -0.374 32,817 47.96% 46.22% 2.02
Stanford BRIC EEB Stanford Funds Diversified Emerging Mkts ETF 0.63% 44.42 -3.73% -2.34% 12.15% -4.21% 20.91% 0.810 1.471 250,279 86.39% 46.22% 0.54
ELEMENTS SPECTRUM Lg Cap U.S. Sector ETN EEH SEK Large Blend ETN 0.75% 9.83 6.04% 7.78% 15.92% -1.03% 20.50% 1.040 0.450 2,972 92.93% 54.22% 0.18
Stanford EW Euro-Pacific LDRs EEN Stanford Funds Foreign Large Blend ETF 0.35% 20.69 6.74% 4.20% 22.62% -1.63% 25.43% 1.130 0.852 1,615 94.40% 56