dolution

A large food chain owns a number of pharmacies that operate in a variety of settings. Some are situated in small towns and are open for only 8 hours a day, 5 days per week. Others are located in shopping malls and are open for longer hours. The analysts on the corporate staff would like to develop a model to show how a store’s revenues depend on the number of hours that it is open. They have collected the following information from a sample of stores.

1. Use a linear function (e.g., y = ax + b; where a and b are parameters to optimize) to represent the relationship between revenue and operating hours and find the values of the parameters using the nonlinear solver that provide the best fit to the given data. What revenue does your model predict for 120 hours?

Your solutions for (a) and (b) should contain a detailed spreadsheet model (where the decision variables, parameters, objective function and constraints are identified and explained), as well as answers to the questions posed. Use Microsoft Excel to solve.