LNQ Inc. is an environmentally friendly company which sells packed organic vegetables in biodegradable bags. Currentlythe monthly fixed cost associated with its operation is estimated at $65,000. The variable cost of a bag is $1.35 and it iscurrently sold for $2.00. Presently, the maximum operating capacity is 120,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 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 Excel's 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 Excel's 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.