I need specific solution with Excel Sheet (management science) and Opportunity c
ID: 379034 • Letter: I
Question
I need specific solution with Excel Sheet (management science)
and Opportunity costs are assumed to be out of the bank(hypothetical)
Problem 6 (50 points) At the beginning aeeount. For the next seven months the following cash requirements must be satisfied: of January you have $3000 in cash on Month Cash irement (Cash S5000 S6000 9000 S2000 S7000 S2000 S3000 nt required) Janua March April une Julv At the beginning of each month you have to determine the amount of money, if any, to withdraw from the bank to satisfy cash requirements. It incurs $10 to make a withdrawal of any amount. The opportunity cost of keeping S1 of cash on hand for a year s S0.20. Assume that opportunity costs are incurred on each month's ending balance of cash on hand. Assume also that a sufficient (actually unlimited) amount of money is currently deposited in the bank account. Q) Determine the amount of money you should withdraw from the bank during each of the next seven months,Explanation / Answer
We can solve this question by two methods- either logical or using excel solver. From both methods we will get the answer that money should be withdrawn every month to just meet the cash requirement for that month and no money should be left in month end.
1. Logical Method:
We need to compare the Opportunity cost and the withdrawl cost and make a decision so that the cost is minimized.
Tradeoff:- If we withdraw excees cash in advance, then we incur opportunity cost. If we withdraw money every month to just meet the cash requirement for the month, then we incur the withdrawl cost every month.
Opportunity cost is given as 20% per year which means 20/12% per month.
which means for $1 of extra cash we incur opportunity cost of 0.016667 $ per month
Let us calculate the amount of cash at which opportunity cost per month becomes equal to withdrawl cost of one withdrawl. Let that amonut be x. Then x can be calculated as:
x = 10/0.016667 = $ 600.
It means if we have excess cash of more than $600 for any given month, then the opportunity cost is more than the withdrawl cost and it is better to withdraw the money only at the time of requirement.
Since every month, the money required is more than or equal to 2000$, it is better to withdraw money every month.
2. Excel Solver:
PFA the google drive link for the exel file which contains the solver.
https://drive.google.com/open?id=1-mQh1FmRL3JpBRh9hbCYs9GAnbdbk8dL