Month Jan Feb Mar Apr May Jun

Sales

Comm Rate Tot Comm 25100 40% 60000 32500 27400 10005 26300 28695

Compound Annual Growth Rate C

CAGR = (Ending Amt/Beginning Amt

Initial Amount Ending Amount Years of Investment CAGR (%)

Campaign cost

0.1 A 32.00% 100 A B 43.00% 100 B 28 8.96 10.00 -1.04 24 10.32 10.00 0.32 C 38.00% 125 C 35 13.30 12.50 0.80 D 24.00% 75 D 0 0.00 7.50 -7.50 Total 87.00 32.58 40.00 -7.42

Contribution Margin Num of campaigns

Revenue Contribution Campaign Expenses Net Profit

pound Annual Growth Rate Calculations

R = (Ending Amt/Beginning Amt) ^ (1/ No of years) -1

100 99998.36 3 8.999945

Scenario Summary
Current Values: 1 2 3

Changing Cells: $B$4 0.006 8 9 Result Cells: $F$13 2.40 3200.00 3600.00 $B$14 8.36 -791.04 -891.04 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.

10 4000.00 -991.04

Campaign cost

0.006 A 32.00% 100 A B 43.00% 100 B 28 8.96 0.60 8.36 24 10.32 0.60 9.72 C D 38.00% ###### 125 75 C 35 13.30 0.75 12.55 D Total 15 102.00 3.60 36.18 0.45 2.40 3.15 33.78

Contribution Margin Num of campaigns

Revenue Contribution Campaign Expenses Net Profit

Scenario Summary
Current Values: 2 1

Changing Cells: $A$4 100 200 300 $B$4 127 250 700 $C$4 3 5 6 Result Cells: $D$4 8.29% 4.56% 15.17% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.

Compound Annual Growth Rate Calculations Initial Amount 100 Ending Amount Years of Investment CAGR (%) 127 3 8.29%

Date 1-Jun 2-Jun 3-Jun 4-Jun 5-Jun 6-Jun 7-Jun 8-Jun 9-Jun 10-Jun

OpsLoss Downtime Expstaff 1610371 9 30 25677 0 21 1504852 11 29 0 0 37 913881 7 33 2352458 18 33 3549325 19 3 0 0 34 0 0 28 1649917 13 32 Expected Downtime Expstaff Loss 15 25

50 D o w n T i m e 10 15 20 25 30 35 40 45 50 55

45

40

Experienced Staff 35

30

25

20

15

Solver for Optimisation : Data, Solver Target Net Profit at 40 by adjusting Revenues. Constraints: Total of Campains (G9) = 400 New Revenue should be at least = current revenue. A Contribution Margin 33.50% Num of campaigns 125 Model A Current Revenue 28 Expected Revenue 28 Contribution 9.38 Campaign Expenses 0.75 Net Profit 8.63 B 24 35 12.18 0.54 11.64 C D 35 15 35 26.92754 11.55 9.29 0.66 0.45 10.89 8.84 Total 124.93 42.40 2.40 40.00 Target Cell B 34.80% 90 C 33.00% 110 D 34.50% 75 Total 400

Conversion to Crores Campaigning Cost 0.006

Target Cell

Bond Model - Exact Matching

What is the minimum cost portfolio, consisting of up to 6 bonds, that provides enough cash flow to cover liabilities in each period? Interest Rate Characteristics of bonds Bond 1 Face Value Coupon Rate Years to Maturity Price Bond 2 Bond 3 Bond 4 Bond 5

7%

$100 14% 3 $118.37

Bond 1

$100 13.00% 5 $124.60

Bond 2

$100 12% 6 $123.83

Bond 3

$100 15% 4 $127.10

Bond 4

$100 11.00% 6 $119.07

Bond 5

Number Purchased

10
Bond 1

10
Bond 2

10
Bond 3

10
Bond 4

10
Bond 5

Cost $6,130
Total Inc Liability

Cash Flow
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6

$140 $140 $140

$130 $130 $130 $130 $130

$120 $120 $120 $120 $120 $120

$150 $150 $150 $150

$110 $110 $110 $110 $110 $110

$650 $650 $650 $510 $360 $230

$32,000 $25,000 $22,000 $28,000 $25,000 $20,000

Problem An investor wants to put together a portfolio consisting of up to 6 different bonds. He has certain cashflow requirements in the future that the coupons of the bonds should cover. These payments are independent of interest rate changes. How should the choose his portfoli to minimize the cost of the bonds, while making sure that the payments cover his future cash-flow requirements?

Solution 1) The variables are the number of each bond to include in the portfolio. In worksheet BOND3 these are given the name Purchased_bonds. 2) The constraints are very simple. First we have the logical constraints: Purchased_bonds >= 0 via the Assume Non-Negative option Purchased_bonds = integer (We can not buy fractions of a bond) Then there is the constraint to make sure that the cash-flow requirements are met: Cash_flow >= Liabilities 3) The objective is to minimize the portfolio cost. This is given the name Total_cost. Remarks In this model we assume that money coming in from maturing bonds can not be used to cover the cash-flow requirements. Also, we do not account for excess money in one period that may be transferred to the next period.

