Reference no: EM132094474
Please solve the following problem using Microsoft Excel Solver and provide screen shots of cells, formulas, constraints, etc.
While undercover as an MBA student to investigate a money laundering operation, Pam becomes interested in entrepreneurship. She assumes that she can save, borrow, and raise $500,000 for a new venture as her initial investment. She is planning on using this money in 3 different ways over the course of the next 4 years: (1) product research and development, (2) setting up and/or improving the production process, and (3) marketing.
1. She will conduct product research and development only once, at the beginning of the first year, and spend at least $50,000 on it.
2. She will set up the production process at the beginning of the first year and has an opportunity to improve it again at the beginning of the third year. Setting up the production process will cost her at least $40,000, and she will determine how much to spend on improvements in the third year.
3. She can spend money on marketing every year. She thinks that she needs to spend at least $30,000 in the first year just to get marketing channels set up and start to build a name for his brand, and she'll determine how much to spend in the subsequent years.
She estimates that any dollars spent on marketing will return 20% annually, any dollars spent on the production process will return 30% at the end of two years, any dollars spent on product research and development will return 50% at the end of three years. Any returns she receives during the four years will be reinvested into the business. How should Pam propose to spend her money in order to maximize the ending cash value of the initial $500,000 investment? Assume that all investments are made at the beginning of the year, and the ending cash value is calculated at the end of the fourth year.