Solve for the optimum aggregate plan. The solution must be compatible with excel, so show calculations please. Make sure all steps on the bottom are accounted for in the aggregate plan.

FYI, the answer is between $$400,000$ and $$450,000$

This problem originates from a case study in the $1990$s when Locally Made Pottery, a small but growing business, was striving to meet increased consumer demand for its unique handcrafted pottery. The company was known for its artisanal pieces, which were sought after by customers for their quality and craftsmanship. As the demand for its products grew, especially in the spring, the business faced challenges in managing production and inventory effectively.

The demand for Locally Made Pottery's handcrafted pottery from consumers is highly seasonal, peaking in the spring as people purchase pottery for outdoor use and home decor. This seasonal demand ripples up the supply chain from retailers to Locally Made Pottery, the manufacturer. The options Locally Made Pottery has for handling this seasonality include adding workers during the peak season, subcontracting out some of the work, building up inventory during the slow months, or building up a backlog of orders that will be delivered late to customers. To determine how to best use these options through an aggregate plan, Locally Made Pottery's vice president of supply chain starts with the first task$-$building a demand forecast. Although Locally Made Pottery could attempt to forecast this demand itself, a much more accurate forecast comes from a collaborative process used by both Locally Made Pottery and its retailers to produce the forecast shown in Table $\backslash (\backslash$mathbf$\{1\}\backslash )$ below. It is important that this demand accounts for the product mix expected to sell and is expressed in terms of aggregate units defined earlier.

Locally Made Pottery sells each item through retailers for $\backslash (\backslash$$ $40\backslash ).$ The company has a starting inventory in January of $1,000$ pottery items. At the beginning of January, the company has a workforce of $65$ employees.

The plant has a total of $20$ working days in each month, and each employee earns $\backslash (\backslash$$ $4\backslash )$ per hour for regular time. Each employee works eight hours per day on straight time, with overtime as needed. As discussed previously, the capacity of the production operation is determined primarily by the total labor hours worked, and machine capacity does not limit production. Because of labor rules, no employee works more than $10$ hours of overtime per month.

The various costs are shown in Table $2.$

Currently, Locally Made Pottery has no limits on subcontracting, inventories, and stockouts$/$backlog$.$ All stockouts are backlogged and supplied from the following month's production. Inventory costs are incurred on the ending inventory each month. The supply chain manager's goal is to obtain the optimal aggregate plan that allows Locally Made Pottery to end June with at least $500$ units $($i$.$e$.,$ no stockouts at the end of June and at least $500$ units in inventory$).$

The optimal aggregate plan is one that results in the highest profit over the six$-$month planning horizon. For now, given Locally Made Pottery's desire for a high level of customer service, assume all demand is to be met, even if it is met late. Therefore, the revenues earned over the planning horizon are fixed. As a result, minimizing cost over the planning horizon is the same as maximizing profit. In other instances, a company may have the option of not meeting certain demand, or price itself may be a variable that a company has to determine based on the aggregate plan. In such cases, minimizing cost is not equivalent to maximizing profits.

Note

$1.$ For every month, have separate rows for labour hired, labour laid off and then calculate labour employed based on previous labour $+$ labour employed $-$ labour laid off. Use the labour hired cell for total hiring cost, labour laid off cell for total layoff cost. When we use MAX or IF formulas with solver, Excel tends to get confused.

$2.$ Try running solver a few times, since it may not give the correct answer in the first run.

$3.$ For any errors or help, feel free to use an appropriate LLM$.$