Working Capital Management.

Determine how to invest excess cash in 1-month, 3-month and 6-month CDs so as to maximize interest income while meeting company cash requirements (plus safety margin). 1-mo CDs: 3-mo CDs: 6-mo CDs: Month: Init Cash: Matur CDs: Interest: 1-mo CDs: 3-mo CDs: 6-mo CDs: Cash Uses: End Cash: Yield 1.0% 4.0% 9.0% Month 1 $400,000 Term 1 3 6 Month 2 $325,000 $0 $0 0.00 Price $2,000 $3,000 $5,000 Month 3 $335,000 $0 $0 0.00 Purchase CDs in months: 1, 2, 3, 4, 5 and 6 1 and 4 1 Month 4 $355,000 $0 $0 0.00 0.00 $80,000 $275,000 Month 5 $275,000 $0 $0 0.00 Interest Earned: $0.00 End $240,000 $0 $0

Total Month 6 $225,000 $0 $0 0.00

0.00 0.00 0.00 $75,000 $325,000

($10,000) $335,000

($20,000) $355,000

$50,000 $225,000

($15,000) $240,000

$60,000 $180,000

Problem A company wants to invest excess cash in 1-month, 3-month and 6-month Certificates of Deposit (CDs). The company has expected uses of cash in the next 6 months, and it wants to make sure that the principal and interest from maturing CDs meet the requirements for cash plus a safety margin for each month. For simplicity we assume that 3-month CDs can only be bought at the start of months 1 and 4, and 6-month CDs can only be bought in month 1. Initial cash available is $400,000. How many and what kind of CDs should the company buy in order to maximize the earned interest, and meet the safety margin of $100,000 each month? Solution The characteristics of the 3 different CDs are given in cells A5 through F9. 1) The variables are the number of CDs to buy in each month. The variable cells are given names One_month_CDs, Three_month_CD1 and Three_Month_CD2, and Six_month_CDs. There are 6+2+1 = 9 variables. 2) The constraints are the limitations on the formulas in this model. First, there is the safety margin requirement for each month. This gives Monthly_cash >= 100000 Then there are the logical constraints on the number of CDs to be bought. It is not possible to buy half or other fractions, or negative amounts of CDs. We can rule out negative amounts with the Assume Non-Negative option. This gives One_month_CDs = integer Three_month_CD1 = integer Three_month_CD2 = integer Six_month_CDs = integer 3) The objective is to maximize earned interest. This is calculated by multiplying the number of CDs bought of each kind by the interest earned for each CD. This is given the name Total_interest. Remarks This is a good example of how the solver can help you make intelligent decisions in investments. Before solving the model with the Solver try to find a solution by hand. What interest is earned? The time required by the solver to solve this model can be considerable. Integer problems are very difficult to solve. In a model like this it would be possible to change the variables to the amount of money to be invested. This would give a 'normal' problem. We chose this form since often it is not possible to invest an arbitrary amount of money in a CD. In some situations it is not desirable to use integer constraints. When an expected solution of a model yields a value that is 2034.86, for example, it is safe to assume we can round this number to 2035. If the value is 0.34 however, it is not safe to assume we can round this number. In each model, you have to tradeoff precision vs solution time to make a

decision whether or not to use integer variables.