for question 4:

4) (45 Points) WhatIf: a) Create a two-way data table for Jack's Coffee Shack showing Gross Profit for Prices per cup of coffee ranging from $1.00 to $3.00 in increments of $0.10 and Prices per por ranging from $4.50 to $6.50 in increments of $0.25. b) BONUS: Determine the Price per cup of coffee which would maximize gross profit assuming all other parameters are as given in column D. Leave this value in D4. 616 4 x Visa x V fx ocio LM Jack's Coffee Shack Price per cup of coffee Price per pound of beans 1.50 5.50 $ Cups sold Monthly revenue $ 5,130.00 I cost of beans $313.50 Gross profit $ 4,816.50 Intro Chart Sales Data Whatif Financial NPV CondSummary Macro PayData + 5) (45 Points) Financial: a) In cell D10, use an Excel function to calculate the amount that should accumulate in a retirement account based on the parameters given in cells D5:08. b) In cell D14, use an Excel function to calculate the monthly amount that could be withdrawn from the retirement account for an estimated 30-year retirement period. Assume that the rate of return remains constant and that the account is drawn down to zero over the retirement period. e an Excel function to calculate the present value of the proposed lottery annual payment offer. Present your answer as a positive number. d) In cell D26, use an IF function to indicate YES if the annual payments are the better option, and NO if they are not Retirement savings planner Years to retirement Estimated rate of return Annual contribution Present account value 40 3.0% (12,000) (15,500) $ $ Accumulated value Years of retirement Monthly amount available T Lottery winning decision-lump sum or annual payments $ $ Amount won Lump sum offered Number of payments Offered annual payments 35,000,000 22,658,741 20 1,750,000 $ Present value of annual payments Take the annual payments? NPV Macro Pay CondSummary Intro Chart W Financial Sales Data hatlf MacBook General Calibri (Body) 11 - A BI UOCA $ % 68 Format as Table Cell Styles G H Jack's Coffee Shack Gross Profit for Price per cup of coffe $1.00 Price per cup of coffee Price per pound of beans $ $ 1.50 5.50 $1.10 Cups sold 114 Monthly revenue $ 5,130.00 Cost of beans $313.50 Gross profit S 4.816.50 $1.200 $1.30 $1.40 $1.50 $1.60 $1.70 $1.80 $1.90 $2.00 $2.10 $2.20 $2.30 $2.40 $2.50 $2.60 $2.80 $2.90 $3.00 Prices per pound of beans $4.50 $4.75 Chart Sales Data PivotTable Whatlf Financial NPV CondSummary Macro Intro 11 Ready $2.301 $2.40 $2.50 $2.60 $2.70 $2.80 $2.900 $3.00 Prices per pound of beans $4.50 $4.75 $5.00 $5.25 $5.50 $5.751 $6.00 $6.25 $6.50 Chart SalesData Pivot Table WhatIf Financial NPV CondSummary Macro General $ % Format as Table Cell Styles DE Prices per pound of beans $4.50 $4.75 $5.00 $5.25 $5.50 $5.75 $6.00 $6.25 $6.50 Prices per cup of coffee Data Pivot Table Whatlf Financial NPV Cond Summary Macro PayData MacBook TUISCIL Id BI Urova. Au x & fx A B co Jack's Coffee Shack Price per cup of coffee Price per pound of beans 1.50 5.50 Cups sold 114 Monthly revenue $ 5,130.00 Cost of beans $313.50 Gross profit $ 4,816.50 mm MYN 4) (45 Points) WhatIf: a) Create a two-way data table for Jack's Coffee Shack showing Gross Profit for Prices per cup of coffee ranging from $1.00 to $3.00 in increments of $0.10 and Prices per por ranging from $4.50 to $6.50 in increments of $0.25. b) BONUS: Determine the Price per cup of coffee which would maximize gross profit assuming all other parameters are as given in column D. Leave this value in D4. 616 4 x Visa x V fx ocio LM Jack's Coffee Shack Price per cup of coffee Price per pound of beans 1.50 5.50 $ Cups sold Monthly revenue $ 5,130.00 I cost of beans $313.50 Gross profit $ 4,816.50 Intro Chart Sales Data Whatif Financial NPV CondSummary Macro PayData + 5) (45 Points) Financial: a) In cell D10, use an Excel function to calculate the amount that should accumulate in a retirement account based on the parameters given in cells D5:08. b) In cell D14, use an Excel function to calculate the monthly amount that could be withdrawn from the retirement account for an estimated 30-year retirement period. Assume that the rate of return remains constant and that the account is drawn down to zero over the retirement period. e an Excel function to calculate the present value of the proposed lottery annual payment offer. Present your answer as a positive number. d) In cell D26, use an IF function to indicate YES if the annual payments are the better option, and NO if they are not Retirement savings planner Years to retirement Estimated rate of return Annual contribution Present account value 40 3.0% (12,000) (15,500) $ $ Accumulated value Years of retirement Monthly amount available T Lottery winning decision-lump sum or annual payments $ $ Amount won Lump sum offered Number of payments Offered annual payments 35,000,000 22,658,741 20 1,750,000 $ Present value of annual payments Take the annual payments? NPV Macro Pay CondSummary Intro Chart W Financial Sales Data hatlf MacBook General Calibri (Body) 11 - A BI UOCA $ % 68 Format as Table Cell Styles G H Jack's Coffee Shack Gross Profit for Price per cup of coffe $1.00 Price per cup of coffee Price per pound of beans $ $ 1.50 5.50 $1.10 Cups sold 114 Monthly revenue $ 5,130.00 Cost of beans $313.50 Gross profit S 4.816.50 $1.200 $1.30 $1.40 $1.50 $1.60 $1.70 $1.80 $1.90 $2.00 $2.10 $2.20 $2.30 $2.40 $2.50 $2.60 $2.80 $2.90 $3.00 Prices per pound of beans $4.50 $4.75 Chart Sales Data PivotTable Whatlf Financial NPV CondSummary Macro Intro 11 Ready $2.301 $2.40 $2.50 $2.60 $2.70 $2.80 $2.900 $3.00 Prices per pound of beans $4.50 $4.75 $5.00 $5.25 $5.50 $5.751 $6.00 $6.25 $6.50 Chart SalesData Pivot Table WhatIf Financial NPV CondSummary Macro General $ % Format as Table Cell Styles DE Prices per pound of beans $4.50 $4.75 $5.00 $5.25 $5.50 $5.75 $6.00 $6.25 $6.50 Prices per cup of coffee Data Pivot Table Whatlf Financial NPV Cond Summary Macro PayData MacBook TUISCIL Id BI Urova. Au x & fx A B co Jack's Coffee Shack Price per cup of coffee Price per pound of beans 1.50 5.50 Cups sold 114 Monthly revenue $ 5,130.00 Cost of beans $313.50 Gross profit $ 4,816.50 mm MYN