Senario\/Summary\tYour friend, Adair Deske, is considering opening a day care center. She has started compiling her assumptions and putting together an Income Statement. She has determined that she must make at least $75,000 profit per year in order to start the business. She has asked you to analyze her Income Statement and help her determine whether it is viable for her to start this business. You have agreed to help her complete her Income Statement and to perform What-If analysis to help her look at her potential profitability.\tWhen submitting the workbook to the Dropbox, provide a comment in the Dropbox comments area explaining what you learned from completing this iLab activity.\tLab Steps\tSTEP 1: Open Worksheet and Complete the Income Statement\tAdair needs your help in completing her Income Statement. She has provided the basic assumptions, but you need to provide the calculations that will be used as you perform What-If analysis.\tA. \ufffd\u00a0\u00a0 Open Week5_Lab6_Adair_Daycare_Student.xlsx. Notice that Adair has provided the basic assumptions concerning her costs and her revenue.\tB. \ufffd\u00a0\u00a0 Complete the following calculations.\tRevenue\t“\ufffd \ufffd \ufffd Total Revenue: Multiply tuition per day times number of days by number of children.\tVariable expenses are those expenses that will depend on the number of children served each year. Jane has provided the assumptions. Define annual costs based on the cost per day multiplied by the number of children multiplied by the number of days.\t“\ufffd \ufffd \ufffd Food Expenses\t“\ufffd \ufffd \ufffd Supply Expenses\tTeacher Cost: Create a VLOOKUP function to determine the annual teacher cost. The Lookup Value is the number of children. The Lookup Table is in cells E3:F7. The Index Column is the second column in the table. You want the closest match. Multiply the LOOKUP function times the annual teacher salary.\tTotal Variable expenses: This will be the SUM of all of the Variable Expenses (B17:B19).\tSummary. Use formulas in these cells because you will wish to change the values in your assumption section to see how these changes impact the Net Income.\tTotal Revenue should refer to the Total Revenue cell (B14).\tTotal Expense is Variable Costs + Fixed Costs.\tNet Income is Total Revenue – Total Expense\tC. \ufffd\u00a0\u00a0 Rename the file according to the file naming conventions as shown at the top of this page. Your Income Statement should look like the one below, but it is important that you have created formulas rather than typing in amounts. As you perform What-If analysis, the formulas will be recalculated to show you the results of changing assumptions.Image Description\tSTEP 2: Analyze the Difference in Total Expenses and Net Income When You Vary the Number of Students\tAdair desires to look at the differences in her net income depending on the number of children she cares for. Her house will accommodate no more than 15 children, and she believes she should have no less than six children. Because you are varying only one of your assumptions, you can develop a one-variable data table. A one-variable data table will allow you to see the impact of changing one variable”\ufffdand see the results on multiple outputs.\tA. \ufffd\u00a0\u00a0 Set up the Data Table to display number of children from 6 through 15. Add a descriptive title to the Data Table.\tB. \ufffd\u00a0\u00a0 Show the Expenses and Net Income for each change in number of children.\tC. \ufffd\u00a0\u00a0 Populate the Data Table using the Data tab, What-If analysis, Data Table tool.\tD. \ufffd\u00a0\u00a0 Apply Conditional Formatting to Net Income that is above $75,000.\tYour final Data Table should look something like this.\tSTEP 3: Analyze the Difference in Net Income When You Vary the Number of Students and the Charge Per Student\tAdair also wishes to look at the impact on Net Income when she varies the number of students and the charge per student. Use a two-variable data table to analyze.\tA. \ufffd\u00a0\u00a0 Set up the Data Table to display number of children as the row input (6″\ufffd15) and charge per student (35″\ufffd75 in $5 increments) as the column input.\tNOTE: In a Data Table, the information in the top row of the table is called the row input and the information in the first column of the table is called the column input. We usually think of the top row in Excel as a column header and the labels in the first column as row labels, so this may seem a bit confusing. Just remember, in a Data Table, the top row is row input; first column is column input.\tB. \ufffd\u00a0\u00a0 Enter Net Income in the result cell, and format this cell to display no data.\tC. \ufffd\u00a0\u00a0 Add a descriptive heading.\tD. \ufffd\u00a0\u00a0 Create the data table and apply conditional formatting to Net Income values over $75,000.\tYour Data Table will look something like this.\tSTEP 4: Create Scenarios and a Scenario Summary\tAdair wants to look at three scenarios. Be sure to save each scenario with a unique name, and place the name of the scenario on the sheet, so that when the scenario changes, the name on the sheet changes to match the scenario.\tScenario \ufffd\u00a0\u00a0\u00a0\u00a0 Teacher Salary Supplies \ufffd \ufffd Number \ufffd \ufffd Tuition\t1. Economy\u00a0\u00a0\u00a0\u00a0 15,000 \ufffd \ufffd\u00a0 25 \ufffd\u00a0 15 \ufffd\u00a0 35\t2. Midrange\u00a0\u00a0\u00a0\u00a0 26,000 \ufffd \ufffd\u00a0 60 \ufffd\u00a0 8 \ufffd\u00a0\u00a0\u00a0 50\t3. High \ufffd \ufffd\u00a0 38,000 \ufffd \ufffd\u00a0 100\u00a0\u00a0\u00a0\u00a0 6 \ufffd\u00a0\u00a0\u00a0 100\tShe wants to see the Net Income that would be achieved in each Scenario and compare this to her original assumptions.\tA. \ufffd\u00a0\u00a0 Name the cells that will be used in the Scenario.\tB. \ufffd\u00a0\u00a0 Create the three Scenarios.\tC. \ufffd\u00a0\u00a0 Create a Scenario Summary.\tD. \ufffd\u00a0\u00a0 Move the Scenario Summary to the end of the workbook.\tSTEP 5: Create a Documentation Sheet and Provide a Recommendation\tA. \ufffd\u00a0\u00a0 Add a documentation sheet to the beginning of the workbook. Include Author, date Created, Last Modified, and Contents sections.\tB. \ufffd\u00a0\u00a0 Provide a recommendation to Adair.\tC. \ufffd\u00a0\u00a0 Format the documentation sheet to match the look and feel of the other sheets in the workbook.\tD. \ufffd\u00a0\u00a0 Add a graphic to the documentation sheet.\tSubmit your completed workbook to the Dropbox. Make sure you post a comment about what you learned when submitting the file.\tIMPORTANT: There are two iLabs this week, and there is a separate Dropbox basket for each one. Be sure to submit your work for this iLab to the Dropbox basket labeled Week 5: iLab 6.<\/p>\n","protected":false},"excerpt":{"rendered":"
Senario\/Summary Your friend, Adair Deske, is considering opening a day care center. She has started compiling her assumptions and putting together an Income Statement. She has determined that she must make at least $75,000 profit per year in order to Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-202978","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/202978","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=202978"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/202978\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=202978"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=202978"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=202978"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}