Complete Course BIS155 Complete Course

Tuesday, 1 December 2015

Complete Course BIS155 Complete Course

Click Link for the Answer:

Lab 1 of 7 Saddle River Marching Band (50 Points)
Submit your assignment to the Dropbox located on the silver tab at the top of this page.

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. 


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

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. 


Lab 3 of 7 Alice Barr Realty Analysis 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. A. Download the file chap4_cap_barr from DocSharing, Lab 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 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 (A3H33). 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 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.


Create labels for the letters. The XYZ Corporation 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 Office
Download the Lab 4, Step 3 Instructions.
A. Create
a new file for printing labels.
B. Merge data from your Excel spreadsheet into the new label
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.
3 Create Address Labels (15 points)
Create labels for the letters. The XYZ Corporation 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 Office
Download the Lab 4, Step 3 Instructions.
A. Create
a new file for printing labels.
B. Merge data from your Excel spreadsheet into the new label
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.
You work with the XYZ Corporation Charitable Trust 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 the Corporation's 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
Full Name and Address First Name Donated Item Value Number of tickets requested Deliverables
You will turn in one spreadsheet and two Word documents. You
will locate the following files in DocSharing (Lab Materials folder)
wk4_trust.txt and wk4_trust_letter.docx. You will submit the following three
items one Excel spreadsheet (Lab4_yourlastname.xlsx), one letter
(Lab4_yourlastname.docx), and one sheet of labels
(Lab4_yourlastname_labels.docx). When submitting the workbook and Word
documents, provide a comment in the Dropbox comments area explaining what you
learned from completing this lab activity.
STEP 1 Import text file into a
spreadsheet and clean up data (20 points)
Download the Lab 4, Step 1 Instructions.
A. Import the text file into Excel and save
as lab4_yourlastname.xlsx. Import only the data you will need for your
B. 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.
C. Check the accuracy of all data by using the Spelling tool in
the Proofing group.
STEP 2 Create Merge Document
(15 points)
Download the Lab 4, Step 2 Instructions.
A. Open the wk4_trust_letter document 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.
B. Merge information from your Excel spreadsheet into the Word
document. Your completed Merge will look like the following when

XYZ Corporation Charitable Trust
123 Adams
Pittsburgh, PA 15697

Shelly Martin
123 North
Pittsburgh, PA 15697

Dear Shelly,

Thank you for
your generous donation to the XYZ Corporation Charitable Trust Fundraiser. We
are delighted to accep the following donation

Item Massage

Value $50.00

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

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

With Special thanks,

Charitable Trust President
XYZ Corporation

3 Create Address Labels (15 points)
Create labels for the letters. The XYZ Corporation 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 Office
Download the Lab 4, Step 3 Instructions.
A. Create
a new file for printing labels.
B. Merge data from your Excel spreadsheet into the new label
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


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.


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.


Hopefully you will find this week's lab activity fun and useful. If you have never created a database, you will be able to tell all of your friends and family that you are now a database designer. You will walk through the steps of creating and populating an Access database to keep a record of items in your home. The database will have a table, a form, a query, and a report. Turn in one database named Lab7_yourlastname.accdb to the DropBox. When submitting the database, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.


Week 1 Quiz

    (TCO 1) Which of the following describes the function of the Formula Bar (as it applies to Excel 2010)? (Points : 2)
    (TCO 1) Which of the following best describes a cell address? (Points : 2)
    (TCO 1) If you wanted to insert the current date into an active cell, which of the following would you use? (Points : 2)
    (TCO 1) In Excel a Formula is _______. (Points : 2)
    (TCO 1) Which of the following describes the Auto Fill in Excel 2010? (Points : 2)
    (TCO 1) Ribbon Commands with arrows indicate_____. (Points : 2)
    (TCO 1) The three types of data that can be entered in a cell in an Excel worksheet are __________. (Points : 2)
    (TCO1) In Excel, a border ________. (Points : 2)
    (TCO 3) A data series is a group of related ____________. (Points : 2)
     (TCO 3) A __________ is usually the most effective way to display proportional relationships, such as market share data, where the individual data values represent parts of a whole. (Points : 2)
    (TCO 3) When creating a chart in Excel, a single data series _____. (Points : 2)
    (TCO 3) To display similar data in a single column, with each series of data distinguished by a different color, use a: (Points : 2)
    (TCO 3) To provide your viewers with an explanation of the representations of the colors utilized in the data series in your chart, provide a: (Points : 2)
    (TCO 3) Which of the following best describes the X Y (scatter) chart? (Points : 2)
    (TCO 3) Which of the following best describes a trendline? (Points : 2)


