• Find your exact break-even point using Excel's Goal Seek.
• Explain why you chose the decision variables and why you think your solution is the best one for this company.

• Find your exact break-even point using Excel's Goal Seek.
• Explain why you chose the decision variables and why you think your solution is the best one for this company.

AutoSave Off n. Breakeven_Analysis_Annotated_V2 (1) Protected View Search Shanyell Henderson SH 1 File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing G11 fx Variable_cost_per_order > Snipping Tool X V 3 A B D E 1 J K L M X Cancel N New Mode v Delay Options 1 Quality Sweaters direct mail model 2 Select the snip mode using the Mode button or click the 1 3 Catalog inputs Model of responses New button. Yes, this is a lot of range names, especially for such a 4. Fixed cost of printing $20,000 Response rate 8% simple model. But (1) they are easy to generate with 5 Variable cost of printing mailing $0.25 Number of responses 8000 Snipping Tool is moving... the Create from Selection shortcut, and (2) they 6 make the formulas for the model much easier to In a future update, Snipping Tool will be moving 7 Decision variable Model of revenue, costs, and profit read. to a new home. Try improved features and snip 8 Number mailed 100000 Total Revenue $320,000 like usual with Snip & Sketch (or try the shortcut 9 Fixed cost of printing $20,000 Windows logo key + Shift + S). 10 Order inputs Total variable cost of printing mailing $25,000 11 Average order $40 Total variable cost of orders $257,600 Try Snip & Sketch 2 12 Variable cost per order $32.20 Total cost $302,600 13 Profit $17,400 14 15 Question 1: Sensitivity of profit to response rate 16 Response rate Profit Profit Versus Response Rate 17 $17,400 18 1% -$37,200 $40,000 19 2% -$29,400 $20,000 20 3% -$21,600 21 This is a scatter chart of the data in the data table 4% -$13,800 $0 22 to the left. If you'd like, you can instead choose 5% -$6,000 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% -$20,000 the type of scatter chart that connects the dots. 23 6% $1,800 24 This probably makes it even clearer that this is a 7% $9,600 -$40,000 linear relationship. 25 8% $17,400 -$60,000 26 9% $25,200 Response Rate 27 10% $33,000 28 29 Question 2: Breakeven response rate 30 Solve the following equation with Goal Seek, using ResponseRate as the changing cell: If there is one "intermediate" Excel tool everyone should understand how to 31 Profit $0 use (ok, along with VLOOKUP and IF functions), it's data tables. The real 32 power of Excel lies in its ability to do quick "what-if" analyses, and this is 33 Goal Seek probably isn't used all that often, but it can be usually accomplished with data tables. Students seem to have difficulty 34 very handy. It is used to solve one equation in one unknown. understanding data tables, but they're easy. For a one-way data table, Excel 35 In this case, the equation is the one shown above, and the takes each value in the left column of the table, plugs it into the specified 36 unknown is the response rate. In words, Goal Seek finds the column input cell, recalculates, and records the requested output(s) in the 37 response rate in cell E4 that drives the profit in cell E13 to 0. table. That's all there is to it! 38 Profit 39 40 Model 31 + 100% o Type here to search jj 12:13 AM 11/1/2020 AutoSave Off n. Breakeven_Analysis_Annotated_V2 (1) Protected View Search Shanyell Henderson SH 1 File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing G11 fx Variable_cost_per_order > Snipping Tool X V 3 A B D E 1 J K L M X Cancel N New Mode v Delay Options 1 Quality Sweaters direct mail model 2 Select the snip mode using the Mode button or click the 1 3 Catalog inputs Model of responses New button. Yes, this is a lot of range names, especially for such a 4. Fixed cost of printing $20,000 Response rate 8% simple model. But (1) they are easy to generate with 5 Variable cost of printing mailing $0.25 Number of responses 8000 Snipping Tool is moving... the Create from Selection shortcut, and (2) they 6 make the formulas for the model much easier to In a future update, Snipping Tool will be moving 7 Decision variable Model of revenue, costs, and profit read. to a new home. Try improved features and snip 8 Number mailed 100000 Total Revenue $320,000 like usual with Snip & Sketch (or try the shortcut 9 Fixed cost of printing $20,000 Windows logo key + Shift + S). 10 Order inputs Total variable cost of printing mailing $25,000 11 Average order $40 Total variable cost of orders $257,600 Try Snip & Sketch 2 12 Variable cost per order $32.20 Total cost $302,600 13 Profit $17,400 14 15 Question 1: Sensitivity of profit to response rate 16 Response rate Profit Profit Versus Response Rate 17 $17,400 18 1% -$37,200 $40,000 19 2% -$29,400 $20,000 20 3% -$21,600 21 This is a scatter chart of the data in the data table 4% -$13,800 $0 22 to the left. If you'd like, you can instead choose 5% -$6,000 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% -$20,000 the type of scatter chart that connects the dots. 23 6% $1,800 24 This probably makes it even clearer that this is a 7% $9,600 -$40,000 linear relationship. 25 8% $17,400 -$60,000 26 9% $25,200 Response Rate 27 10% $33,000 28 29 Question 2: Breakeven response rate 30 Solve the following equation with Goal Seek, using ResponseRate as the changing cell: If there is one "intermediate" Excel tool everyone should understand how to 31 Profit $0 use (ok, along with VLOOKUP and IF functions), it's data tables. The real 32 power of Excel lies in its ability to do quick "what-if" analyses, and this is 33 Goal Seek probably isn't used all that often, but it can be usually accomplished with data tables. Students seem to have difficulty 34 very handy. It is used to solve one equation in one unknown. understanding data tables, but they're easy. For a one-way data table, Excel 35 In this case, the equation is the one shown above, and the takes each value in the left column of the table, plugs it into the specified 36 unknown is the response rate. In words, Goal Seek finds the column input cell, recalculates, and records the requested output(s) in the 37 response rate in cell E4 that drives the profit in cell E13 to 0. table. That's all there is to it! 38 Profit 39 40 Model 31 + 100% o Type here to search jj 12:13 AM 11/1/2020