How do you do the second to last bullet on goal seek

A 33. Assume the demand for a company's drug Wozac during the current year is 50,000, and assume demand will grow at 5% a year. If the company builds a plant that can produce x units of Wozac per year, it will cost $16x. Each unit of Wozac is sold for $3. Each unit of Wozac produced incurs a variable production cost of $0.20. It costs $0.40 per year to operate a unit of capacity. Determine how large a Wozac plant the company should build to maximize its expected profit over the next 10 years. 2 Assignment: Pharmaceutical Company - Wozac Modified version of question 33, page 87. Management has asked you to determine how large of a manufacturing plant to build. Ignore tax. Create a spreadsheet to model the annual profit for the pharmaceutical company assuming they build a plant to supply 60,000 units per year. o Calculate the demand for Wozac in years 1-10. o Calculate how many units of Wozac are actually sold. o Prepare an income statement for years 1-10. o Find the total net income for the life of the plant (sum of years 1-10). Color-code the spreadsheet correctly to indicate inputs, decision variables, and outputs. Check your model: with a plant capacity of 60,000 units per year, the total net income for years 1-10 is $411,418. Common Misconceptions: o The $16x cost is the cost to build the factory. It is a one-time expense. You can put it entirely in year 1 or depreciate it over 10 years. In the real world, it would be depreciated over a much longer time frame. The $0.40 per year is the cost to operate capacity. You have to maintain the entire factory, even if you are not using all of it! Create a one-way data table to show the total profit for a manufacturing plant with 50,000 to 60,000 annual capacity in 1,000 units/year increments. From this table, what size production plant maximizes profit? Use goal-seek to find the sales price per unit the company would need to charge per unit to make $1 million total net income in years 1-10 with a plant capable of producing 60,000 units per year. Record your answer to the penny ($x.xx), then set the price back to $3 since this messes up the data table