devry-bis155-week-5-ilab5-ilab-6-latest-2016-march

Want answers to the assignment Below?

Text or Whatsapp Olivia at +1 (307) 209-4351


 

  

Ilab -5

.equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/BIS155_W5_iLab.html#”>iLab > STEP 4: Create a Documentation Sheet

iLab Overview

Scenario/Summary

The owner of Carina’s Pizza, wants to evaluate the profitability of the three restaurants before expanding further. They are particularly interested in the comparative results of three dining categories, dine-in, pickup, and delivery. Carina has asked for your help in doing this analysis in return for a small stipend and all the pizza you can eat. You have already prepared a template and distributed it to each restaurant manager, who has entered the sales data for last year. Your next task is to consolidate the data into a single workbook that shows the total sales for each quarter and each dining category. The information should be shown in tabular, as well as graphical formats. You will also create a documentation sheet so that Joe will know exactly what you have created.

Deliverables

NOTE

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these .equella.ecollege.com/file/8ff9f27a-3772-48cf-9855-4bec4e6706bf/1/Dropbox.html”>step-by-step instructions.

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

You will turn in one Excel workbook for this Lab.

File naming convention: If your name is Jane Doe, then your files should be named very similar to: Doe_J_Week5_iLab5.xlsx.

Click to download the .equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/BIS155_W5__Lab_5_Grading_Rubric.docx”>Grading Rubric for Lab 5

Remember This

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 (Lab 5) to the Dropbox basket labeled Week 5: iLab 5.

Required Software

Microsoft Office: Excel 2013

Options for accessing Microsoft Excel 2013:

  1. Use a personal copy on your PC. You can request a copy of Microsoft Office 2013 via the Student Software Store icon on the Course Resources Page
  2. If you are a MAC user, click to read the .equella.ecollege.com/items/2d5f4ce6-cbbd-4727-9910-eb5621ef0ed5/0/ZZZ_W2_MacSupport.html”>MAC User Information.
  3. Click .devry.edu/”>DeVry virtual lab to access Microsoft Excel in the virtual lab. For additional virtual lab information and tutorials on saving files, click the iLab icon on the Course Resources page.

Lab Steps

STEP 1: Open all Worksheets and consolidate into a summary worksheet.

You will be using .equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/Week5_BIS155_iLab5_Carinas_Summary_Student.xlsx”>Week5_Lab5_Carinas_Summary_Student.xlsx the partially completed Summary workbook and the completed .equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/Week5_BIS155_iLab5_Carinas_Northside_Student.xlsx”>Northside Workbook, .equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/Week5_BIS155_iLab5_Carinas_Southside_Student.xlsx”>Southside Workbook, and .equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/Week5_BIS155_iLab5_Carinas_Downtown_Student.xlsx”>Downtown Workbook.

Consolidate the data from the three completed worksheets into the Summary workbook (Week5_Lab5_Carinas_Summary_Student.xlsx).

  1. The individual worksheets for the three locations have been placed in the single workbooks (Northside, Southside and Downtown) for you. Be sure to practice the copying feature from multiple workbooks, as shown in the tutorial videos.
  2. Using the Consolidate feature and perform a summary consolidation of the three locations into the provided summary worksheet page.
  3. Rename the file that includes your consolidated summary worksheet. File naming convention: If your name is Jane Doe, then your files should be named very similar to: Doe_J_Week5_iLab.xlsx.
  4. Close the Northside, Southside and Downtown Workbooks. Leave your Summary workbook open.

STEP 2: Summarize Dining Categories by Quarter

You must provide a summary report of the dining category sales by quarter for the year so that Joe Bruno knows exactly what each location is doing.

  1. Create formulas to calculate sales by dining category and quarter on the Summary Sheet.
  2. Group the worksheets and add Grand Totals by Dining Category and by Quarter.

STEP 3: Create Graph

Carina is more of a visual person, and you want to present your data in an alternate way. You will create a chart to summarize your data.

  1. Create a chart on the separate sheet already in your workbook. Your chart should show the amount of income from each of the dining categories displayed by quarter. A trend line might help her to see the area of highest growth.
  2. Include a title, legend, and data labels on your chart.
  3. Format the chart to coordinate with your summary table, using the same colors, fonts, titles, and clip art.

