Final Exam

/in /by

Final Exam

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

You are the assistant to the band director for the Upper Saddle River Marching Band, and you must prepare a report showing the status of the marching band fundraising event for presentation to the board of trustees. The report will summarize all sales of all items and include the total profit-to-date with the amount remaining to reach the profit goal. You will open the partially completed workbook, create formulas, format for presentation to the board, and add charts to graphically depict the sales over time and by product.

Deliverables

After completing the steps below, turn in one Excel 2010 workbook. The workbook should be named Lab1_yourLastName.xlsx. When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity

iLAB STEPS

STEP 1: Open the workbook and add a documentation sheet (5 points)

Back to Top

Begin the lab by locating the spreadsheet, chap1_cap_fundraising.xslx, in Doc Sharing (Lab Materials). Open the workbook in Excel 2010, and rename it Lab1_your lastname.xlsx (Mary Jones would save the file as Lab1_jones.xlsx).

  • Step-by-Step Guide:Opening a File in the iLab Environment

Insert a new spreadsheet into the workbook. Create a Documentation Sheet. Include your name, the date, and the purpose of the spreadsheet. Your documentation sheet should look something like the one below:

Upper Saddle River Marching Band – Fundraising Event
Creator Your Name
Date Date
Purpose Board of Directors Presentation on Fundraising Sales to date

Need help? Click here for a Step-by-Step Guide: Creating the Documentation Sheet.

How to Use The Lab Videos
Please do not rely solely on the videos to complete this week’s lab.The videos may not exactly match the official lab instructions, and may not show all the steps required.For full credit, follow the written instructions on the main iLab page and the linked Step-By-Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you, by showing operations that are similar to, but not exactly the same as, those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.

STEP 2: Calculate Values (10 points)

Back to Top

You are to create the formulas needed to calculate the total sales for each item, the profit per item, and the profit based on the number of items sold to date. You will also create a formula to calculate total profit-to-date and the remaining profit needed to make the Marching Band goal for the fundraising campaign.

NOTE: In the data provided, there is no column for sales on 7/6/2006. The band did not do fundraising on that date due to bad weather. You do not need to adjust the dates or add a column for the skipped date; just use the sales data as given. The skipped date will not cause any problems.

  1. In cell C5, enter a formula to calculate profit per item. Profit per item is 50%. Format the cells that hold profit as Currency with two decimal places.
Lab 1, Step 2A VideoThis video walks through the current step(s) in this week’s lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2A Instructions.

 

  1. In column N, enter a formula to calculate the total sales for each item.
Lab 1, Step 2B VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2B Instructions.

 

  1. In column O, calculate the profit per item, which will be the total sales for each item multiplied by the profit per item.
Lab 1, Step 2C VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2C Instructions.

 

  1. In cell D15, calculate Total Profit-to-Date. In cell D16, calculate the remaining profit needed to reach the goal.
Lab 1, Step 2D VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2D Instructions.

 

  1. Create a formula to calculate the percentage of profit contributed by the sale of each item. All of the other calculations were based on “relative references.” For example, in cell O5, you created a formula N5 * C5. When you copied the formula to the next row, Excel automatically changed the reference to cell N6 and C6. In this instance, we want to divide the total profit from each item (O5 through O14) by the total profit overall (cell D15). The reference to the item will change, but the reference to the total profit to date will remain the same. We can use an absolute reference to do this. In cell P5, we will enter =O5/$D$15. The dollar signs tell Excel to always reference column D and row 15. Next week, we will learn more about relative and absolute cell referencing.
Lab 1, Step 2E VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2E Instructions.

 

STEP 3: Format the Worksheet (15 points)

Back to Top

Now that you have finished the formulas, you must format the worksheet in a professional manner so that it is suitable for presentation to the board of trustees for the college. Remember to focus on readability and reusability.

  1. Insert a comment in cell D16 to explain the formula underlying this cell.

Download the Lab 1, Step 3A Instructions.

  1. Format the spreadsheet as depicted below:

Download the Lab 1, Step 3B Instructions.

STEP 4: Layout the Worksheet (5 points)

Back to Top

Now that you have finished the major formatting, you must lay out the worksheet to further define and separate the areas of the worksheet. This step makes the worksheet easier to read and prepares for printing.

  1. Change the color of the first tab to a shade of brown.
  2. Rename the second tab Fundraising, and change the color to orange.
  3. Create a custom header with your name on the left and your instructor’s name on the right.
  4. Change the page orientation to landscape.

Download the Lab 1, Step 4 Instructions.

