Need help setting up the LP model itself. I have the breakeven, and formulas and what not. But I am stuck in terms of applying it to excel. Would love some assistance. Even if it was just where to put the decision variables, the costs, etc. Once I have that I am sure I can figure out setting up the one way tables and what not. I am just not sure where to start.

1. Based on the situation described above, develop a spreadsheet model that calculates the monthly profit as a function of the number of bags produced and sold:

Be sure to put all numerical values in separate cells from formulas.

Format all dollar amounts with $ signs and 0 decimals for all values, except for the variable cost and the selling price (2 decimals).

Format all other numerical amounts as Number with thousands separators.

Name this sheet LNQSpring21 Model.

2. Write a formula to calculate the monthly breakeven number of vegetables bags on your spreadsheet. Show the mathematical procedure to get to the formula in your written report (use the notation presented in class). Format this cell as a number with a comma and 0 decimals. In a separate cell compute the breakeven number of bags as a percentage of the maximum monthly capacity. Format this cell as a percentage with 0 decimals.

3. The company is considering increasing the selling price per bag. Build a one-way data table based on your spreadsheet model using Excels Data Table command to show the monthly breakeven number of vegetable bags and percentage of maximum capacity with the price ranging from $1.50 to $3.00 per bag (in increments of $0.15).

Put a border around the contents of the table.

Format the amount inside the table as a Number with 0 decimals.

Format percent values inside the table as Percentage with 0 decimals.

4. The company is considering changing its production process to add an extra cover to the bag to reduce refrigeration times that would increase the cost of a bag. Management wants to estimate how profit reacts to changes in both the variable cost and the selling price. Construct a two-way data table based on your spreadsheet model using Excels Data Table command to show the profit associated with the selling price ranging from $1.50 to $2.75 (in increments of $0.25) as the variable cost per bag varies from $0.80 to $1.50 (in increments of $0.10 across the top of the table and assuming LQN produces at maximum capacity).

Put a border around the contents of the table.

Format all dollar amounts as Currency with 0 decimals.

Apply conditional formatting to the cells in the table that exceed $25,000.

5. Create a line chart showing the net profit for per-bag prices of $1.75, and $2.25 using the corresponding 8 columns of your data table: Move this chart to a New Sheet and name it VarCost VS Profit FixedPrice. Use appropriate axis titles. Name each data series with the corresponding price in a chart legend on the right.

6. Write a formula relating the selling price and the breakeven quantity as a percentage of the production capacity. Show the procedure in your written report.

7. Create a scatter plot with smooth lines showing the price on the horizontal axis and the breakeven quantity as a percentage of the production capacity, on the vertical axis.

Move this chart to a New Sheet and name it Price VS BEPercentage.

Use appropriate axis titles.