devry-bis155-all-week-ilab-latest-2016-march

Want answers to the assignment Below?

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


 

  

devry
bis155 week 1 ilab Latest 2016 March

> STEP 6: Create Charts to Represent the Data

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

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

After completing the steps below, turn in one Excel 2013 workbook.
Rename the workbook with your lastname_first initial_Week1_iLab.xlsx. EXAMPLE:
If your name were Jane Doe, your workbook would be Doe_J_Week1_iLab.xlsx.

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

Click to download the.equella.ecollege.com/file/8c321e6d-b6d5-4fb8-add7-b228c844d079/49/BIS155_W1__Lab_1_Grading_Rubric.docx”>Grading Rubric for Lab 1

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

Preparation

Be sure you have reviewed the tutorials located on the Lesson page
before you begin the Lab.

Please do not rely solely on the videos to
complete this week’s lab.
The videos provide detailed examples, using the Week 1 OK Office
Systems—Student.xlsx workbook. Applying the examples to the Fundraising
assignment will give you both practice and instruction of what to complete.

For example, your final assignment file should include five
worksheets (Documentation, Sales-Data, Profit by Product, Percent Sales Graph,
and Profits-by-Day), each with a corresponding example, as shown in the video
tutorials.

For step-by-step instructions on how to open a document in the
iLab environment click.equella.ecollege.com/file/8c321e6d-b6d5-4fb8-add7-b228c844d079/49/Opening_a_Document_in_the_iLab_Environment.pdf”>here.

STEP 1: Opening and Saving

Download the spreadsheet,.equella.ecollege.com/file/8c321e6d-b6d5-4fb8-add7-b228c844d079/49/Week1_iLab_Capital_Fundraising_Student.xlsx”>Week1 iLab Capital Fundraising
Student.xslx. You will be prompted to save the file. Click yes.

Open the saved file from your Download folder on your computer.

Note: If you are using the Remote Lab environment you will need to
follow the instructions for uploading the file. These instructions can be found
on the iLab page when you click on the iLab icon on Course Resources.

To save the spreadsheet with a new file name

· open the workbook in Excel 2013; and

· in Excel, click File then Save as and rename it as
lastname_first initial_Week1_iLab.xlsx (Jane Doe would save the file as
Doe_J_Week1_iLab.xlsx).

STEP 2: Adding a Documentation Sheet

Insert a new spreadsheet into the workbook.

Change the name from Sheet 2 to Documentation. The Documentation
Sheet should be the first sheet in the workbook.

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

STEP 3: Format Your spreadsheet

Apply formatting to your spreadsheet.

Your goal is to take into consideration readability, reliability,
and re-usability. Remember that presentation has an impact on the viewer, and
professional appearance is important for reporting data, just as personal
appearance impacts perception. The goal is to make the data more readable, and
possibly to highlight data that might otherwise be lost in many numbers.

The minimum formatting requirements (per the Grading Rubric) are:

Title Rows formatted as titles

Font Size changes

Color Changes

Use of Shading and Borders

Currency and numeric formatting

Note: Refer to the Formatting video in the lesson tutorials for
help with this step.

STEP 4: Add Formulas

A. Create the formulas needed to calculate the following for each
item:

Net Selling Price = Selling Price * (1 – Sales Discount)

Total Sales = Sum(Daily Sales) * Net Selling Price

Total Profit = Total Sales – (Cost per item * sum(Daily Sales))

Average Profit % = Total Profit / Total Sales

B. Create Grand Totals for the Total Sales and the Total Profit
columns.

C. Create the formula that will average all of the item profit %.

D. Create formula to calculate the Remaining Profit needed to make
the Marching Band goal for the fundraising campaign.

Remaining Profit = The Profit Goal – Total Profit (all items)

One of the strengths of Excel is its ability to make tedious and
repetitious calculations, for speed and to reduce errors. Like all tools, there
is always the possibility that you could use it incorrectly, so with formulas,
it is important to verify the formulas you enter are correct.

Note: Refer to the Creating Formulas video in the Lesson tutorials
for help with this step.

If you have done the Tutorial on formulas, you will have noticed,
during the creation of the formulas, that there are many ways to accomplish
similar results. Remember that arithmetic formulas follow an order in which
calculations are performed. I make it a personal habit to always use
parentheses () to order the calculations in the order in which I want them
performed, so as not to make a mistake.

For example, if I enter the following formula into Excel, what is the outcome
that I wanted?

6 + 4 * 10

