Excel speardsheet
dunlap05
week_5_ilab_overview.dociLab 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.
Software Citation Requirements
This course uses open-source software, which must be cited when used for any student work. Citation requirements are on the Open Source Applications page. Please review the installation instruction files to complete your assignment.
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 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 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 MAC User Information .
3. Click 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 Week5_Lab5_Carinas_Summary_Student.xlsx the partially completed Summary workbook and the completed Northside Workbook , Southside Workbook , and Downtown Workbook .
Consolidate the data from the three completed worksheets into the Summary workbook (Week5_Lab5_Carinas_Summary_Student.xlsx).
A. 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.
B. Using the Consolidate feature and perform a summary consolidation of the three locations into the provided summary worksheet page.
C. 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.
D. 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.
A. Create formulas to calculate sales by dining category and quarter on the Summary Sheet.
B. 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.
A. 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.
B. Include a title, legend, and data labels on your chart.
C. 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).
This is a custom column chart titled Major Sales Increase in Delivery Service with vertical bars representing Dine-In, Pick-up, and Delivery amounts.
Over time, Dine-in sales decreased, Pick-Up sales stayed steady, and Delivery sales gradually increased.
Press the ESC key to close the image description and return to the page.
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.
A. Insert a new worksheet and rename the sheet tab Documentation.
B. 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.
C. Format the documentation worksheet using the same colors and font used in other worksheets and charts.
D. Hide the gridlines on the Documentation Sheet using the Page Layout tab, Sheet Options group.
E. 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.
F. 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.