STEP 5: Create Charts (15 points)

Back to Top

Create charts based on the data in your worksheet. The charts provide information visually and help you analyze the information.

  1. Create a 3-D pie chart on a separate sheet that displays the percentage of profits for each item. Include descriptive titles and labels and a graphic pointer to the highest profit item. Your chart should look something like this:
Lab 1, Step 5A VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 5A Instructions.

 

  1. For the next chart, you will need to calculate the total profit per day. Here are the steps:
    1. Copy the daily profit data to a new sheet, and name the sheet Total Sales Data.
    2. In a new section of the worksheet, copy the Items in Column A and the Days.
    3. Then calculate the profit per day for each item
    4. Calculate the total profit per day.

Your data will look something like this:

Create a trend line chart on a separate sheet that shows the total profit by day. Include a descriptive title and labels. Include a callout that indicates the top profit day and label it “Kick Off Event.” Your chart will look something like this:

Make certain that all worksheets in the workbook have meaningful names. Upload the file to the Dropbox. Make sure you post a comment about what you learned when submitting the file.

Lab 1, Step 5B VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 5 Data Instructions (constructing the data set)
and Lab 1, Step 5B Instructions (creating the line chart).
*Note: The video incorrectly refers to “Sales Per Day”, when it should state “Profit Per Day”. Assume that you are working on Profit Per Day – thank you!

Print

 

Lab 2 of 7: First National Bank New Loan Report (50 Points)

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

You are an intern at First National Bank working in the loan department, and your boss has asked you to prepare the monthly “New Loan Report” for the Board of Directors. This analysis report will clearly list and summarize all new loans for residential housing in the past month. The summary area includes the loan statistics as labeled data in the data file. The format of the report is appropriate for the Board of Directors for the First National Bank.

Deliverables

Submit one workbook title Lab2_YourName.xlsm to the Dropbox. (Note that files containing macros have the extension, .xlsm rather than .xlsx.) When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

iLAB STEPS

STEP 1: Open, Save, and Document the Workbook (3 points)

Back to Top

  1. Locate the file chap2_cap_housing.xlsx in Lab Materials in Doc Sharing. Open the file in Excel, and save it as Lab2_yourlastname.
  2. Add a documentation sheet that provides a spreadsheet title, author, date, and purpose. Format the documentation sheet. The Bank’s logo is dark blue, so use a color scheme that reflects that logo. Select complementary Font and Fill colors for the documentation sheet. Ensure that the sheet tab is named Documentation and that the documentation sheet is the first sheet in the workbook.

(To get help on creating a documentation sheet, refer to Lab 1, Step 1.)

How to use the lab videos
Please do not rely solely on the videos to complete this week’s lab. The videos may not exactly match the official lab instructions and may not show all the steps required.For full credit, follow the written instructions on the main iLab page and the linked Step-By-Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you by showing operations that are similar to, but not exactly the same as, those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.

STEP 2: Create Calculations (10 points)

Back to Top

Functions are used to calculate the interest rate, down payment, monthly payment, and average selling price for each residential home in the worksheet. You need to create a formula to determine the down payment. Finish the calculations by using the appropriate functions to complete the Loan Statistics summary area of the worksheet.

  1. Use a VLOOKUP function to determine the interest rates in column D.
Lab 2, Step 2A VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2A Instructions.

 

  1. Calculate the down payment by multiplying the results of a VLOOKUP function by the selling price. Enter the formula in column E.
Lab 2, Step 2B VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2B Instructions.

 

  1. Calculate the amount financed by subtracting the down payment from the selling price. Enter the formula in column F.
Lab 2, Step 2C VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

No detailed written instructions are necessary for the Lab 2, Step 2C. The only written instruction needed is already listed after the outline letter C, above the Step 2C box.

 

  1. Use a PMT function to determine the monthly payments in column G.
Lab 2, Step 2D VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2D Instructions.

 

  1. Use appropriate formulas and functions to calculate over loan statistics for the month:
Lab 2, Step 2E VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2E Instructions.

 

Number of Loans, Highest Amount Financed, Lowest Amount Financed, Total Amount Financed.

STEP 3: Format the Worksheet (9 points)

Back to Top

Now that you have finished the calculations, you must format the worksheet in a professional manner suitable for delivering to the Board of Directors. Your final worksheet will look something like the worksheet below:

  1. Format all money figures as currency with two decimals and all percentages as percents.
  2. Adjust columns sizes to fit the data.
  3. Merge and Center titles and size appropriately.
  4. Change Font color to dark blue.
  5. Add a small graphic appropriate for the purpose of the worksheet.
  6. Separate sections of the worksheet and provide borders as appropriate.

