Adair 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.

1. Open Week5_Lab6_Adair_Daycare_Student.xlsx. Notice that Adair has provided the basic assumptions concerning her costs and her revenue.
2. Complete the following calculations. Revenue
   • Total Revenue: Multiply tuition per day times number of days by number of children.
   Variable 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.
   • Food Expenses
   • Supply Expenses
   Teacher 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.Total Variable expenses: This will be the SUM of all of the Variable Expenses (B17:B19).Summary. 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.Total Revenue should refer to the Total Revenue cell (B14).Total Expense is Variable Costs + Fixed Costs.Net Income is Total Revenue – Total Expense
3. 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

This screen displays an income statement for the company, Day Care Wonders. The spreadsheet includes data fields for the following categories: Assumptions, Annual Revenue, Annual Variable Expenses, Annual Fixed Expenses, and a Summary for Total Revenue and Total Expenses.

Press the ESC key to close the image description and return to the page.STEP 2: Analyze the Difference in Total Expenses and Net Income When You Vary the Number of Students

Adair 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—and see the results on multiple outputs.

1. Set up the Data Table to display number of children from 6 through 15. Add a descriptive title to the Data Table.
2. Show the Expenses and Net Income for each change in number of children.
3. Populate the Data Table using the Data tab, What-If analysis, Data Table tool.
4. Apply Conditional Formatting to Net Income that is above $75,000.

Your final Data Table should look something like this.STEP 3: Analyze the Difference in Net Income When You Vary the Number of Students and the Charge Per Student

Adair 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.

1. Set up the Data Table to display number of children as the row input (6–15) and charge per student (35–75 in $5 increments) as the column input. NOTE: 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.
2. Enter Net Income in the result cell, and format this cell to display no data.
3. Add a descriptive heading.
4. Create the data table and apply conditional formatting to Net Income values over $75,000.

Your Data Table will look something like this.STEP 4: Create Scenarios and a Scenario Summary

Adair 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.

She wants to see the Net Income that would be achieved in each Scenario and compare this to her original assumptions.

1. Name the cells that will be used in the Scenario.
2. Create the three Scenarios.
3. Create a Scenario Summary.
4. Move the Scenario Summary to the end of the workbook.

STEP 5: Create a Documentation Sheet and Provide a Recommendation

1. Add a documentation sheet to the beginning of the workbook. Include Author, date Created, Last Modified, and Contents sections.
2. Provide a recommendation to Adair.
3. Format the documentation sheet to match the look and feel of the other sheets in the workbook.
4. Add a graphic to the documentation sheet.

 ATTACHMENT PREVIEW Download attachmentDay Care WondersAuthoryour nameDate created:2/10/2013Today’s date:2/6/2016Purpose:Learning Objective:Sheets:Income Statement – Income StatementScenario Summary – Shows the summary for different scenarios.Recommendation:Create an Income Statement and create different scenarios so that Jane Morales canmake a decision based on number of children and total revenue.Putting together the assumptions and create an Income statement. Given a business caserequiring a forecast of future sales, profits, or expenses, develop a worksheet that canfacilitate decision-making.Jane must charge atleast $50 as Tuition per child so that the business is profitable. For herto meet the goal of making a profit of $75,000, Jane must charge $55 and have atleast 12children throughout the year or she can charge $65 and have 10 children.Furthermore, she can play around with the Income Statement and see if she can reducethe expenses, hence making more profit.

View the AnswerDay Care WondersIncome StatementTeacher:Student Ratio RequiredStudentsTeachers11Assumptions72Number of Children/Day6133Average Days per Year250194Teacher Cost / Year38,000ChildrenExpensesNet IncFood/Child/Day$1.256$64,975$85,025Supplies Child/Year$100.006$64,975$85,0257$103,388$71,613Annual Revenue8$103,800$96,200Tuition per Day$100.009$104,213$120,788Total Rev$150,00010$104,625$145,37511$105,038$169,963Annual Variable Expenses12$105,450$194,550Food Expenses1,87513$143,863$181,138Supplies per Year60014$144,275$205,725Teacher Cost38,00015$144,688$230,313Total Variable Expenses$40,475Goal$75,000Annual Fixed ExpensesInsurance5,000Maintenance6,500Administrative & Advertising1,000Est. Taxes12,000Total Fixed Expenses$24,500SummaryTotal Revenue150,000Total Expenses64,975Net Income$85,025Tuition per DayProfitabilityNet Income$35.00$45.00$55.00$65.00$75.00Children6$85,025$2,525$17,525$32,525$47,5257$85,025($24,638)($7,138)$10,363$27,8638$85,025($13,800)$6,200$26,200$46,2009$85,025($2,963)$19,538$42,038$64,53810$85,025$7,875$32,875$57,875$82,87511$85,025$18,713$46,213$73,713$101,21312$85,025$29,550$59,550$89,550$119,55013$85,025$2,388$34,888$67,388$99,88814$85,025$13,225$48,225$83,225$118,225

Show entire documentSign up to view the entire interaction

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"