Question

Use the table below to answer the following question(s).

Bolton Computer's Laptop Pricing Decision	​
​	​
Inputs	​
​	​
Price	​
​	​
Model	​
​	​
Sales	-3.85 × price + 1150.9
​	​
Outputs	​
​	​
Total revenue	sales × price

Bolton Computers are planning to release a new line of low-end laptops. Using Excel's Solver, develop a nonlinear model to calculate the optimal price for the laptops, given that sales = -3.85 × price + 1150.9.

Answer 1

Let p=Price

s=sales

The revenue(r) =s*p

or, r =(-3.85*p+1150.9)*p ( since s = -3.85 × price + 1150.9 )

or, r =1150.9p-3.85p²

This is a very simple problem related to solving the non linear equation .

There will be three steps to solving the problem.

1. Identify and design the decision variable

In our problem the price will be the decision variable and nothing else as per the given equations shared to us.

In the current problem ,take a fresh excel spreadsheet and type

Decision variable in a cell on top

Clearly since price is the only decision variable

type price below the decision variable cell and type 1 in the rightmost cell to it (to denote that it is significant)

2. Now to the constraints function

select cells below the decision variable and type constraint function

then type price below it and leave the rightmost cell to it as blank . In the cell rightmost to it type >= sign and in the cell rightmost to it type 0. This is to signify that the price has to be greater than 0.

3. Now to the objective function

The objective function is only dependent on the price function and

is given by r =1150.9p-3.85p²

In excell solver it is done by typing =1150.9*E10-3.85*E10^2

Here E10 is the cell which containts the constraint variable price

Now we are ready with our excel solver model.

Go to data and look for solver whixh will be on extreme right on the taskbar

press it

then you will see "set decision". This requires us to put the objective function value . so we will select the cell containing objective function in it (the cell with objective function formula)

Now you will see "changing variables"

seelct the cell containing the changing varaible s identified in our spreadsheet (step1)\

Now you will see constraints

Click add in it

yuo will get a logic table.

Select cell to the rightmost of price under constraints box

the choose >= from the table

then type 0 in the rightmost box of the pop up .

This will define your constraint

Now click OK

and click Solve.

Yuo will get options for answers residual etc

Select "answer only)

this will give you a value in the cell where we had put the objective value formula

This will be the optimal solution value .

In our problem this comes to 86011.09

at price of 149.47 (~150)

I attach my solver sheet for reference