STEP 4: Create an Excel Application with Macro and Macro Button (28 points)

Back to Top

NOTE: At the end of this step, you will save the file as an xlsm file rather than an xlsx file. This is the filetype that allows you to save a file that includes a Macro.

  1. Add a new worksheet to the workbook, and name it Loan calculator.

Download the Lab 2, Step 4 Instructions.

Lab 2, Step 4A VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

 

  1. Create a user input section that asks users to input the following:

What is the price of the home?

Do you wish to put any additional money down?

Pay at the beginning of the month?

Lab 2, Step 4B VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

 

  1. Create an Output section that uses the user-provided data and calculates the Mortgage payments for a 15-year loan, a 20-year loan, and a 30-year loan.
Lab 2, Step 4C VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

 

  1. Add user input validation to ensure that users input at least $50,000 for the cost of the home and answer Yes/No to the payment question.
Lab 2, Step 4D VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

 

  1. Unlock the cells in which users will input data so that you may protect the sheet to keep users from changing other areas.
  2. Create a Macro.
  3. Use Insert Shapes to add a Macro button that will allow users to clear values and start over. Name the button “Enter New Values.”
  4. Protect the worksheet, but do NOT require a password.
  5. Format the worksheet so that it is easy to understand and use. The following is a sample of what your final worksheet might look like:

Save your file as an xlsm file and submit to the DropBox. Make sure you post a comment about what you learned when submitting the file.

 

Lab 3 of 7: Alice Barr Realty Analysis (50 Points)

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

You are an intern with the Regional Realty Association and are analyzing the claim made by Alice Barr Realty that “we get your price.” You have prepared a spreadsheet that shows data relating to 3 months’ sales by Alice Barr Realty. You are going to determine the percent of asking price for each home sold and analyze the sales data to determine the true track record of the company in selling homes for the asking price.

Deliverables

Turn in one workbook titled lab3_yourlastname to the Dropbox. When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

iLAB STEPS

STEP 1: Open, Save, and Document the Workbook (5 points)

Back to Top

You must open a workbook that lists home sales for 3 months.

  1. Download the file chap4_cap_barr from DocSharing, Lab Materials.
  2. Open the workbook in Excel, and save it as Lab3_Yourlastname.xlsx.
  3. Insert a documentation sheet that includes title, author, date, and purpose. Format it appropriately, including changing the Sheet Name to Documentation. At the end of this activity, you will be asked to provide a conclusion as to whether this realty company “really gets your price.”
How to use the lab videos
Please do not rely solely on the videos to complete this week’s lab. The videos may not exactly match the official lab instructions, and may not show all the steps required.For full credit, follow the written instructions on the main iLab page and the linked Step by Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you, by showing operations that are similar to but not exactly the same as those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.

STEP 2: Add Calculations and Format Sales Data Worksheet (10 points)

Back to Top

A formula is used to calculate the percent of asking price, and this formula is applied to all listed sales. You will format the list in an attractive and useful manner.

  1. Calculate the percent of asking price for each sale (selling price divided by asking price). Enter the formula in Column F. Format Column F as a percentage with one decimal.
  2. Format Columns D and E as currency, with no decimals. Format Columns G and H as dates so just the day and month (for example, 5-May) are visible. You will need to use the Number Format dropdown list, select More Number Formats, and select the Date category.
  3. Widen columns to make all data and headings visible. Format titles as appropriate. Bold and center the column headings.

Your Sales Data worksheet will look something like this:

Lab 3, Step 2 VideoThis video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

 

STEP 3: Sort Data and Create Summary Report and Chart (10 points)

Back to Top

  1. Before analyzing data, it is a good idea to copy the data to a new worksheet so that you have a back-up copy of the original data. Right click on the Sales Data sheet tab and select Move or Copy. Create a copy of the current spreadsheet and move it to the end. Name the sheet Sorted Data.

Download the Lab 3, Step 3 Instructions.

Lab 3, Step 3A VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

 

  1. In order to sort the data, you must convert them to a data table. Format the data table to look attractive.
  2. Sort the data by selling agent (A to Z) and then by asking price (largest to smallest).
  3. Convert the sorted data back into a range, and create a summary report showing the total asking price and total selling price by selling agent.
Lab 3, Step 3D VideoThis video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

 

  1. Create a column chart that shows the difference by agent between asking price and selling price.

STEP 4: Filter the Data (5 points)