Did I mean to have a result of 100 [(6+4) * 10] or 46 [6 + (4 * 10)]. Excel
would give you 46.

Excel will try to follow the following order of execution whenever possible,
but I would use the best practice of always using parentheses to be certain of
what you want.

1. Parenthesis

2. Exponents

3. Multiplication and division

4. Addition and subtraction

STEP 5: Clean-Up

Clean-up the formatting of your report, taking into account
professional appearance.

The minimum requirement (per the Grading Rubric):

Apply a color to each tab (sheet) in the workbook

Assign a descriptive name to each tab (sheet) in the workbook

Printing Setup (for each sheet)

o Create a Custom Page Header

o Set the page orientation to landscape

Note: Refer to the Formatting Cleanup video in the lesson
tutorials for help with this step.

Remember, professional appearance doesn’t only promote better
readability of your report, but represents you as a professional.

STEP 6: Create Charts to Represent the Data

Because a picture is worth a thousand words, create each of the
charts below to further enhance your report. Each chart should be on a separate
sheet in the workbook.

· Pie chart

· Bar chart

· Line chart

Note: Refer to the Creating Bar Charts, Pie Charts, and Line
Charts videos in the lesson tutorials for help with this step. Once you have
completed the tutorials, you can apply your skills to this step of the
assignment.

Be sure to properly label the charts – do not leave default titles
such as “Axis” or “Series”.

STEP 7: Save and Submit

Save your Excel file.

Your Excel file should contain five worksheets.

· Documentation

· Sales-Data

· Profit by Product

· Percent Sales Graph

· Profits-by-Day

Submit your completed Excel file to the Dropbox (see the
Deliverables above).

devry
bis155 week 2 ilab Latest 2016 March

.equella.ecollege.com/file/58ff92b1-b8b5-4a89-b378-381466be75f9/32/BIS155_W2_iLab.html”>iLab>
Step 4: Create an Excel Application With Macro and Macro Button

.equella.ecollege.com/file/2c8d44cf-16de-4684-900a-8088186a0f88/1/ReadSpeaker.html”>What’s this?

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 should be appropriate for the Board of Directors for the
First National Bank.

Be sure to watch the tutorials at the end of theLessonpage.

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

Submit one workbook 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 iLab activity.

File naming convention: If your name is Jane Doe, then your file
should be named very similar to: Doe_J_Week2_iLab.xlsm

Click to download the.equella.ecollege.com/file/58ff92b1-b8b5-4a89-b378-381466be75f9/32/BIS155_W2__Lab_2_Grading_Rubric.docx”>Grading Rubricfor Lab 2

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 labto 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

Preparation

Be sure you have reviewed the Tutorials located on the Lesson page
before you begin the iLab.

Please donotrely solely on the videos to complete
this week’s iLab.The videos provide detailed examples, using the
Week2_BIS155_Tutorial_Loan_Calculations_Student.xlsx workbook. Applying the
examples to the loan assignment will give you both practice and instruction
what to complete.

For example, your final assignment file should include three
worksheets (Documentation, Loan Data, and Loan Calculator), each with a
corresponding example, as shown in the video tutorials.

Step 1: Open, Save, and Document the Workbook

A. Download the.equella.ecollege.com/file/58ff92b1-b8b5-4a89-b378-381466be75f9/32/Week2_iLab_FirstNatlBank_Students.xlsx”>Week2_iLab_FirstNatlBank_Students.xlsx
Workbook. Open the file in Excel, and save it as the file name as describe
above (i.e., Doe_J_Week2 iLab.xlxsm). [Note: You may get a popup message that
asks you to “Enable Saving”. Simply click “Yes” to Enable
and you will be able to save the file]

B. It is recommended that as you progress through this assignment,
that you work on what you have learned so far, in terms of readability. Be sure
to keep your worksheet professional with easy to read column headings, shading,
spacing, boxes, logos, and so on. Remember to label your worksheets with
appropriate labels (i.e., not sheet1, sheet2, etc.).

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

Below is an example of what your documentation page may look like.
Future assignments will all be similar, when asked to create a documentation
page.

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

Step 2: Create Calculations

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.

A. Use a VLOOKUP function to determine the interest rates in
column D. There is a tutorial video on creating and using VLookup tables at the
end of the Lesson page.

B. Calculate the down payment by multiplying the results of a
VLOOKUP function by the selling price. Enter the formula in column E.

C. Calculate the amount to be financed by subtracting the down
payment from the selling price. Enter the formula in column F.

