Question

Can you provide solution in the Excel using Solver for the below problem ?

On Monday morning, you have $3000 in cash on hand. For the next seven days, the following cash requirements must be met: Monday, $5000; Tuesday, $6000; Wednesday, $9000; Thursday, $2000; Friday, $7000; Saturday, $2000; Sunday, $3000. At the beginning of each day, you must decide how much money (if any) to withdraw from the bank. It costs $10 to make a withdrawal of any size. You believe that the opportunity cost of having $1 of cash on hand for a year is $0.20. Assume that opportunity costs are incurred on each day’s ending balance ($0.20/365). Determine how much money you should withdraw from the bank during each of the next seven days.

Answer 1

Cost of withdrawl	$     10
Opportunity cost per $1 per year	$ 0.20
	Mon	Tue	Wed	Thu	Fri	Sat	Sun
No of Withdraw	1	0	1	0	1	0	0
Beginning Cash Bal.	$ 3,000	$   6,000	$       -	$    2,000	$        -	$    5,000	$    3,000
Amount Withdrawn	$ 8,000	$       -	$ 11,000	$        -	$ 12,000	$        -	$        -
Cash Requirement	$ 5,000	$   6,000	$   9,000	$    2,000	$    7,000	$    2,000	$    3,000
Ending cash Bal.	$ 6,000	$       -	$   2,000	$        -	$    5,000	$    3,000	$        -
Total cost to withdraw	$     30	($10 x 3)
Total opportunity cost	$ 8.77	($0.20 x $16,000 /365)
Total Cost	$ 38.77