BIS 155 Uop Course-ShopTutorial
31 views | +0 today

# BIS 155 Uop Course-ShopTutorial

Curated by lakshmianu11
 Scooped by lakshmianu11

## BIS 155 Lab 3 of 7: Alice Barr Realty Analysis - ShopTutorial.com

BIS 155 Lab 3 | Alice Barr Realty Analysis
lakshmianu11's insight:

BIS 155 Lab 3 of 7: Alice Barr Realty Analysis

For more course tutorials visit

www.shoptutorial.com

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.
STEP 1: Open, Save, and Document the
Workbook (5 points)
You must open a workbook that lists home sales for 3 months.
Materials.
B. Open the workbook in Excel, and save it as
Lab3_Yourlastname.xlsx.
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 (10 points)
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 (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.
C. Widen columns to make all data and headings visible. Format
titles as appropriate. Bold and center the column headings.
STEP 3: Sort Data and Create Summary Reportand Chart (10
points)
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 must 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
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
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 Barr Realty should be allowed to
data analysis you have just completed.

No comment yet.
 Scooped by lakshmianu11

## BIS 155 Lab 1 of 7: Saddle River Marching Band - ShopTutorial.com

BIS 155 Lab 1 | Saddle River Marching Band (50 Points)
lakshmianu11's insight:

BIS 155 Lab 1 of 7: Saddle River Marching Band

For more course tutorials visit

www.shoptutorial.com

Submit your assignment to the Dropbox located on the silver tab at the top of this page.
L A B O V E R V I E W
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.

No comment yet.
 Scooped by lakshmianu11

## BIS 155 Course Project Excel Project - ShopTutorial.com

BIS 155 Course Project | Data imported from SalesData.csv text file located in Course Project
lakshmianu11's insight:

BIS 155 Course Project Excel Project

For more course tutorials visit

www.shoptutorial.com

The general instructions for this project can be found in the Week 6 Lecture material. The Week 6 Lecture describes the assignment and provides an overview of the project as a whole. There are no step-by-steps for the course project. The following are grading criteria for this project.
Guidelines
Organize the Data
Data imported from SalesData.csv text file located in Course Project Materials in DocSharing. Professional formatting follows the formatting guidelines we studied during the first week of class. Documentation sheet added to provide author, purpose, and date and provide information about each of the sheets you will create as you analyze the data. At least one calculated field added and copied to all cells in the worksheet.
Analyze the Data
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 labs for assistance in completing the different types of 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 most sense?
Present your Conclusions in a Report
Use the integration techniques we worked on in the course to develop a report (Word document) that includes data and graphs copied and pasted from your spreadsheet. You will turn in both your Recommendation Report and your spreadsheet. (There is an Excel Project Template in DocSharing, Course Project Materials, to help you formulate your report. The yellow-lined information provides an explanation of what should be included in each section of the report.)

Your report will focus 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. (45 points)

You should include a final paragraph that explains three things you learned about using Excel for data analysis. (15 points)

No comment yet.
 Scooped by lakshmianu11

## BIS 155 Lab 2 of 7: Excel's Advanced Formulas - ShopTutorial.com

BIS 155 Lab 2 | Excel’s Advanced Formulas & Functions
lakshmianu11's insight:

BIS 155 Lab 2 of 7: Excel’s Advanced Formulas

For more course tutorials visit

www.shoptutorial.com

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.

No comment yet.
 Scooped by lakshmianu11

## BIS 155 Entire Course - ShopTutorial.com

BIS 155 Entire Course | Create Address Labels
lakshmianu11's insight:

BIS 155 Entire Course

For more course tutorials visit

www.shoptutorial.com

BIS 155 Course Project: Excel Project

BIS 155 Lab 1 of 7: Saddle River Marching Band

BIS 155 Lab 2 of 7: Excel’s Advanced Formulas & Functions

BIS 155 Lab 3 of 7: Alice Barr Realty Analysis

BIS 155 Lab 4 of 7: Create Address Labels

BIS 155 Lab 5 of 7: Bruno’s Pizza Analysis

BIS 155 Lab 6 of 7: Day Care Center

BIS 155 Lab 7 of 7: Access Database

No comment yet.