**A+ Work!

INCLUDES SPREADSHEET WITH FORMULAS

1. (20 points broken as shown in the questions). Wanda Mboweni has started a small business to sell ride-on lawn mowers, lawn tractors, and a new line of product in the market robotic lawn mowers. She has selected two products to sell as shown in the table, and wants you to add two more products that fit the mower categories in her product line and also add variety to the product offerings.

Products Retail Price Wholesale Cost

Husqvarna 23 hp 48 in. Deck Yard Tractor $1,800 $1,450

Toro TITAN ZX5000 Riding Lawn Mower $4,000 $3,200

Wanda wants to earn the most profit she can, but she has to operate within some business constraints. She wants your help in estimating the maximum profit she can earn while meeting the following constraints.

Wanda has obtained a business loan of $600,000 to purchase the inventory that she wants to sell. Therefore, the total wholesale cost of her inventory cannot exceed $600,000.

Wanda must purchase a minimum of 10 units of each item from the manufacturers.

Wanda does not want to stock more than 120 units of any model except the robotic type to minimize the risk of stocking too many of a slow moving mower model. Since the robotic models are not yet quite popular, Wanda wants to hold a lower stock level of such models not to exceed 30 for each such model.

Do the following to help Wanda.

a) (1 point). Use the Internet to locate the retail price of two more lawn mower models that fit the mower categories in her product line and also add variety to the product offerings. You must have at least one robotic model. Consider the wholesale price of each unit to be 75% of the retail price for both units you find.

b) (2 points). Create a worksheet in Microsoft Excel to enter the data and set up your calculations as shown below. Apply some format to the worksheet to make it look good. Enter the data. Note that all the constraint related data should be added as shown.

c) (3 points). Enter the required formulas. Enter a purchase quantity of 10 for each item, which will fill out the table with corresponding results.

d) (1 point). Store a scenario naming it as Minimum purchase. What are the Total Purchase Quantity, Overall Total Cost, and the Overall Total Profit?

e) (7 points). Use Excel Solver to maximize the profit with the constraints mentioned above and limiting the purchase quantities to positive integers. When setting up constraints, use cell references rather than numeric values. In the options for the Solver, assume a linear model.

f) (2 points). Add the solution to the scenarios naming it Solution. Create a scenario summary that shows the original values and the final solution. What are the Total Purchase Quantity, Total Cost of Purchase, and the Total Profit? By what amount and percentage did the Total Profit change from that when the minimum quantity is purchased?

g) (1 point). Print a screenshot that shows the filled in Solver Parameters dialog box and the worksheet containing the solution. Make sure that the Solvers dialog box does not block the view of the solution.