Wooden Desk Company makes old$-$style roll top desks. These are very popular with the upscale

executive class, and with people who would like to be upscale executives some day. Two kinds

of wooden roll top desks are made: the MajorDomo and the JuniorExecutive. The MajorDomo is

the larger of the two desks and its selling price is higher. The MajorDomo sells for $$750$ and the

JuniorExecutive sells for $$550.$

Each desk is made out of $(1)$ pine wood, $(2)$ the company's secret shellac$-$based finish,

$(3)$ the nuts and bolts and other miscellaneous hardware that hold the desk together, and $(4)$ the

honest toil of the company's master craftsmen. Below is a summary of the materials and labor

for each desk, which are also presented in the Constants section of the starter file $($Rows $9-16).$

For the coming year's production, the company has $150,000$ feet of pine on hand, $6,000$

quarts of shellac on hand, and $7,000$ pounds of hardware on hand. They have $15$ craftsmen on the

payroll. Each person works $2000$ hours a year. See Rows $17-21$ of the starter file.

Pine costs a dollar a foot. Shellac costs $2$ dollars a quart. The hardware costs $3$ dollars a

pound. A skilled craftsman makes $$15$ an hour. The company expects to have $$100,000$ in fixed

costs in the coming year. See Rows $22-26$ of the starter file.

Income taxes are an expense; taxes are paid on positive pre$-$tax income $($total revenue

greater than total costs$).$ If pre$-$tax income is zero or less, tax expense is zero. The expected tax

rate on positive pre$-$tax income is $30\%.$

Management wants to make at least $800$ of each kind of desk, The company can sell all

the desks they make. Management wants to maximize net income. How many of each kind of

desk should it make and sell?

Instructions:

Use variables in the Changing Cells and Constants sections to construct formula for each

variable in the Calculations and Income Statement sections.

Note: You should use cell addresses of these variables in your calculation rather than

using the numbers directly.

Use Slover to solve this optimization problem. You should have an Answer Report $1$

worksheet that summarizes the optimal production plan and the constraints you set up$.$

Submit your Excel file on Canvas.Wooden Desk

Changing Cells

Number of Major Domos $($MD$)$

$1$

Number of Junior Execs $($JrE$)$

$1$

Constants

Tax rate

$0\mathrm{.}3$

Pine in feet in $1MD$

$75$

Pine in feet in $1$JrE

$48$

Finish qt in $1MD$

Finish qt in $1$JrE

$3$

Hardware lbs in $1MD$

Hardware lbs in $1$JrE

$4$

Labor hrs in $1MD$

$2\mathrm{.}5$

Labor hrs in $1$JrE

$15$

Pine on hand feet

$10$

Finish on hand qts

$150000$

Hardware on hand lbs

Labor hours available per craftsman

Number of craftsman

Cost of pine per foot

Cost of finish per qt

Cost of hardware per lb

Labor cost per hour

Fixed costs in year

$6000$

Selling Price $-$ MD

$7000$

Selling Price $-$ JrE

$2000$

$15$

$$1\mathrm{.}00$

$$2\mathrm{.}00$

$$3\mathrm{.}00$

$$15\mathrm{.}00$

$$100,000\mathrm{.}00$

$$750\mathrm{.}00$

$$550\mathrm{.}00$

Calculations

Hours available total

Pine used in feet $($MD$+$JrE$)$

Finish used in qts $($MD$+$JrE$)$

Hardware used in lbs $($MD$+$JrE$)$

Labor hours used $($MD$+$JrE$)$

Income Statement

Revenue $($sales$)$ of MD

Revenue $($sales$)$ of JrE

Total Revenue

Cost of Pine

Cost of Finish

Cost of Hardware

Cost of Labor

Fixed costs in year

Total Costs

Pre$-$tax net income

Income Tax e$)$ File Preview

Net IncomePlease download the instruction file $-$ Assignment $4.$pdf ubrace$(darrubrace)$ and the starter file $-$ Assignment $4-$ Starter File.xlsx ubrace$(darrubrace)$ to

finish this assignment. Submit your Excel file for this assignment.

Notes:

Your formula in B$32$ to B$35$ under the Calculations section should be for both Major Domos $($MD$)$ and Junior Execs

$($JrE$).$

After you run Solver $($clicking on Solve$),$ you should see that Solver found a solution on the top of the Solver

Results window $($see the screenshot below$).$ Then click on Answer under Reports box on the right, then click on

OK$.$ Then the Answer Report $1$ worksheet will appear.

Solver Results

Solver found a solution. All Constraints and optimality

conditions are satisfied.

O Keep Solver Solution

Answer

Restore Original Values

R$$turn to Solver Parameters Dialog

$$ Outline Reports

$O{K}_{?}$

Cancel

Save Scenario...

Solver found a solution. All Constraints and optimality conditions are satisfied.

When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used,