Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

The sensitivity analysis in the Quality Sweaters example was on the response rate. Suppose now that the response rate is known to be 8%, and

The sensitivity analysis in the Quality Sweaters example was on the response rate. Suppose now that the response rate is known to be 8%, and the company wants to perform a sensitivity analysis on the number mailed. After all, this is a variable under direct control of the company. Create a one-way data table and a corresponding line chart of profit versus the number mailed, where the number mailed varied from 80,000 to 150,000 in increments of 10,000. Does it appear, from the results you see here that there is an optimal number to mail, from all possible values, that maximizes profit? Write a concise memo to management about your results. Problem 2 Continuing the previous problem, use Goal Seek for each value of number mailed (once for 80,000, once for 90,000, and so on). For each, find the response rate that allows the company to break even. Then chart these values, where the number mailed is on the horizontal axis, and the breakeven response rate is on the vertical axis. Explain the behavior in the chart in a brief memo to management.

show it step by step in excel with the formula how to use what to do thanks.

Number mailed 100000 Total Revenue $320,000 Total_cost
Fixed cost of printing $20,000 Total_Revenue
Order inputs Total variable cost of printing mailing $25,000 Variable_cost_of_printing_mailing
Average order $40 Total variable cost of orders $257,600 Variable_cost_per_order
Variable cost per order $32.20 Total cost $302,600
Profit $17,400
Question 1 - sensitivity of profit to Number Mailed
Number Mailed Profit
$ 17,400
$ 80,000 $ 62,399,955,000
$ 90,000 $ 70,199,955,000
$ 100,000 $ 77,999,955,000
$ 110,000 $ 85,799,955,000
$ 120,000 $ 93,599,955,000
$ 130,000 $ 101,399,955,000
$ 140,000 $ 109,199,955,000
$ 150,000 $ 116,999,955,000
1% $ (37,200)
10% $ 33,000
Question 2 - breakeven response rate
Solve the following equation with Goal Seek, using the Response_rate cell as the changing cell:
Profit = $0

Step by Step Solution

There are 3 Steps involved in it

Step: 1

Certainly Lets address the problem step by step Part 1 Sensitivity Analysis on Number Mailed Well begin by setting up a oneway data table in Excel to analyze how the profit changes with the number of ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Auditing A Practical Approach

Authors: Robyn Moroney, Fiona Campbell, Jane Hamilton

4th Edition

0730382648, 978-0730382645

More Books

Students also viewed these Accounting questions