Back to Top

Download the Lab 3, Step 4 Instructions.

  1. Create another copy of the original sales data. Rename the sheet Filtered Data.
  2. Convert the range to a table, and then filter to show only those properties sold in Miami by agent Carey.
Lab 3, Step 4 VideoThis video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

STEP 5: Apply Conditional Formatting (5 points)

Back to Top

You determine that one measure of how well the company “gets your price” is to set a scale. Excellent performance is at or above the asking price. Good performance is between 95% and 100%. Below 95% is average performance. You will create conditional formatting rules to display graphically the performance level for each sale. Make a copy of the original sales data chart. Name the new sheet Conditional Format.

Download the Lab 3, Step 5 Instructions.

  1. Select the data range and insert a table.
  2. Use Advanced Formatting to set up the following conditional formatting rules for the field Percent of Asking Price.
    • Greater than or equal to 100% = GREEN
    • 95% to 99.9% = YELLOW
    • Less than 95% = RED
  3. Use Percent of Asking Price as the Sort Key, and sort by color with green first, then yellow, and then red.
Lab 3, Step 5 VideoThis video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

STEP 6: Create a Pivot Table (10 points)

Back to Top

Now that you have sorted, subtotaled, filtered, and conditionally formatted the data, you have a few more questions to answer. You want to see average percentage of selling price by agent and by city. This will allow you to see whether there are any patterns based on the city or based on the salesperson. To do this kind of comparison, you decide to create a pivot table.

Download the Lab 3, Step 6 Instructions.

  1. Open the Sales Data worksheet. Select the Sales Data range (A3:H33). From the Formulas tab, select Define Name from the Defined Names group. Name the range of cells SalesData.
  2. Create a pivot table based on the named range, SalesData. Create the pivot table in a new worksheet.
  3. Select Selling Agent as your Row Labels and City as your Column Labels. Select Percent of Sales as your Values. Change the Summary type for Percent of Sales to average, the Number Format to percent, and the Name to Average Percent of Asking Price. Apply Data Bars Conditional Formatting to the percentages. Hide the Field Headers.
  4. Rename the sheet PivotTable, and move it so that it is the last tab in the worksheet.
Lab 3, Step 6 VideoThis video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

STEP 7: Draw a Conclusion (5 points)

Back to Top

On the Documentation Sheet, add a section called Analysis. Based on your data analysis, do you believe that Barr Realty should be allowed to advertise that they “Get your price?” Justify your answer by referring to the data analysis you have just completed.

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

 

BIS 155

In-class Activity

These worksheets are to be completed in class. After completion, submit to Week 4’s iLab dropbox.

Sheet 1

Instructions

  1. Sort the data.

 

  1. Use an IF Function to add the Special Attachment fee to the Rental Fee if the client said “Yes” to get the Amount. The client will be charged just the Rental Fee if they do not want the Special Attachment.

 

  1. In Cell D11, put the total for Column D

 

  1. Format Column D for dollars. Remember, there are two ways to format numbers for dollars. Those two ways are: Accounting and Currency.

Format the numbers in Column D for Accounting.

 

  1. Give the sheet a meaningful name.

 

 

Sheet2

 

Instructions

 

  1. Sort the data.

 

  1. If the “Sold” price is no more than 2.5% less than the “List Price”, give agent a 4% commission of the price it sold for, otherwise give agent a 2% commission on the price it sold for.

 

  1. Format columns D through F for Currency (not Accounting)

 

  1. Give the sheet a meaningful name.

 

Sheet 3

Instructions

  1. Sort the data

 

  1. Use Conditional Formatting using yellow on Column D if the Date on Market is less than or equal to 80 days.

 

  1. Sort the worksheet by Date on Market in Descending order.

 

  1. Give the sheet a meaningful name.

 

Sheet 4

Instructions

 

  1. Use the VLOOKUP function to return the discount for Column D in the lookup table. The lookup table is located in cells A14 through C16.

 

  1. Calculate the Amount in Column E.

 

  1. Format Column D for percent.

 

  1. Format Column E for Currency.

 

  1. Calculate the Total in E10

 

  1. Give the sheet a meaningful name.

 

 

Lab 5 of 7: Bruno’s Pizza | Lab 6 of 7: Day Care Center

Lab 5 of 7: Bruno’s Pizza Analysis (50 Points each)

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

The owner of Bruno’s Pizza, Joe Bruno, wants to evaluate the profitability of his three restaurants before he expands further. He is particularly interested in the comparative results of three dining categories, dine-in, pickup, and delivery. Joe 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