D. Use a PMT function to determine the monthly payments in column
G. You should assume that the payment is being made at the beginning of the
month. Remember that the Interest Rate being shown is an annual percentage rate
(APR), yet the payments are being made monthly. Be sure to make your adjustment
to the interest rate for this. In addition, the term of the loan is being shown
in years. Be sure to adjust the years to months in your PMT function argument.

E. Use appropriate formulas and functions to calculate our loan
statistics for the month.

· Number of Loans

· Lowest Amount Financed

· Total Amount Financed

· Highest Amount Financed

Step 3: Format the Worksheet

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.

A. Format all money figures as currency with two decimals and all
percentages as percents.

B. Adjust columns sizes to fit the data.

C. Merge and center titles and size appropriately.

D. Change font color to dark blue.

E. Add a small graphic appropriate for the purpose of the
worksheet.

F. Separate sections of the worksheet and provide borders as
appropriate.

Step 4: Create an Excel Application With Macro and Macro Button

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

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

1. What is the price of the home?

2. Do you wish to put any additional money down?

3. Pay at the beginning of the month?

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

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

E. Unlock the cells in which users will input data so that you may
protect the sheet to keep users from changing other areas.

F. Create a Macro.

G. Use Insert Shapes to add a Macro button that will allow users
to clear values and start over. Name the button Enter New Values.

H. Protect the worksheet, but do NOT require a password.

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

Remember This

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

devry
bis155 week 3 ilab Latest 2016 March

.equella.ecollege.com/file/23685acf-413b-4965-909f-8038a1be37ff/25/BIS155_W3_iLab.html”>iLab>
STEP 6: Create a Pivot Table

·

·

iLab Overview

Scenario/Summary

You are an intern with the Regional Realty Association and are
analyzing the claim made by Luke Liberty Realty that “we get your
price.” You have prepared a spreadsheet that shows data relating to 3
months’ sales by Luke Liberty 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

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

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

File naming convention: If your name is Jane Doe, then your file
should be named very similar to: Doe_J_Week3_iLab.xls

Click to download the.equella.ecollege.com/file/23685acf-413b-4965-909f-8038a1be37ff/25/BIS155_W3__Lab_3_Grading_Rubric.docx”>Grading Rubricfor
Lab 3

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 labto 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

Preparation

Be sure you have reviewed the Tutorials located on the Lesson page
before you begin the iLab.

Please donotrely solely on the videos
to complete this week’s lab.
The videos provide
detailed examples using the supporting documents. Applying the examples to the
loan assignment will give you both practice and instruction on what to
complete.

For example, your final assignment file should include seven
worksheets (Documentation, Sales Data, Sort Data, AskvsSellbyAgent, Filtered
Data, Conditional Format, Pivot Table), each with a corresponding example, as
shown in the video tutorials.

STEP 1: Open, Save, and Document the Workbook

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

A. Download the workbook.equella.ecollege.com/file/23685acf-413b-4965-909f-8038a1be37ff/25/Week3_iLab_LibertyRealty_Student.xlsx”>Week3_iLab_LibertyRealty_Student.xlsx

B. Open the workbook in Excel, and save it according to the file
naming convention above.

C. 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.”

STEP 2: Add Calculations and Format Sales Data Worksheet

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.

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

B. Format Columns D and E as currency, with no decimals. Format
Columns G and H as dates so just the day and month (e.g., 5-May) are visible.
You will need to use the Number Format dropdown list, select More Number
Formats, and select the Date category.

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

STEP 3: Sort Data and Create Summary Report and Chart

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

B. In order to sort the data, you may want to convert them to a
data table. Format the data table to look attractive.

C. Sort the data by selling agent (A to Z) and then by asking
price (largest to smallest).

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

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

STEP 4: Filter the Data

A. Create another copy of the original sales data. Rename the
sheet Filtered Data.

B. Convert the range to a table, and then filter to show only
those properties sold in Miami by agent Carey.

STEP 5: Apply Conditional Formatting

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.

A. Select the data range and insert a table.

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

C. Use Percent of Asking Price as the Sort Key, and sort by color
with green first, then yellow, and then red.

STEP 6: Create a Pivot Table

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.

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

B. Create a pivot table based on the named range, SalesData.
Create the pivot table in a new worksheet.

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

D. Rename the sheet PivotTable, and move it so that it is the last
tab in the worksheet.

STEP 7: Draw a Conclusion

