Optimization Rubric Dashboard Unacceptable 10 Points Accepta

Optimization Rubricdashboardunacceptable 10 Pointsacceptable 20 P

Design a comprehensive spreadsheet model for Overlooked Oils, including separate tabs for each part of the project, incorporating the LP models to maximize profit and minimize costs with added constraints, and a dedicated dashboard tab with clear instructions for managerial use. Your model should clearly document objectives, constraints, results, and provide actionable insights for the company's management regarding production decisions, inventory handling, and cost management.

Paper For Above instruction

Overlooked Oils, a perfume manufacturer based in Idaho Springs, Colorado, faces multiple operational challenges that require strategic optimization to enhance profitability and operational efficiency. To address these challenges, a detailed linear programming (LP) modeling approach must be employed, supported by a well-structured, user-friendly spreadsheet tool that facilitates decision-making by the company's management. This paper discusses the development of LP models across three project parts, the assumptions involved, the constraints, and the expected outcomes, emphasizing how such models assist in optimizing production processes under various constraints and cost fluctuations.

In the initial phase of the project, the primary objective is to maximize profit by determining the optimal quantity of each perfume to produce given the available ingredients, costs, and sales prices. The LP model's objective function is straightforward: maximize total profit, calculated as total revenue minus total ingredient costs. The revenue per bottle is fixed at $10.00, and the total costs are based on the unit costs per mL of ingredients and the quantities used in each perfume production batch. Constraints include minimum production levels of 100 bottles per perfume, total Bergamot usage equating precisely to the available stock (due to its limited shelf life), and the availability limits of each ingredient.

Formally, the LP model's variables include the number of bottles for each perfume. The constraints incorporate the recipe requirements, ingredient availability, and the minimum production levels. The critical constraint of Bergamot использование ensures the full usage to avoid waste costs, with any unused Bergamot incurring marginal costs without profit. Solving this model provides an optimal production plan, indicating the number of bottles of each perfume that maximizes profit and clarifies the remaining inventory levels post-production.

In the subsequent phases, the model's complexity increases. The second part introduces an overarching production volume constraint limited to exactly 1500 bottles daily. The goal shifts to minimizing total costs while respecting this overall production limit, inherently encompassing the previous constraints. This new LP model aims to provide an efficient, cost-effective production schedule, balancing ingredient costs, production targets, and ingredient availabilities.

The third part examines the impact of changing ingredient costs—specifically, doubling the sandalwood cost—on the production plan and operational costs. Re-running the LP model under this altered cost structure reveals shifts in production allocation, highlighting the sensitivity of profits to input costs. These analyses demonstrate the importance of flexible, data-driven decision models in managing raw material costs and optimizing product output.

For practical application, a comprehensive Excel spreadsheet is necessary. Each project part should be represented on a dedicated tab, with inputs such as costs, inventories, and recipes clearly documented. Solution outputs—number of bottles per perfume, remaining ingredient inventories, and limiting factors—must be displayed transparently in output cells. A dedicated dashboard tab must guide users through interpreting the results, adjusting variables, and understanding the implications of modifications. Instructions should be clear, assuming no prior optimization knowledge, highlighting how to modify recipes, costs, and inventories, and explaining how these adjustments influence overall profitability or costs.

This optimization tool empowers Overlooked Oils' management to make informed, strategic decisions. By systematically analyzing production constraints and cost sensitivities, the firm can improve resource utilization, reduce waste, and maximize return on investment. Moreover, the model's flexibility allows testing various scenarios, such as inventory shortages or cost increases, facilitating proactive operational planning and sustaining competitive advantage in the natural perfume industry.

References

• Beasley, J. E., Christy, R. D., & Ng, K. T. (2000). An Overview of the Use of Linear Programming in Manufacturing. Optimization and Engineering, 1(4), 407–432.