DCSN205

Instructions:

• Give your Excel workbook a name to indicate you (e.g., Elisa Top.xlsx).
• Make sure to label

1. A Company is considering investing in 3 projects. If it fully invests in a project, i.e., buys the whole project, the realised cash flows (in millions of dollars) will be as shown in the Table below. However, the Company can, if so desired, invest in a fraction of a project.

At the beginning of each period, the Company can, if desired, borrow on the money market up to $9 million at 3% interest per period, e.g., if it borrows $1 at the beginning of period 1, it must pay back $1.03 at the end of that period (which is the same as the beginning of the next period, i.e., period 2), and so on.

The Company will invest $2 million at the beginning of period 1 and $1 million at the beginning of period 3.

The Companys goal is to maximize cash on hand after all cash flows at the beginning of period 7 (Note: this implies that in all other periods, all available funds are reinvested, i.e., net cash = 0 for periods 1 through 6).

	Period
	1	2	3	4	5	6	7
Project 1	-3.0	-1.0	-1.8	0.4	1.8	1.8	5.5
Project 2	-2.0	-0.5	1.5	1.5	1.5	0.2	-1.0
Project 3	-2.0	-2.0	-1.8	1.0	1.0	1.0	6.0

Cash Flow from Projects

Build and solve a LP spreadsheet model to determine the optimal investment (what fraction of each project is bought) and borrowing (how much to borrow at the beginning of each period) strategy.

2. During the next 4 months, a Company must meet (on time) the following demands for a product:

	Month 1	Month 2	Month 3	Month 4
Demand (units)	4000	6000	3000	2000

• At the beginning of month 1, the number of units of the product on hand is 200, and the Company has 100 workers.

• A worker is paid $1500 per month. Each worker can work up to 160 hours a month before he or she can receive overtime of up to 20 hours per month paid at $13 per hour.

• It takes 4 hours of labour to produce each unit of the product.

• At the beginning of each month, workers can be hired or fired. The hiring cost per worker is $1600, and the firing cost per worker is $2000.

• At the end of each month, a holding cost of $3 per unit left in inventory is incurred.

Develop and solve an LP spreadsheet model to determine the optimal production schedule (number of units produced each month) and labour policy (number of hires and fires and hours of overtime each month) in order to minimize total cost (hiring + firing + regular-time wages + overtime wages + inventory holding cost).

Assume that all variables are continuous (i.e., fractional values are acceptable).