Week 2 Quiz
1.(TCO 2) An absolute cell reference: (Points : 2)
2.(TCO 2) Which of the following best describes a circular reference? (Points: 2)
3.(TCO 2) =$E2-G$4 contains examples of _____ cell references. (Points: 2)
4.(TCO 2) In Excel, an Argument would be best described by which of the following statements? (Points: 2)
5.(TCO 2) The MEDIAN function would identify the ____. (Points: 2)
6.(TCO 2) The MAX function identifies the _____. (Points: 2)
7.(TCO 2) The NOW function would perform which of the following? (Points:2)
8.(TCO 2) The _____ number is the number of the column in the lookup table that contains the return values. (Points : 2)
9.(TCO 2) Arithmetic operations, cell references, and ____can be used in Excel formulas. (Points : 2)
10.(TCO 2) To simplify entering ranges in formulas, you can use _____. (Points : 2)
11.(TCO 7) A _____ helps to ensure consistency and standardization for similar workbooks, such as detailed income statements for all 12 months in a year. (Points : 2)
12.(TCO 2) When calculating a PMT function, the PV is best described by which of the following? (Points : 2)
13.(TCO 7) Before protecting a worksheet, you should __ the cells that you want users to be able to edit. (Points : 2)
14.(TCO 7) Which of the following file formats can store macros? (Points : 2)
15.(TCO 7) By default, Excel automatically _____ macros and displays a security warning when a file containing macros is opened. (Points : 2)


Week 3 Quiz
1.(TCO 4) If there are certain columns of information that are not needed at a particular time but might be needed later,_ the unneeded columns to allow users to focus on only the essential information. (Points:2)
2.(TCO 4) In Excel, the print area is defined as _____. (Points : 2)
3.(TCO 4) When sorting in Excel, it arranges records in a table _____. (Points : 2)
4.(TCO 4) Filtering the Cost column (field) to show only records greater than $10,000 is......__. (Points:2)
5.(TCO 4) To create a new Conditional Formatting Rule, _____. (Points : 2)
6.(TCO 4) In addition to sorting and filtering cells by content, you can sort and/or filter by _____. (Points : 2)
7.(TCO 4) When data is grouped, the margin area displays the _____. (Points : 2)
8.(TCO 4) A _____ allows you to identify relationships between variables in your data. (Points : 2)
9.(TCO 4) Data mining techniques such as PivotTables can detect _____ of data. (Points : 2)
10.(TCO 4) A faster way to expand all categories at one time is to click _____. (Points : 2) 
11.(TCO 4) Data displayed in the PivotTable will be updated _____. (Points : 2)
12.(TCO 4) A quick way to display the Go to dialog box is to press the _____ key(s). (Points : 2)
13.(TCO 4) After a PivotChart has been created, the PivotTable Settings can still be changed....___.(Points:2)
14.(TCO 4) To build a PivotTable and PivotChart at the same time, click the PivotTable arrow......__. (Points:2)
15.(TCO 4) Which of the following will NOT delete a PivotChart? (Points : 2)


Week 4 Quiz 
1.(TCO 8) Which of the following is one of the most common file types imported into Excel? (Points : 2)
2.(TCO 8) Which of the following is NOT a text file? (Points : 2)
3.(TCO 8) Which of the following is NOT a method for refreshing data? (Points : 2)
4.(TCO 8) The Convert Text to Columns Wizard allows you to choose the file type, such as Delimited____(Points:2)
5.(TCO 8) Which of the following is a frequently used function to manipulate txt? (Points : 2)
6.(TCO 8) To change the text string Jack Doe to JACK DOE, use the ________ function. (Points : 2)
7.(TCO 8) Which of the following cannot be used in a CONCATENATE function? (Points : 2)
8.(TCO 8) If you want to send a letter to all your customers using Mail Merge, and you already have the customers' names and addresses stored in an Excel file, you should choose _________ from the Select....... (Points : 2)
9.(TCO 8) The New Comment command is located on the ________ tab. (Points : 2)
10.(TCO 8) An alternative method for inserting a comment in a cell includes: (Points : 2)
11.(TCO 8) The command to track changes is located on the ________ tab. (Points : 2)
12.(TCO 8) The command to send an Excel workbook by e-mail is located on the ________ tab. (Points:2)
13.(TCO 8) Windows can search for files by author name or keywords that have been entered into_(Points:2)
14.(TCO 8) Which function is similar to the Find and Replace feature? (Points : 2)
15.(TCO 8) To encrypt a workbook with a password, click Info on the File tab, click _____...... (Points : 2)


