Excel_8G_Income_Model Project Description: In this Project, you will assist Kelsey Tanner, CFO of Brina's Bistro, and use a worksheet model and Solver to create several scenarios that would result in breaking even four months after opening Steps to Perform: Step Instructions Points Possible 1 0 2 0 3 8 3 Open the Excel workbook Student_Excel_8G_Income_Model.xlsx downloaded with this Project On the Projected Income worksheet, change the value in cell B15 to 6 and press ENTERin the green shaded area Sales now indicates $276,806 and the Gross Profit Loss indicates (54015)Note that these match the figures under Month 6 in cells H3 and H10 respectively Management wants to create several scenarios that would result in breaking even six months after opening Ensure that the Solver add-in is installed. Change the value in cell B16 to 11% and press ENTER. Open Solver. Set the objective cell as Sb$20 to a Value Of O and set the Variable Cells to $b$16.$b$18 (be sure to type the comma between the two cell references) to change both the growth rate in cell B16 and the costs related to Sales % in cell B18. Click Solve Save this scenario as 11% Growth and then restore the original values Display the Solver Parameters dialog box again. Add a constraint that requires Percent growth in cell B16 to be less than or equal to 10% lick Solve, and then save this scenario as 10% Growth Restore the original values Display the Solver Parameters dialog box again Delete the existing constraint and add a constraint for the Costs related to sales in cell B18 to be equal to 85% Click Solve, save the scenario as 85% Costs and then restore the original values With the Projected Income worksheet displayed, create a scenario summary worksheet. As the result cell , click cell B20. Redisplay the Projected Income worksheet. Change the value in cell B15 back to 1 and change the value in cell B16 back to 10% On the Projected Income worksheet create a Line chart using the noncontiguous ranges A2 M3 and A8: M8 Change the Chart Title to Expected Break-Even Point Position the chart so that the upper left comer of the chart aligns with the upper left comer of cell B22 Click cell A1 to deselect the chart and move to the top of the worksheet Ensure that the Analysis ToolPak add-in is installed Display the Sales worksheet. Create a Moving Average using the sales B2 B49 as the input range, 7 as the interval, C3 as the Output Range, and selecting the Labels in First Row and Chart Output check boxes Select the chart, and then move the chart to a new sheet named Sales Trend Chart 7 7 7 7 8 7 9 7 10 7 10 7 11 5 Select the chart, and then move the chart to a new sheet named Sales Trend Chart On the Sales Trend Chart worksheet, click one time to select the Horizontal (Category) Axis Labels, display the Select Data Source dialog box, click to Edit the Horizontal (Category Axis Labels), if necessary display the Sales worksheet, and then select the range A9:A49 from the Sales worksheet. On the Sales Trend Chart worksheet, change the date format used for the Horizontal (Category) Axis labels to the 3/14 Date format Created On: 10/15/2020 G019_XL_CHO_GRADER_BG_HW - Income Model 1.1 Grader - Instructions Excel 2019 Project Step Instructions Points Possible 7 12 13 7 14 7 On the Sales Trend Chart worksheet, change the Horizontal (Category) Axis title to Date On the Vertical (Value) Axis, set the Minimum to 4000 Click outside of the chart to deselect it. On this chart sheet, insert a custom footer with the file name in the left section On the Schedules worksheet, in cell F3, enter a COUNTIFS function to count the number of Day shifts each server is scheduled to work. Defined names exist for SERVER and SHIFT and the order is SERVER, cell E3, SHIFT, and "Day"Copy the formula down to cell F19. In the range 13:119, enter an AND function to determine which servers are scheduled for 2.5 day shifts In cell J3, enter a COUNTIFS function to calculate the number of Monday shifts each server is scheduled to work Edit the formula in cell J3 by adding a second COUNTIFS function to calculate the number of Tuesday shifts each server is scheduled to work. Copy the formula down through cell J19. Defined names exist for the range SERVER and DAY In cell K3, enter an IF function with a nested AND function to determine which employees are scheduled for at 2-5 shifts, including a Monday or Tuesday. The function should return the world Eligible if true and leave the cell blank if false. Copy the formula down through cell K19. Display the Service Awards worksheet. Servers earn a cash award when the number of Comment Cards from customers with a positive comment is 20 or more per month. In cell C3, 15 7 16 7 17 7 Grader - Instructions Excel 2019 Project Step Instructions Points Possible 12 7 13 7 On the Sales Trend Chart worksheet, change the Horizontal (Category) Axis title to Date On the Vertical (Value) Axis, set the Minimum to 4000 Click outside of the chart to deselect it. On this chart sheet, insert a custom footer with the file name in the left section On the Schedules worksheet, in cell F3, enter a COUNTIFS function to count the number of Day shifts each server is scheduled to work. Defined names exist for SERVER and SHIFT and the order is SERVER, cell E3, SHIFT, and "Day"Copy the formula down to cell F19. in the range 13:119, enter an AND function to determine which servers are scheduled for 2-5 day shifts 14 7 15 7 16 7 17 7 7 In cell 33, enter a COUNTIFS function to calculate the number of Monday shifts each server is scheduled to work. Edit the formula in cell J3 by adding a second COUNTIFS function to calculate the number of Tuesday shifts each server is scheduled to work. Copy the formula down through cell J19. Defined names exist for the range SERVER and DAY In cell K3, enter an IF function with a nested AND function to determine which employees are scheduled for at 2-5 shifts, including a Monday or Tuesday. The function should return the world Eligible if true and leave the cell blank if false. Copy the formula down through cell K19. Display the Service Awards worksheet. Servers earn a cash award when the number of Comment Cards from customers with a positive comment is 20 or more per month. In cell C3, enter an IFS function by using the Function Arguments dialog box to compute the cash award based on the following Comments of 30 or more earn a bonus of $75; Comments of 25 or more but less than 30 earn a bonus of $50, Comments of 20 or more but less than 25 earn a bonus of $25; if conditions are not met, leave the cell blank Copy the formula down through cell C19. Ten awards display as the result Ensure that the worksheets are correctly named and placed in the following order in the workbook Scenario Summary, Projected Income, Sales Trend Chart , Sales, Schedules, and Service Awards Save and close the file and then submit for grading 18 0 Total Points 100 Share Com - - 27 O 4 Dom Home int Di Page Layout Form Date V Help XO Calib -AN Wat Geen DC- BIU ma 2.4. sabar UPONTES RUNABLE DE Wome Cotto Procted income Statement Model Condom Formatting Sy DO Sed & Time Sect bum TV N O D 1 Projected Income Statement Model 7 10 156.250 $171.87 $ 109,043 $207,969 $ 320,766 $ 251,60 $213.306 $ 304,483 $ 334.936 $48.4.2 $425,272 $445,789 o 1 E . 6 11 $ 2 3 Sale 4 5 En Costs to sales 7 Cocte related to CHEESE STORE 135 40,000 175,938 149331 40,000 129,531 354AES 40.000 204485 100,931 0.000 220,033 190.00 40.000 210 210,020 DO 258,22 2020 0.000 280821 2641 40.000 2004 291,106 320513 40.000 40.000 331.394 OSB 192.582 10.000 427 845 119.401) $ 197,658) (15,422) $ 112,964) $ 10.2001 17,207) $ (1,018) $ (41) $3.542 $ 7,195 12.cas $ 17,454 100 10 Gros Profit/ou 11 + Red Seth 13 Costariated to as of Sale 14 15 M LON 171.2005 NS 10,000 1 Costa related to 19 Fendt 20 Gross Pro 21 22 23 20 25 20 27 Projecte come Sales Share Comments 29 O4 Det Sonds Condom Forge Wadow H N . P 0 R S T u y Fe Home insert DrawPage Layout Formula Duta Vw Hole (OA Cal 18 - AA wote D- om ata POATES AWARE Ofertas betale but est de son Daily Sole Model E F 1 Daily Sales Model Moving 2 3 15. 5.695 4 16. 17 5,514 6 18 5.123 + 19 6056 20 5.123 21 5.500 10 22 5.229 2) SA90 12 26- 6,160 13 25 5.98 10 26- 15 27 J 66.5 15 28 5.561 17 2 Suase 10- 5,861 19 D 60 20 2 6205 3- 5,736 265 23 5 5611 24 5458 7. 20 430 Sales Scies RRRRR File Home Insert Share Com DwPage Layout Form Date Vow Help weet 2.4.3 $ % 54 Condooms forming the 27 O co Foto Doma ST F M N 0 P 0 S V UPDATES WILL Updorot bed but wome AI Awek of D G H Schedule-Week of June 18 Servers-Day Shift Day Shift Servers of the Week 2 Day sh Server Number of S Server 75 Shta MOT 3 Son Ewuning Aisha 4 Sun Dey Anh Aty Arty 5 Sun Ewing Card Bryan Bryan 6 Bun Ewing Hugo Carol Carol 7 Sun Sun Day DAN Bry Sun Ewing Bryan Janne Jan 10 Sun Ewing Todd 11 Sun Mary 12 Bun 3 Son Day Les 14 Bon Day Jor Marcus 15 S En More Merge 16 Hon Der FO Mary Mary Ering he Rhes ton Carl Tood Todd 20 ton Herede 21 on E tered 22 ton 23 Men Bryan in 24 Dw You 25 Mary Hary 20 ton Day 27 ton 28T Dwy 29 Tue Day Cam Mere om Mer Hele Share Com Geen 28 % Dom Sted Conditione formats Cell Konting G H 1 3 x M N 0 P R s T 14 Home int Doge Layout D view Vw X Day - format BE UPDATES AVAILABLE Updfcrowy toward to come Al D Month of December Cards 3A 10 & Arty 5 Ilyen > 6 14 7 HD 22 9 19 10 20 11 12 Astia 25 t) 15 16 m= 17 15 17 Mere Tour 12 20 21 22 23 24 25 Excel_8G_Income_Model Project Description: In this Project, you will assist Kelsey Tanner, CFO of Brina's Bistro, and use a worksheet model and Solver to create several scenarios that would result in breaking even four months after opening Steps to Perform: Step Instructions Points Possible 1 0 2 0 3 8 3 Open the Excel workbook Student_Excel_8G_Income_Model.xlsx downloaded with this Project On the Projected Income worksheet, change the value in cell B15 to 6 and press ENTERin the green shaded area Sales now indicates $276,806 and the Gross Profit Loss indicates (54015)Note that these match the figures under Month 6 in cells H3 and H10 respectively Management wants to create several scenarios that would result in breaking even six months after opening Ensure that the Solver add-in is installed. Change the value in cell B16 to 11% and press ENTER. Open Solver. Set the objective cell as Sb$20 to a Value Of O and set the Variable Cells to $b$16.$b$18 (be sure to type the comma between the two cell references) to change both the growth rate in cell B16 and the costs related to Sales % in cell B18. Click Solve Save this scenario as 11% Growth and then restore the original values Display the Solver Parameters dialog box again. Add a constraint that requires Percent growth in cell B16 to be less than or equal to 10% lick Solve, and then save this scenario as 10% Growth Restore the original values Display the Solver Parameters dialog box again Delete the existing constraint and add a constraint for the Costs related to sales in cell B18 to be equal to 85% Click Solve, save the scenario as 85% Costs and then restore the original values With the Projected Income worksheet displayed, create a scenario summary worksheet. As the result cell , click cell B20. Redisplay the Projected Income worksheet. Change the value in cell B15 back to 1 and change the value in cell B16 back to 10% On the Projected Income worksheet create a Line chart using the noncontiguous ranges A2 M3 and A8: M8 Change the Chart Title to Expected Break-Even Point Position the chart so that the upper left comer of the chart aligns with the upper left comer of cell B22 Click cell A1 to deselect the chart and move to the top of the worksheet Ensure that the Analysis ToolPak add-in is installed Display the Sales worksheet. Create a Moving Average using the sales B2 B49 as the input range, 7 as the interval, C3 as the Output Range, and selecting the Labels in First Row and Chart Output check boxes Select the chart, and then move the chart to a new sheet named Sales Trend Chart 7 7 7 7 8 7 9 7 10 7 10 7 11 5 Select the chart, and then move the chart to a new sheet named Sales Trend Chart On the Sales Trend Chart worksheet, click one time to select the Horizontal (Category) Axis Labels, display the Select Data Source dialog box, click to Edit the Horizontal (Category Axis Labels), if necessary display the Sales worksheet, and then select the range A9:A49 from the Sales worksheet. On the Sales Trend Chart worksheet, change the date format used for the Horizontal (Category) Axis labels to the 3/14 Date format Created On: 10/15/2020 G019_XL_CHO_GRADER_BG_HW - Income Model 1.1 Grader - Instructions Excel 2019 Project Step Instructions Points Possible 7 12 13 7 14 7 On the Sales Trend Chart worksheet, change the Horizontal (Category) Axis title to Date On the Vertical (Value) Axis, set the Minimum to 4000 Click outside of the chart to deselect it. On this chart sheet, insert a custom footer with the file name in the left section On the Schedules worksheet, in cell F3, enter a COUNTIFS function to count the number of Day shifts each server is scheduled to work. Defined names exist for SERVER and SHIFT and the order is SERVER, cell E3, SHIFT, and "Day"Copy the formula down to cell F19. In the range 13:119, enter an AND function to determine which servers are scheduled for 2.5 day shifts In cell J3, enter a COUNTIFS function to calculate the number of Monday shifts each server is scheduled to work Edit the formula in cell J3 by adding a second COUNTIFS function to calculate the number of Tuesday shifts each server is scheduled to work. Copy the formula down through cell J19. Defined names exist for the range SERVER and DAY In cell K3, enter an IF function with a nested AND function to determine which employees are scheduled for at 2-5 shifts, including a Monday or Tuesday. The function should return the world Eligible if true and leave the cell blank if false. Copy the formula down through cell K19. Display the Service Awards worksheet. Servers earn a cash award when the number of Comment Cards from customers with a positive comment is 20 or more per month. In cell C3, 15 7 16 7 17 7 Grader - Instructions Excel 2019 Project Step Instructions Points Possible 12 7 13 7 On the Sales Trend Chart worksheet, change the Horizontal (Category) Axis title to Date On the Vertical (Value) Axis, set the Minimum to 4000 Click outside of the chart to deselect it. On this chart sheet, insert a custom footer with the file name in the left section On the Schedules worksheet, in cell F3, enter a COUNTIFS function to count the number of Day shifts each server is scheduled to work. Defined names exist for SERVER and SHIFT and the order is SERVER, cell E3, SHIFT, and "Day"Copy the formula down to cell F19. in the range 13:119, enter an AND function to determine which servers are scheduled for 2-5 day shifts 14 7 15 7 16 7 17 7 7 In cell 33, enter a COUNTIFS function to calculate the number of Monday shifts each server is scheduled to work. Edit the formula in cell J3 by adding a second COUNTIFS function to calculate the number of Tuesday shifts each server is scheduled to work. Copy the formula down through cell J19. Defined names exist for the range SERVER and DAY In cell K3, enter an IF function with a nested AND function to determine which employees are scheduled for at 2-5 shifts, including a Monday or Tuesday. The function should return the world Eligible if true and leave the cell blank if false. Copy the formula down through cell K19. Display the Service Awards worksheet. Servers earn a cash award when the number of Comment Cards from customers with a positive comment is 20 or more per month. In cell C3, enter an IFS function by using the Function Arguments dialog box to compute the cash award based on the following Comments of 30 or more earn a bonus of $75; Comments of 25 or more but less than 30 earn a bonus of $50, Comments of 20 or more but less than 25 earn a bonus of $25; if conditions are not met, leave the cell blank Copy the formula down through cell C19. Ten awards display as the result Ensure that the worksheets are correctly named and placed in the following order in the workbook Scenario Summary, Projected Income, Sales Trend Chart , Sales, Schedules, and Service Awards Save and close the file and then submit for grading 18 0 Total Points 100 Share Com - - 27 O 4 Dom Home int Di Page Layout Form Date V Help XO Calib -AN Wat Geen DC- BIU ma 2.4. sabar UPONTES RUNABLE DE Wome Cotto Procted income Statement Model Condom Formatting Sy DO Sed & Time Sect bum TV N O D 1 Projected Income Statement Model 7 10 156.250 $171.87 $ 109,043 $207,969 $ 320,766 $ 251,60 $213.306 $ 304,483 $ 334.936 $48.4.2 $425,272 $445,789 o 1 E . 6 11 $ 2 3 Sale 4 5 En Costs to sales 7 Cocte related to CHEESE STORE 135 40,000 175,938 149331 40,000 129,531 354AES 40.000 204485 100,931 0.000 220,033 190.00 40.000 210 210,020 DO 258,22 2020 0.000 280821 2641 40.000 2004 291,106 320513 40.000 40.000 331.394 OSB 192.582 10.000 427 845 119.401) $ 197,658) (15,422) $ 112,964) $ 10.2001 17,207) $ (1,018) $ (41) $3.542 $ 7,195 12.cas $ 17,454 100 10 Gros Profit/ou 11 + Red Seth 13 Costariated to as of Sale 14 15 M LON 171.2005 NS 10,000 1 Costa related to 19 Fendt 20 Gross Pro 21 22 23 20 25 20 27 Projecte come Sales Share Comments 29 O4 Det Sonds Condom Forge Wadow H N . P 0 R S T u y Fe Home insert DrawPage Layout Formula Duta Vw Hole (OA Cal 18 - AA wote D- om ata POATES AWARE Ofertas betale but est de son Daily Sole Model E F 1 Daily Sales Model Moving 2 3 15. 5.695 4 16. 17 5,514 6 18 5.123 + 19 6056 20 5.123 21 5.500 10 22 5.229 2) SA90 12 26- 6,160 13 25 5.98 10 26- 15 27 J 66.5 15 28 5.561 17 2 Suase 10- 5,861 19 D 60 20 2 6205 3- 5,736 265 23 5 5611 24 5458 7. 20 430 Sales Scies RRRRR File Home Insert Share Com DwPage Layout Form Date Vow Help weet 2.4.3 $ % 54 Condooms forming the 27 O co Foto Doma ST F M N 0 P 0 S V UPDATES WILL Updorot bed but wome AI Awek of D G H Schedule-Week of June 18 Servers-Day Shift Day Shift Servers of the Week 2 Day sh Server Number of S Server 75 Shta MOT 3 Son Ewuning Aisha 4 Sun Dey Anh Aty Arty 5 Sun Ewing Card Bryan Bryan 6 Bun Ewing Hugo Carol Carol 7 Sun Sun Day DAN Bry Sun Ewing Bryan Janne Jan 10 Sun Ewing Todd 11 Sun Mary 12 Bun 3 Son Day Les 14 Bon Day Jor Marcus 15 S En More Merge 16 Hon Der FO Mary Mary Ering he Rhes ton Carl Tood Todd 20 ton Herede 21 on E tered 22 ton 23 Men Bryan in 24 Dw You 25 Mary Hary 20 ton Day 27 ton 28T Dwy 29 Tue Day Cam Mere om Mer Hele Share Com Geen 28 % Dom Sted Conditione formats Cell Konting G H 1 3 x M N 0 P R s T 14 Home int Doge Layout D view Vw X Day - format BE UPDATES AVAILABLE Updfcrowy toward to come Al D Month of December Cards 3A 10 & Arty 5 Ilyen > 6 14 7 HD 22 9 19 10 20 11 12 Astia 25 t) 15 16 m= 17 15 17 Mere Tour 12 20 21 22 23 24 25