Need help? Review the Week 1 step-by-step instructions for charting. Hint: Select clustered columns as your basic chart type. The following is a suggested format (your answers may vary).

devry bis155 week 5 ilab5 & ilab 6 Latest 2016 March.equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/BIS155_W5_Lab5_ExampleGraph.png” alt=””>

.equella.ecollege.com/file/ac2f3a17-d457-4387-b66c-8bc2cf9a0785/41/BIS155_W5_iLab.html#descbox1″>Image Description

STEP 4: Create a Documentation Sheet

You must document your worksheet with a professional-looking documentation sheet before Carina can reimburse you for your work.

  1. Insert a new worksheet and rename the sheet tab Documentation.
  2. Include the following on your documentation worksheet: Your name as author, date of creation, date of last modification, purpose. Create a Contents section that lists the sheets in the workbook and provides a brief description.
  3. Format the documentation worksheet using the same colors and font used in other worksheets and charts.
  4. Hide the gridlines on the Documentation Sheet using the Page Layout tab, Sheet Options group.
  5. Group the worksheets and create a custom footer with your name on the left, the page number in the middle, and the instructor’s name on the right.
  6. Based on your analysis, do you think Carina should add another store to her chain? Why or why not? Provide a comment on the Documentation Sheet.

Submit your completed workbook to the Dropbox. Make sure you post a comment about what you learned when submitting the file.

IMPORTANT: 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 5.

Ilab -6

.equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/BIS155_W5_iLab.html#”>iLab > STEP 4: Create Scenarios and a Scenario Summary

iLab Overview

Scenario/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 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.

When submitting the workbook to the Dropbox, provide a comment in the Dropbox comments area explaining what you learned from completing this iLab activity.

Deliverables

NOTE

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these .equella.ecollege.com/file/8ff9f27a-3772-48cf-9855-4bec4e6706bf/1/Dropbox.html”>step-by-step instructions.

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

You will turn in one Excel workbook for this iLab.

File naming convention: If your name is Jane Doe, then your files should be named very similar to: Doe_J_Week5_iLab6.xlsx

Click to download the.equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/BIS155_W5__Lab_6_Grading_Rubric.docx”>.equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/BIS155_W5__Lab_6_Grading_Rubric.docx”>Grading Rubric for Lab 6

Remember This

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 (Lab 6) to the Dropbox basket labeled Week 5: iLab 6.

Required Software

Microsoft Office: Excel 2013

Options for accessing Microsoft Excel 2013:

  1. Use a personal copy on your PC. You can request a copy of Microsoft Office 2013 via the Student Software Store icon on the Course Resources Page
  2. If you are a MAC user, click to read the .equella.ecollege.com/items/2d5f4ce6-cbbd-4727-9910-eb5621ef0ed5/0/ZZZ_W2_MacSupport.html”>MAC User Information.
  3. Click .devry.edu/”>DeVry virtual lab to access Microsoft Excel in the virtual lab. For additional virtual lab information and tutorials on saving files, click the iLab icon on the Course Resources page.

Lab Steps

STEP 1: Open Worksheet and Complete the Income Statement

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 .equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/Week5_Lab6_Adair_Daycare_Student.xlsx”>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.

devry bis155 week 5 ilab5 & ilab 6 Latest 2016 March.equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/wk6_ilab_a.jpg” alt=””>

.equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/BIS155_W5_iLab.html#descbox1″>Image Description

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.

devry bis155 week 5 ilab5 & ilab 6 Latest 2016 March.equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/wk6_ilab_b.jpg” alt=”screenshot example of the final data in excel”>

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.

devry bis155 week 5 ilab5 & ilab 6 Latest 2016 March.equella.ecollege.com/file/c50bf910-9808-48be-9ad1-ea928d82f04f/19/wk6_ilab_c.jpg” alt=”screenshot example of the final data in excel”>

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.

Scenario Teacher Salary Supplies Number Tuition
1. Economy 15,000 25 15 35
2. Midrange 26,000 60 8 50
3. High 38,000 100 6 100

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.

Submit your completed workbook to the Dropbox. Make sure you post a comment about what you learned when submitting the file.

IMPORTANT: 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.

  

Other Questions.

We Accept

Order your Assignment today and save 15% with the discount code ESSAYHELP

X