Week 5 Quiz
1.(TCO 5) When multiple worksheets are selected, the word ________ appears in the title bar. (Points : 2)
2.(TCO 5) In Excel, which of the following would you perform to group adjacent worksheets? (Points : 2)
3.(TCO 5) When two windows of the same workbook are opened, Excel adds ________ to the.... (Points:2)
4.(TCO 5) After saving the arrangement of windows, a workbook can be opened by opening __file. (Points:2)
5.(TCO 5) Which of the following functions do NOT work with 3D formulas? (Points : 2)
6.(TCO 5) Excel's ability to manage worksheets allows you to do all EXCEPT: (Points : 2)
7.(TCO 5) If A1 contains the hourly pay rate of $10.25, A2 contains the hours worked (40), and A3 contains =A1*A2, which cell is the dependent cell(s)? (Points : 2)
8.(TCO 6) In a what-if analysis of a car purchase, the original interest rates of 6% and 5%, 5.5% and 6.5% are the ________ values. (Points : 2)
9.(TCO 6) Which What-If Analysis tool would be best at comparing the combined effects of.....? (Points : 2)
10.(TCO 6) After entering the substitution values and the reference to a formula, the next step to complete a two-variable data table and view the results is to: (Points : 2)
11.(TCO 6) The Goal Seek command is located in the ________ command on the Data tab. (Points : 2)
12.(TCO 6) Which of the following is NOT in the What-If Analysis command in the Data Tools.....? (Points:2)
13.(TCO 6) Which of the following is NOT one of the three parameters in Solver? (Points : 2)
14.(TCO 6) Which of the following is NOT required for Solver to generate a solution? (Points : 2)
15.(TCO 6) The initial results of a Goal Seek are: (Points : 2)


1.(TCO 1) You work for a local construction firm, "DeVry Engineering Group" and your supervisor wants to test your knowledge and skills with Microsoft Excel and has instructed you to develop a spreadsheet to calculate weekly payroll for “15” employees with the following assumptions:....... (Points : 40) 
2.(TCO 3) You currently work for an automotive parts supply store.  Your company is growing and is considering expansion.  The company currently has three locations (North, South, and Central) in one state.  Each parts supply store carries inventory in four categories.  You have been presented with the sales figures for the last three years for each location and inventory category by store.  Based on this information, you're tasked with analyzing current sales for each store by category and overall total sales by store and category….. (Points : 40) 
3.(TCO 4) You are given a spreadsheet with daily sales numbers ordered by date from January 1st to December 31st. You have been tasked with finding the average sales of each month, then to reorder the months so they are listed in order from highest to lowest average sales.  Give a step-by-step explanation of how you will rearrange the data so you can analyze the best and worst months….. (Points : 40) 
4.(TCO 5) You've just joined the staff of the XYZ Manufacturing Company (XYZ, for short). XYZ manufactures only one product, the gizmo. It comes in two sizes, the mini-gizmo and the magna-gizmo. Both are difficult to manufacture, and consequently, the company closely monitors rejected units. The company has three locations, each of which produces both the mini and the magna-gizmos. You are automating the weekly production reports so that you can easily calculate total production for the entire company each week. The mini-gizmo is priced at $3.25 per unit. The magna-gizmo is priced at $7.00 per unit. The unit cost for a reject mini-gizmo is $1.75. The cost for a reject magna-gizmo is $3.50. Respond fully to the following questions regarding this task:…… (Points : 40) 
5.5. (TCO 9) You have been tasked with analyzing an extremely large amount of data and to ultimately produce a report to share with the board of directors. The data is currently in a text file and has over two thousand records of data.  Explain how you would use Excel to analyze this data and organize it to prepare a written report. Be very specific on the variety of tools you would use and the steps you would go through to analyze the data and to ultimately prepare a detailed report with recommendations………(Points : 40)


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


BIS 155 Week 6 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

BIS 155 Week 1 Quiz  (Excel Fundamentals and Charting)

BIS 155 Week 2 Quiz (Excel s Advanced Formulas & Functions)

BIS 155 Week 3 Quiz (Data Analysis Functions)

BIS 155 Week 4 Quiz (Office Integration and Mail Merge)

BIS 155 Week 5 Quiz (Consolidating Data and What-If Analysis)

BIS 155 Week 8 Final Exam
BIS 155 Complete Course BIS155 Complete Course
Click Link for the Answer:

© Copyright 2015 Work Bank Theme by Workbank