Question
Please make the excel model !! **Hint The decision variables are simple what % investment to take in each company so there arent too many
Please make the excel model !!
**Hint
The decision variables are simple what % investment to take in each company so there arent too many variables there actually.
Your main constraint is you need to have $1M left over at the end of each year. So what youll do is for each year figure out how much cash you'll end up with and make sure that's more than $1M. In other words, starting cash + other income - cash needed from investing in the various projects = ending cash which has to be bigger than $1M. Then the year after that you know your ending cash is growing at 2% interest which means your starting cash will be 2% more than what you ended your prior year. Then you do the same thing starting cash + other income cash used by the projects = ending
Tom Douglas is a famous Seattle restaurant entrepreneur. He has been so successful that he now just angel invests into new concepts that approach him. Recently a slew of entrepreneurs have reached out asking for his partnership. He has hired you on to determine which restaurant concepts he should invest in and at what level. The four he is considering are: Bitcoin Boba, Fancy French, Sushi Yum, and FroYolo. Each project requires a different level of investment over the next few years and will be sold within five years. Below are the projected cash flows ($M). Bitcoin Boba Year 1 Fancy French -4 Sushi Yum -6 FroYolo -9 -12 -8 -4 -8 uw NP 2 3 -10 -7 17 67 0 0 | -7 4 -4 26 -7 5 44 0 38 He currently has $10M to invest and expects to have an additional $4M each year to invest in these concepts at the start of each year (1 through 5). Thus for year 1 he'll have a total of $14M to invest. Assume that the money not spent in a given year is available for future years and also earns 2% interest. For example, if the ending balance in year 1 is $1M, then $1.02M will be available for projects in year 2 (along with the $4M in additional investment funds for year 2). Assume that all cash flows occur simultaneously at the start of the year (e.g., income in a given year can be used for investment in that same year). Tom needs to decide how much to invest in each project fully, fractionally (with other investors), or not at all. If he participates at less than 100%, then all the cash flows associated with that project are reduced proportionally. For example, if Tom Douglas invests in FroYolo at 50%, the cash flows associated with that project would be -4.5, -4, -3.5, -3.5, and 17.5 respectively at the start of years 1 through 5. Tom further has a policy that he would like to end each year with a cash balance of at least $1M. Interest is earned on all remaining cash including the $1M minimum balance. Which projects should you tell Tom Douglas to take part in and at what % level of investment so as to end year 5 with as much cash as possible? Formulate and solve a linear programming spreadsheet model. Clearly indicate your final values and total cash value. Provide a screenshot of your model and a screenshot of your solver screen prior to solving. You do not need to write the algebraic equationStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started