Madison 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 $47,000. The variable cost of a bag is $2.20 and it is currently sold for $2.70. Currently, the monthly production volume is 90,000 bags and the maximum operating capacity is 125,000 bags per month.

1. Develop a spreadsheet model on a monthly basis for the situation described above:

2. Write a formula to calculate the monthly breakeven number of vegetable bags on your spreadsheet. Also write a formula to compute the breakeven number of bags as a percentage of the maximum capacity.

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 a price ranging from $2.50 to $3.00 per bag (in increments of $0.10).

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 want to see 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 $2.50 to $3.00 (in increments of $0.10) as the variable cost per bag varies from $1.8 to $2.5 (in increments of $0.10 across the top of the table). Apply conditional formatting to the cells in the table that exceed $11,000.

5. Create a line chart showing the net profit for per-bag prices of $2.50, $2.70, $2.90 using the corresponding 8 columns of your data table Use appropriate axis titles. Name each data series with the corresponding price in a chart legend on the right.

6. Add Two Scenarios: Make sure to give meaningful names to the input cells and output cells and create a backup copy of the sheet. The fixed cost increases by 10% and variable cost per unit decreases by 5%. The variable cost increases by 5% and selling price increases by 4%.