Spreadsheet Modeling

Hard coding is unacceptable. Any hard coded work will get $2$ pts only, irrespective of $100\%$

correct solution.

Use Excel only as your tool of analysis. Solutions with other analysis tools will get $0$ points.

Please do not skip any part for partial or full credit.

The Quality Sweaters Company sells hand$-$knitted sweaters. The company is planning to print a

catalog of its products and undertake a direct mail campaign. The cost of printing the catalog is

$$20,000$ plus $$0\mathrm{.}10$ per catalog. The cost of mailing each catalog $($including postage, order forms,

and buying names from a mail$-$order database$)$ is $$0\mathrm{.}15.$ In addition, the company plans to include

direct reply envelopes in its mailings and incurs $$0\mathrm{.}20$ in extra costs for each direct mail envelope

used by a respondent. The average size of a customer order is $$40,$ and the company's variable cost

per order $($primarily due to labor and material costs$)$ averages about $80\%$ of the order's value$-$that

is$,$ $$32.$ The company plans to mail $100,000$ catalogs.

Download "Breakeven Analysis Template" Excel file from Moodle and use the

template to do the HW

Suppose now that the response rate is known to be $15\%($Solve the HW using $15\%),$

and the company wants to perform a sensitivity analysis on the number mailed. After

all, this is a variable under direct control.

a$)$ quad$4$pts $)$ In quality sweater example, we discussed how to create one way data table

between profit and response rate. For Q$1,$ you need to create another one$-$way data

table between profit and the number mailed with number mailed having an

increment of $10,000$ from $100,000-200,000.$

b$)2$ pts $)$ Create range names and paste the range names used.

c$)3$ pts $)$ Create a line chart. Give a title to your chart, label the axis and edit the x axis

values.

d$)2$ pts $)$ If the company mailed $175,000$ catalogs, at what response rate would the company

break even?

e$)2$ pts $)$ Given that the response rate is known to be $15\%,$ how many catalogs will have to be

mailed to get a profit of $$95,000?$

Please answer through an excel sheet. See my attached to see the template given. Thank you!