Excel Sheet Assistance Needed
Budget: $30 – $250 USD
Waiting Line Analysis (Refer to your previous assignment for your formula sheet)
Sheet 1:
Assume you own a corn-dog food truck in Joplin, Missouri. Your customers arrive at the rate of 40 per hour. You can serve customers very fast at the rate of 60 per hour. ***In my video I showed you how to do all the formulas. In the test you cannot simply include everything from the video. Only answer the questions that are asked. Make sure these questions which will be parts A-D are in order and clearly labeled A-D. Failure to do this will result in at least a 40% deduction***
A. What is your utilization?
B. What is your average number of customers in the system?
C. What is the average time in the system?
D. What is the probability that there are less than 4 people in the system?
Sheet 2:
Assume you have been hired to analyze the lines at a single boat entry at Lake Powell in June. After doing some analysis, you find that boats arrive at the gate to get in at the rate of 10 per hour. The single booth can service 12 per hour. ***In my video I showed you how to do all the formulas. In the test you cannot simply include everything from the video. Only answer the questions that are asked. Make sure these questions which will be parts A-D are in order and clearly labeled A-D. Failure to do this will result in at least a 40% deduction***
A. What is the utilization of the person working in the booth?
B. What is the average number of customers in the waiting line?
C. What is the average time in the waiting line?
D. What is the probability the there are more that 2 in the system?
Product and Service Design
Sheet 3
You have just been promoted to the VP of sales for your organization. Your first job is to analyze a new project and whether it should be done. This project is developing and selling a new widget that has the following cost estimates and forecast.
Project Schedule
Table
The following figure shows the project schedule.
TestDiagram-2.PNG
Part A. What is the NPV?
Part B. Assume that your forecasting team re-analyzes the forecast for year 4 and changes that years forecast to 90% of the original 60,000 forecast. What is the new NPV? Under this change to year four would you recommend the project? Why or why not?
Part C. In addition to the change in part B your finance team also believes that the discount rate for this project should only be calculated as 7%. What is the new NPV with the change in forecast for year 4 and change in discount rate? Under these changes would you recommend the project? Why or why not?
Linear Programming
Sheet 4:
Use linear programming to solve the following problem.
Minimize: Z= 2A + 4B
Constraints
4A + 6B >= 120
2A + 6B >= 72
B >= 10
Sheet 5:
A farmer in beaver county is trying to maximize her profits by planting the optimal amount of corn, hay and wheat. She has unlimited acres that she can plant her crops (this is beaver county after all). Each acre of corn that she plants will bring $1,000 of profit. Each acre of hay that she plants will bring $1,500 of profit. Each acre of wheat that she plants will bring $2,000 dollars of profit.
She has two finite resources. She only has 100 workers and 150 tons of fertilizer.
It takes .1 workers to produce 1 acre of corn, .3 workers to produce 1 acre of hay, and .2 workers to produce 1 acre of wheat.
It takes .2 tons of fertilizer to produce 1 acre of corn, .1 tons of fertilizer to produce 1 acre of hay, and .4 tons of fertilizer to produce 1 acre of wheat.
Use linear programming to maximize her profit (Hint: The objective function should be Max: y= 1,000c + 1,500h + 2,000w and you should have a total of two constraints for workers and fertilizer).
A. What is her total profit?
B. How many acres of corn, hay, and wheat should she plant?
Attach your Excel Spreadsheet to the assignment link.
Sheet 1:
Assume you own a corn-dog food truck in Joplin, Missouri. Your customers arrive at the rate of 40 per hour. You can serve customers very fast at the rate of 60 per hour. ***In my video I showed you how to do all the formulas. In the test you cannot simply include everything from the video. Only answer the questions that are asked. Make sure these questions which will be parts A-D are in order and clearly labeled A-D. Failure to do this will result in at least a 40% deduction***
A. What is your utilization?
B. What is your average number of customers in the system?
C. What is the average time in the system?
D. What is the probability that there are less than 4 people in the system?
Sheet 2:
Assume you have been hired to analyze the lines at a single boat entry at Lake Powell in June. After doing some analysis, you find that boats arrive at the gate to get in at the rate of 10 per hour. The single booth can service 12 per hour. ***In my video I showed you how to do all the formulas. In the test you cannot simply include everything from the video. Only answer the questions that are asked. Make sure these questions which will be parts A-D are in order and clearly labeled A-D. Failure to do this will result in at least a 40% deduction***
A. What is the utilization of the person working in the booth?
B. What is the average number of customers in the waiting line?
C. What is the average time in the waiting line?
D. What is the probability the there are more that 2 in the system?
Product and Service Design
Sheet 3
You have just been promoted to the VP of sales for your organization. Your first job is to analyze a new project and whether it should be done. This project is developing and selling a new widget that has the following cost estimates and forecast.
Project Schedule
Table
The following figure shows the project schedule.
TestDiagram-2.PNG
Part A. What is the NPV?
Part B. Assume that your forecasting team re-analyzes the forecast for year 4 and changes that years forecast to 90% of the original 60,000 forecast. What is the new NPV? Under this change to year four would you recommend the project? Why or why not?
Part C. In addition to the change in part B your finance team also believes that the discount rate for this project should only be calculated as 7%. What is the new NPV with the change in forecast for year 4 and change in discount rate? Under these changes would you recommend the project? Why or why not?
Linear Programming
Sheet 4:
Use linear programming to solve the following problem.
Minimize: Z= 2A + 4B
Constraints
4A + 6B >= 120
2A + 6B >= 72
B >= 10
Sheet 5:
A farmer in beaver county is trying to maximize her profits by planting the optimal amount of corn, hay and wheat. She has unlimited acres that she can plant her crops (this is beaver county after all). Each acre of corn that she plants will bring $1,000 of profit. Each acre of hay that she plants will bring $1,500 of profit. Each acre of wheat that she plants will bring $2,000 dollars of profit.
She has two finite resources. She only has 100 workers and 150 tons of fertilizer.
It takes .1 workers to produce 1 acre of corn, .3 workers to produce 1 acre of hay, and .2 workers to produce 1 acre of wheat.
It takes .2 tons of fertilizer to produce 1 acre of corn, .1 tons of fertilizer to produce 1 acre of hay, and .4 tons of fertilizer to produce 1 acre of wheat.
Use linear programming to maximize her profit (Hint: The objective function should be Max: y= 1,000c + 1,500h + 2,000w and you should have a total of two constraints for workers and fertilizer).
A. What is her total profit?
B. How many acres of corn, hay, and wheat should she plant?
Attach your Excel Spreadsheet to the assignment link.