Turn in one workbook, titled Lab5_yourlastname.xlsx. When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity. 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 (Lab 5) to the Dropbox basket labeled simply Week 5: iLab, not to the basket labeled Week 5: iLab – 6.

iLAB STEPS

STEP 1: Open all Worksheets and consolidate into a summary worksheet. (10 points)

Back to Top

Download the Lab 5, Step 1 Instructions.

You must open four workbooks–the partially completed Summary worksheet and the completed Westside, Eastside, and Downtown workbooks. Consolidate the data from the three completed worksheets into the Summary worksheet.

  1. From Doc Sharing, Lab Materials, open the chap7_cap files: Summary, Eastside, Downtown, and Westside.
  2. Copy the data from the Eastside, Downtown, and Westside workbooks into the Summary workbook so that the Summary workbook has four worksheets.
  3. Rename the Summary file lab5_yourlastname.xlsx.
  4. Close all files except the Summary Workbook.
How to use the lab videos
Please do not rely solely on the videos to complete this week’s lab. The videos may not exactly match the official lab instructions, and may not show all the steps required.For full credit, follow the written instructions on the main iLab page and the linked Step-By-Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you, by showing operations that are similar to, but not exactly the same as, those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.
Lab 5, Step 1 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guide.

** Please be patient when loading these videos as they can be large.

STEP 2: Summarize Dining Categories by Quarter (15 points)

Back to Top

 

Download the Lab 5, Step 2 Instructions.

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.
Lab 5, Step 2 VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

STEP 3: Create Graph (15 points)

Back to Top

Joe Bruno 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 a separate sheet. Your chart should show the amount of income from each of the dining categories displayed by Quarter. A trend line might help him 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 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):

Image Description

Lab 5, Step 3 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

Please be patient when loading these videos as they can be large.

 

STEP 4: Create a Documentation Sheet (10 points)

You must document your worksheet with a professional-looking documentation sheet before Joe Bruno 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 Bruno should add another store to his 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 (Lab 5) to the Dropbox basket labeled simply Week 5: iLab, not to the basket labeled Week 5: iLab – 6.

Lab 5, Step 4 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

Please be patient when loading these videos as they can be large.

 

End of Lab 5 of 7

Lab 6 of 7: Day Care Center (50 Points each)

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

Your friend, Jane Morales, 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.

Deliverables

Turn in one workbook, titled Lab6_yourlastname.xlsx. When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity. 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 (Lab 6) to the Dropbox basket labeled Week 5: iLab – 6, not to the basket labeled simply Week 5: iLab.

iLAB STEPS

STEP 1: Open Worksheet and Complete the Income Statement (15 points)

Back to Top

Jane 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. From Doc Sharing (Lab Materials folder), open wk5_chap8_daycare_income_statement.xlsx. Notice that Jane 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

  1. Rename the file Lab6_yourlastname.xlsx.

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

Lab 6, Step 1 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

Please be patient when loading these videos as they can be large.

STEP 2: Analyze the Difference in Total Expenses and Net Income When You Vary the Number of Students (10 points)

Back to Top

Download the Lab 6, Step 2 Instructions.

Jane 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 6 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:

Lab 6, Step 2 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

Please be patient when loading these videos as they can be large.

 

STEP 3: Analyze the difference in Net Income When You Vary the Number of Students and the Charge per Student (10 points)

Back to Top

Download the Lab 6, Step 3 Instructions.

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

  1. Enter Net Income in the result cell, and format this cell to display no data.
  2. Add a descriptive heading.
  3. Create the data table and apply conditional formatting to Net Income values over $75,000.

Your Data Table will look something like this:

Lab 6, Step 3 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides

Please be patient when loading these videos as they can be large.

 

STEP 4: Create Scenarios and a Scenario Summary (10 points)

Back to Top

Download the Lab 6, Step 4 Instructions.

Jane wants to look at three scenarios.

Scenario Teacher Salary Supplies Number Tuition
Economy 15,000 25 15 35
Midrange 26,000 60 8 50
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.
Lab 6, Step 4, Part 1 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

Please be patient when loading these videos as they can be large.

 

Lab 6, Step 4, Part 2 VideoThis video will demonstrate how to complete the current step(s) of this week’s Lab.

The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

Please be patient when loading these videos as they can be large.

 

STEP 5: Create a Documentation Sheet and provide a Recommendation (5 points)

Back to Top

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

 

 

smilesmile. .

get-your-custom-paper




The post Final Exam appeared first on nursing term paper.

 
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.