Problem 1 -
A new trend to reduce plastic in all products used or sold in greenhouse nurseries is getting national attention with customers. With this in mind, Green Acre Getaway Gardens management team is considering the operations requirements for replacing plastic potting containers with peat pots. The peat pots are made from Canadian sphagnum peat moss and wood pulp and are 100% biodegradable. The store owner, Taylor, has discovered that the peat pots can be made in house during the fall and winter months using leased proprietary equipment and purchasing peat and wood fiber. The equipment lease costs $349 for the entire fall and winter season. The variable cost is $0.032 to produce a 3 inch round and 3 inch tall peat pot (cost includes raw materials and labor). A revenue increase of $0.10 per pot is expected. (this is due to a savings of $0.07 for each currently used plastic pot combined with an expected additional $0.03 customers are willing to spend for a plant in a peat pot) On the Peat Pot worksheet, how many peat pots does Taylor need to sell to break-even each year? Use the Data -> What-if -> Goal Seek function to determine the break-even quantity. You will need to create a Total Revenue function, a Total Cost function, and a Profit function in the designated cells. Also, use the 'Quantity cell to show the break-even quantity.
Problem 2 -
The staff of Green Acre Getaway Gardens has brought it to Taylor's attention that there is an alternative potting product. Green Acre Getaway Gardens could purchase 3x3 inch coir pots (made from coconut husks) and rent metal holders. The coconut husk pots are more attractive to customers and they are willing to pay more for plants potted in these pots. The specialized holders only cost $244 to rent per season and each coir pot costs $0.055 to purchase. A revenue increase of $0.12 per pot is expected. (this is due to a savings of $0.07 for each currently used plastic pot combined with an expected additional $0.06 customers are willing to spend for a plant in a coir pot) Taylor has decided to use either the Peat Pots or the Coir Pots, but not both. What is the break-even quantity where Green Acre Getaway Gardens is indifferent on which potting product to use and sell? Use the Product Choice worksheet for the determination. You will need to determine for each product the profit from each product based on the quantity sold. Again, you will need to use Goal Seek to find the answer.