On the Documentation Sheet, add a section called Analysis. Based
on your data analysis, do you believe that Luke Liberty 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.





devry
bis155 week 4 ilab Latest 2016 March

.equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/BIS155_W4_iLab.html”>iLab>
STEP 2: Create Merge Document

·

iLab Overview

Scenario/Summary

You work with the Student Education Trust, which allows you to
demonstrate your expertise with Excel. The trust is sponsoring an auction, and
you have received a list of all donors who have contributed to this auction.
The list was pulled from their database as a comma-separated text file. You
have been asked to create a letter that will go out to each of the contributors
that will accept their donation. Tickets to the event will be enclosed. The
letter requires that you provide the following pieces of information.

· Full name and address

· First name

· Donated item

· Value

· Number of tickets requested

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 spreadsheet and two Word documents.

You will submit the following three items: one Excel spreadsheet,
one Word document showing 25 letters, and one Word document showing 25 labels.
When submitting the Excel workbook and the Word documents, provide a comment in
the Dropbox comments area explaining what you learned from completing this iLab
activity.

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

Doe_J_Week4_iLab_data.xlsx,
Doe_J_Week4_iLab_letters.docx, and
Doe_J_Week4_iLab_labels.docx

Click to download the.equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/BIS155_W4__Lab_4_Grading_Rubric.docx”>Grading Rubricfor Lab 4

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 labto 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

Preparation

Be sure you have reviewed the Tutorials located on the Lesson page
before you begin the iLab.

Please donotrely solely on the videos
to complete this week’s lab.
The
videos provide detailed examples, using the supplied files. Applying the
examples to the lessons will give you both practice and instruction on what to
complete.

STEP 1: Import Text File Into a Spreadsheet and Clean up Data

A. Download the text file:.equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/Week4_iLab_MailingData_Student.txt”>Week4_iLab_MailingData_Student.txt.
To complete the download, right click on the file name and select Save Link As.
The file will save to your local drive.

B. Open a new Excel file and import only the data from the txt
file you will need for your letter (i.e., excluding unnecessary columns).

C. Separate the first and last names into two columns so that you
may use the first name in a Mail Merge. Make sure the fields are named LastName
and FirstName.

D. Check the accuracy of all data by using the Spelling tool in
the Proofing group.

E. Add a documentation sheet that provides a spreadsheet title,
author, date, and purpose. Format the documentation sheet by applying a
professional color scheme to the text. Ensure that the sheet tab is named
Documentation and that the documentation sheet is the first sheet in the
workbook.

STEP 2: Create Merge Document

A. Open the.equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/Week4_iLab_Trust_Template_Student.docx”>Week4_iLab_Trust_Template_Student.docxdocument in Word. Make sure that the
document is in the same folder as the Excel spreadsheet. This will make it
easier to work with the two documents during the Merge process.

·Note:Be sure to name this file, using the
naming conventions listed above.

B. Merge information from
your Excel spreadsheet into the Word document. Your completed merge will look
like the following when complete.

9/27/2015

Student Education
Trust

123 Adams Street
Pittsburgh, PA 15697

Shelly Martin
123 North Street
Pittsburgh, PA 15697

Dear Shelly,

Thank you for your generous donation to the Student Education Trust Fundraiser.
We are delighted to accept the following donation.

Item: Massage

Item Value: $50.00

Enclosed you will find the 2 tickets you requested for this Fundraising event,
which will take place on Friday, October 24, 2015. We will be looking forward
to seeing you and giving your personal thanks.

Thank you again for you support of the fund. The proceeds will support
international annual rescue efforts.

With Special thanks,

Jeffrey McMillan
President
Student Education Trust

Accept all spelling suggestions in the document, and save document
as described above.

STEP 3: Create Address Labels

Create labels for the letters. The Student Education Trust wishes
to use mailing labels to address the envelopes. They own 8 1/2 x 11″
sheets of label paper with 1″ by 4″ labels, which they purchased from
the local Office supplies store. Note: Select a label type that is similar to
this (i.e., 3 labels across, and about 4″ deep).

Note:Be especially aware of the extra
spacing that Microsoft automatically provides (i.e., space after paragraph),
and be sure to make sure the labels are single spaced.

A. Create a new file for printing labels.

B. Merge data from your Excel spreadsheet into the new label
document. You can assume that a standard address block is required for the
labels.

Submit your completed workbook and the two Word documents (merged
letters and merged labels) to the Dropbox. Make sure you post a comment about
what you learned when submitting the files.




