Question 1

Optimizing Inventory and Maximizing Profits at Revolution Bikes Revolution Bikes keeps an inventory of high-quality bike frames on hand for all their custom jobs and would like to minimize the holding costs and other expenses associ- ated with excessive inventory and minimize the risk of losing any sales due to supply shortages. Also, since they sell both custom and standard bicycles, you have been asked to create a Solver model that will estimare the optimal number of each bike type co sell in order to maximize profits. They cannot produce more bicycles of either type than its estimated monthly demand nor can they use more resources than what is available. Business Background The owner of Revolution Bikes has provided you with an Excel the number of units sold each month over the last three years. This data will need to be cleansed belore proceeding with the analysis. Below is the data niecessary to calculate the EOQ and reorder point for the bike frames used in the custom bulds . Annual demand is estimated as the average monthly units sold times 12 Each bike frame ordered costs $300 . The cost to hold a unit in inventory for a year is $30 containing MANAGER'S EMPHASIS Clarity Be sure to organize all the proided inputs logically to ensure the . The lead time is always three weeks. Fifteen bike trames are always kept in inventory as part of the safety stock To assist you in building the model, Revolution Bikes has provided you with the informa tion on the next page, including ir Optimiaing invertory a St 2|1|425 500 713 5 20.0 0-5 7.0 3_6_4 3_3_2 15-10-95 C 0 er loo di di Points of Concern Revolution Bikes orders many different items from various suppliers and distributors. The economic order quantity can only be applied to a single item being ordered. Since bike frames are the most expensive product being purchased and stored in inventory, they are the focus of the EOQ. Because demand may vary from month to month, be sure the model is flexible by referencing cells instead of hardcoding in values for the formulas Business Requirements See the list below for the basic requirements requested by the owner of Revolution Bikes. Be sure to label and format all values appropriately and use named ranges where . Calculate the economic order quantity, using the data provided in the ex04Inven- tory xisx worksheet and the data provided above. Calculate lead time demand by multiplying demand by lead time (converted to years: -3/52) . Calculate the reorder point based on the lead time demand and safety stock . Create a one-variable data table to see how EOQ and the reorder point are affected when annual demand varies from 290-350 in increments of 10. . Use the Scenario Manager to create a summary report showing the EOQ reorder point for a worst-case, most-likely, and best-case scenario based on the following: Worst-case: Demand-300, Holding Costs $45 Most-likely: Demand- 320, Holding Costs $30 Best-case: Demand 350, Holding Costs $20 On another worksheet, create a model that will use Solver to determine the number of custom and standard bikes to produce in order to maximize profits and stay within all material and production constraints Create a Solver Sensitivity Report. Provided File Revolution Bikes Monthly Units Sold File contains data on units sold from 2015-2017 ex04Inventory.xlsx Analysis Questions 1. The data provided on the UnitsSold worksheet required some cleaning before it could be analyzed to calculate the estimated annual demand. What text functions 2. What is the economic order quantity if annual demand is 320, order costs are $300, and holding costs are $30? Explain the business benefits of being able to calculate 3. Explain why estmating the reorder point further reduces costs for Revolution Bikes did you use to clean the data? Explain how you knew which text functions to use the EOQ 4. Interpret the results of the Scenario Summary and explain how it bonefits the own Optimizing Inventory and Maximizing Profits at Revolution Bikes Revolution Bikes keeps an inventory of high-quality bike frames on hand for all their custom jobs and would like to minimize the holding costs and other expenses associ- ated with excessive inventory and minimize the risk of losing any sales due to supply shortages. Also, since they sell both custom and standard bicycles, you have been asked to create a Solver model that will estimare the optimal number of each bike type co sell in order to maximize profits. They cannot produce more bicycles of either type than its estimated monthly demand nor can they use more resources than what is available. Business Background The owner of Revolution Bikes has provided you with an Excel the number of units sold each month over the last three years. This data will need to be cleansed belore proceeding with the analysis. Below is the data niecessary to calculate the EOQ and reorder point for the bike frames used in the custom bulds . Annual demand is estimated as the average monthly units sold times 12 Each bike frame ordered costs $300 . The cost to hold a unit in inventory for a year is $30 containing MANAGER'S EMPHASIS Clarity Be sure to organize all the proided inputs logically to ensure the . The lead time is always three weeks. Fifteen bike trames are always kept in inventory as part of the safety stock To assist you in building the model, Revolution Bikes has provided you with the informa tion on the next page, including ir Optimiaing invertory a St 2|1|425 500 713 5 20.0 0-5 7.0 3_6_4 3_3_2 15-10-95 C 0 er loo di di Points of Concern Revolution Bikes orders many different items from various suppliers and distributors. The economic order quantity can only be applied to a single item being ordered. Since bike frames are the most expensive product being purchased and stored in inventory, they are the focus of the EOQ. Because demand may vary from month to month, be sure the model is flexible by referencing cells instead of hardcoding in values for the formulas Business Requirements See the list below for the basic requirements requested by the owner of Revolution Bikes. Be sure to label and format all values appropriately and use named ranges where . Calculate the economic order quantity, using the data provided in the ex04Inven- tory xisx worksheet and the data provided above. Calculate lead time demand by multiplying demand by lead time (converted to years: -3/52) . Calculate the reorder point based on the lead time demand and safety stock . Create a one-variable data table to see how EOQ and the reorder point are affected when annual demand varies from 290-350 in increments of 10. . Use the Scenario Manager to create a summary report showing the EOQ reorder point for a worst-case, most-likely, and best-case scenario based on the following: Worst-case: Demand-300, Holding Costs $45 Most-likely: Demand- 320, Holding Costs $30 Best-case: Demand 350, Holding Costs $20 On another worksheet, create a model that will use Solver to determine the number of custom and standard bikes to produce in order to maximize profits and stay within all material and production constraints Create a Solver Sensitivity Report. Provided File Revolution Bikes Monthly Units Sold File contains data on units sold from 2015-2017 ex04Inventory.xlsx Analysis Questions 1. The data provided on the UnitsSold worksheet required some cleaning before it could be analyzed to calculate the estimated annual demand. What text functions 2. What is the economic order quantity if annual demand is 320, order costs are $300, and holding costs are $30? Explain the business benefits of being able to calculate 3. Explain why estmating the reorder point further reduces costs for Revolution Bikes did you use to clean the data? Explain how you knew which text functions to use the EOQ 4. Interpret the results of the Scenario Summary and explain how it bonefits the own