LNQ Inc. is an environmentally friendly company which sells packed organic vegetables in biodegradable bags. Currently the monthly fixed cost associated with its operation is estimated at $50,000. The variable cost of a bag is $1.20 and it is currently sold for $2.00. Presently, the monthly production volume is 65,000 bags and the maximum operating capacity is 90,000 bags per month.

1. Based on the situation described above, develop a spreadsheet model that calculates the monthly profit as a function of the number of bags 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 LNQFall20 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 capacity. Format this cell as a percentage with 0 decimals.

3. The company is considering increasing the selling price per bag. Construct a 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 $2.00 per bag (in increments of $0.05). 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 data table based on your spreadsheet model using Excels Data Table command to show the net profit associated with the selling price ranging from $1.80 to $2.30 (in increments of $0.10) 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 the production volume remains at its current value). 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 $8,000.

5. Create a line chart showing the net profit for per-bag prices of $1.90, and $2.10 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. Comment on the form of the plotted function