devry
bis155 week 5 ilab &6 Latest 2016 March

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
Rubricfor 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 labeledWeek 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 labto 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.xlsxthe 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).

.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 labeledWeek 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”>Grading Rubricfor
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 labeledWeek
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 labto 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

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

o
Food Expenses

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

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

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.

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 labeledWeek 5: iLab 6.



devry
bis155 week 6 ilab 7 Latest 2016 March

.equella.ecollege.com/file/03a32993-e116-42fb-91e2-2296ff02b3bf/30/BIS155_W6_iLab.html”>iLab>
STEP 3: Create the Three-Scenario Analysis

·

iLab Overview

Scenario/Summary

The purpose of this exercise is to use the Excel skills you have
acquired thus far, and apply them to an analysis of a business scenario, of
your choice. This will consist of preparing the groundwork in Excel (importing
data, preparing the analysis using tables, charts, graphs, pivot tables, etc.)
and preparing a memo to your management, explaining your three separate
analysis.

This is different than prior iLab assignments, in that there are
no specific instructions as to what type of analysis (i.e.,
Sales/Units/discounts by Country/Rep/Product/Date/Category, etc.) is to be
performed. The general requirement is for you to prepare a minimum of three
discrete analyses and perform a write-up to management, using the memo format provided.

Remember that you are making a professional analysis and
presentation, so be sure to give some thought to what you are trying to
recommend to management, and why.

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

Important:
There aretwofiles that you will submit this week
to the Dropbox.

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

Doe_J_Week6_Analysis.xlsx
Doe_J_Week6_Memo.docx

Click to download the.equella.ecollege.com/file/03a32993-e116-42fb-91e2-2296ff02b3bf/30/BIS155_W6__Lab_7_Grading_Rubric.docx”>Grading Rubricfor Lab 7

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 labto 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: Import Your Data Into Excel

The ultimate goal is to prepare a memo to management, using the.equella.ecollege.com/file/03a32993-e116-42fb-91e2-2296ff02b3bf/30/Week6_iLab_Memo_Template_Student.docx”>Week 6 iLab
Memo Template. Before you begin working with Excel, it might be a good idea to
take a look at this template, so that you can envision your end results.

In this first step you will open Excel and import the data from.equella.ecollege.com/file/03a32993-e116-42fb-91e2-2296ff02b3bf/30/Week6_iLab_SalesData_Student.txt”>Week 6 iLab
Sales Data. This is a comma delimited file with column headers, and you
should be sure to format the “Order Date” as a date type column.
Remember to save the file as an Excel workbook, using the naming convention
shown above. I don’t believe there is any reason to perform a spell check, as
there are just too many unique names of products in this data.

Using a company name of your choice, format this data sheet in a
professional looking manner. Given the fact that you have a unit price and
quantity, it might help you to create another column to show the gross revenue.

STEP 2: Convert Data to a Table Format

In this step, create a new worksheet, using a copy of your
original data. Convert the data to a table format. You may choose to use this
table to begin your analysis, through the use of sorting, filters, subtotals,
and so on. If you choose to do so, be sure to include an appropriate page title
and sheet name to describe your work.

STEP 3: Create the Three-Scenario Analysis

This is an opportunity to show your intuition, creativity, and
Excel skills, so be sure to provide good examples here, for each scenario that
you present.

Analyze the Data

As mentioned, analyze the data in at least three different ways.
Each form of Data Analysis should be provided on a separate, appropriately
labeled worksheet. It is expected that each sheet will be professionally
formatted and clearly documented with titles, comments, and explanation. Remove
any extra sheets so your workbook is as compact as possible when you turn it
in. Go back and review your iLabs for assistance in completing the different
types of analysis. There is a memo template that you will be using to present
your analysis.

The following are some examples of analysis you might wish to do.

· Sort by discount level. Have discounts increased sales volumes?
Is there any discount level that appears to be more effective than others?

· Graph sales over time to see trends. Are there any peaks and
lows in sales? Is there any time of year in which sales are highest? Lowest?

· Pivot the data to see total sales by quarter, country, category,
and salesperson. Are there any highs? Are there any lows that need to be
addressed?

· Subtotal the data. How are quarterly sales totals? Sales totals
by salesperson? By country?

· Challenge Option: Perform What-If analysis. What if prices were
raised by a certain percentage with a slight decline in sales? What combination
of price increases and decline in sales makes the most sense?

STEP 4: Prepare the Management Analysis Memo

In this step you will use your Excel analysis, and prepare a write
up of your results, using the Week 6 iLab Memo Template Microsoft Word
document. Be sure to rename this file, using the file naming conventions shown
above.

As the template states, your memo should include any textual
explanation of your analysis, to include any graphics that were presented in
your workbook. The write-up of your analysis should be formatted
professionally. Be sure to review your writing for spelling and grammar
structure.

Your memo will focus at least one paragraph on each of the Data
Analysis sheets in your workbook. Explain the type of analysis you completed,
the conclusions you drew, and a recommendation based on the analysis.

You should include a final paragraph that explains three things
you learned about using Excel for data analysis. Be sure to submit your work
for this iLab to the Dropbox basket labeledWeek 6: iLab 7.



devry
bis155 week 7 ilab 8 Latest 2016 March

.equella.ecollege.com/file/1f71be11-64b2-465e-a667-b0f4510202c2/20/BIS155_W7_iLab.html”>iLab>
STEP 9: Regression Analysis

iLab Overview

Scenario/Summary

Hopefully you will find
this week’s iLab activity fun and useful. We’ll be exploring the world of
statistics from a business perspective this week, allowing you to practice your
skills with descriptive statistics, formatting, graphs, and regression analysis.

As discussed in the
lesson, the value of statistics lies in the ability to analyze data more
effectively for the purpose of improving decision making. You might have heard
the expression that “statistics never lie, and only liars use statistics”.
There is an obvious truth in this statement, in that, depending on the
questions being asked and the data collected, the statistics can skew reality.

For example, it is true
that as ice cream sales increase, accidents at swimming pools increase. Does
this mean that the more ice cream that is sold, the more accidents it causes
(correlation/causation)? Of course not, but the data, if not interpreted
correctly, could lead to false conclusions. It just so happens that both are
correlated to a rise in temperature in the summertime. The hotter it is
outside, the more kids flock to swimming pools, leading to more accidents, and
the more ice cream is sold. So you see, although statistics are vital in the
world of decision making, you have to be wise, and ask the right questions.

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. The workbook will consist of nine separate worksheets,
including the Documentation sheet.

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

Click to download the.equella.ecollege.com/file/1f71be11-64b2-465e-a667-b0f4510202c2/20/BIS155_W7__Lab_8_Grading_Rubric.docx”>Grading
Rubricfor Lab 8

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 labto 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: Getting
Started—Worksheet Template

Please download this
week’s iLab file:.equella.ecollege.com/file/1f71be11-64b2-465e-a667-b0f4510202c2/20/Week7_iLab_Statistics_Student.xlsx”>Week7_iLab_Statistics

Your first step should be
to save and rename this file according to the naming convention above.

It is recommended, as you
work on this iLab, that you save your work often.

STEP 2: Create a
Documentation Page

This will be a similar
documentation page that you have used for all prior iLabs. Please refer to
instructions in iLab 1 for detailed instructions.

Be sure to place the
documentation sheet as your first sheet.

STEP 3: Descriptive
Statistics

TheData_1971_2000worksheet is already loaded with data
for you, which is the actual temperatures for all of the U.S. states between
1971 and 2001. As you can see, the data already contains theaveragetemperature for each state, in both
Fahrenheit and Celsius, along with the ranking of the states, in terms of
warmest average temperature (#1) to the lowest.

1. Freeze the top row, so
that the column headers are visible as you scroll through the data.

2. At the bottom of the
page, you are asked to provide the Count, Average, Median, Mode, Min, and Max
for each of the states for each of the data columns. The shaded area at the end
of the states is where these descriptive statistics should be entered.

3. To the right of the
data, starting at approximately Texas (row 44), use the Data Analysis feature
to display the summary descriptive statistics for each temperature and the
rank. Be sure to shade and format your descriptive statistics (similar to the
shading in Step #2 above) so as to be able to read everything well.

As you read your results,
you might note some interesting results. First and foremost, note how the
statistics associated with the rankings are virtually worthless, as they really
don’t provide any insight to the data itself. This is a little of what I meant
above when I talked about some statistics are junk, and you have to be careful
in how you ask your questions and interrupt the results.

STEP 4: Bar Chart and
Summary Statistics

Using theBarChartworksheet, calculate the summary
statistics shown at the bottom of the data, for each of Bottles, Cans, and
Plastic.

Create a bar chart to the
right of the data, with a title of Marketing Campaign Results. You can choose
the

  

Other Questions.

We Accept

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

X