1 / 137

BIS 155 Effective Communication - tutorialrank.com

For more course tutorials visit<br>www.tutorialrank.com<br><br>BIS 155 Course Project Excel Project<br>BIS 155 Lab 1 of 7: Saddle River Marching Band<br>BIS 155 iLab 1 Upper Saddle River Marching Band (New)<br>BIS 155 Lab 2 of 7: Excel's Advanced Formulas & Functions<br>

Bartholomew9
Download Presentation

BIS 155 Effective Communication - tutorialrank.com

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BIS 155 All iLabs + Course Project For more course tutorials visit www.tutorialrank.com BIS 155 Course Project Excel Project BIS 155 Lab 1 of 7: Saddle River Marching Band BIS 155 iLab 1 Upper Saddle River Marching Band (New) BIS 155 Lab 2 of 7: Excel's Advanced Formulas & Functions BIS 155 iLab 2: First National Bank (New) BIS 155 Lab 3 of 7: Alice Barr Realty Analysis BIS 155 iLab 3: Luke Liberty Realty (New) BIS 155 Lab 4 of 7: Create Address Labels BIS 155 iLab 4 Student Education Trust Create Address Labels (New) BIS 155 Lab 5 of 7: Bruno's Pizza Analysis BIS 155 iLab 5: Carina's Pizza Analysis (New) BIS 155 Lab 6 of 7: Day Care Center BIS 155 iLab 6 (Week 5) Day Care Center (New)

  2. BIS 155 iLab 7 (Week 6) Analysis of a business Scenario BIS 155 Lab 7 of 7: Access Database BIS 155 iLab 8 (Week 7) Descriptive statistics, formatting, graphs, and regression analysis ************************************************************************ BIS 155 Final Exam Guide (Cool Clocks, Inc.) For more course tutorials visit www.tutorialrank.com BIS155 Practical Final Exam Instructions 1 for Cool Clocks, Inc. (v2) Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook, or other resources (for example, you may search the Web for information). You may not get interactive help from any other person (either in person or via email, text messaging, IM, or other communications channels). I strongly recommend that you save your work frequently. 1. Formatting and charts (TCOs 1 & 3; 40 points) a. Enter your name and today's date on the Documentation sheet

  3. b. needed Select the Q1&Q2 sheet, and adjust the widths of the columns as c. A-D, increase the font size to 20, and change the font color to red. Center the title "Cool Clock, Inc. Sales by Product" across columns d. "Total Sales". Bold the column headings "Product", "Unit Price", "Quantity" and e. for each product. Add formulas to the Total Sales column to calculate the total sales f. D8. Add a grand total at the bottom of the Total Sales column, in cell g. accounting or currency format with a dollar sign and two decimal places. Format the numbers in the Unit Price and Total Sales columns as h. percentage of total sales for each product & the product name. (Do not include the grand total in the pie chart!). Create a 3-D pie chart on a separate sheet that displays the i. Product". Make the title of the chart & the worksheet tab "Total Sales by j. Place each product percentage of sales inside its pie slice. k. Place a legend for all products on the right side of the chart. l. immediately after the Q1&Q2 sheet. Move the sheet named “Total Sales by Product” with the pie chart

  4. m. Save your work (CTRL+s) - (but leave it open to continue). 2. Formulas and applications (TCOs 2 & 7; 40 points). On the Q1&Q2 sheet, in cell E8, add a formula using a lookup function that will look up the grand total in cell D8 in the table of sales and assessments in A11:B13, and display the corresponding assessment. Use a range lookup. (For example, if the grand total of $12,500 is displayed in cell D8, "Good" should be displayed in cell E8.) In cell E9 enter the label Highest Sales. In F9 enter a formula using a function to display the highest total sales value for any product. In cell E10 enter the label Lowest Sales. In F10 enter a formula using a function to display the lowest total sales value for any product. In cell E11 enter the label Average Sales. In F11 enter a formula using a function to display the average total sales value for all products. Format the labels and statistics (Highest Sales, Lowest Sales, and Average Sales) to display an outline (exterior border) around these cells for readability. Resize all columns for visibility. Add comments to cells F9, F10, and F11. In your comments, explain briefly in your own words what is displayed in each cell. Add gridlines and cell formatting and color formatting to cells A10:B13. Add a top and double bottom border to cell D8. Protect the worksheet so the contents cannot be accidentally changed by a user. Do not use a password.

  5. Save your work (CTRL+s) - (but leave it open to continue). 3. tables (TCOs 4 & 8; 40 points). Data cleansing, lists, sorting, conditional formatting, and pivot On the Q3 sheet, adjust the widths of the columns as needed. Column C contains each customer’s combined first and last name. Insert two empty columns to the right of column C, and use an Excel feature to separate the customers’ first names and last names into the new columns. In the first row, label the new columns Customer First Name and Customer Last Name. After doing this, delete the original Customer Name column. Resize the columns. Convert the list of orders (A1 – J21) into an Excel table. Sort the table into descending order (highest to lowest) by Order Amount. Apply conditional formatting to the Order Amount column so that order amounts greater than $100 are highlighted in green. Convert the table back into a range. Create a pivot table on a new worksheet that uses Customer Country as the row field, Product as the column field, and the sum of Order Amount as the values. Place this sheet immediately after the Q3 sheet, labeled Q3-Pivot. Format the cells in the Pivot table using all borders with accounting or currency format with 2 decimal places. Add gridlines to the Pivot table.

  6. Save your work (CTRL+s) - (but leave it open to continue). 4. Data consolidation, analysis, and reporting (TCOs 5 & 9; 40 points) a. Summary. Group the four sheets Q4 East, Q4 Central, Q4 West, and Q4 b. A, and format the values in cells B2:E5 as currency with no decimal places. Resize all columns. While the sheets are grouped, bold the labels in row 1 and column c. Ungroup the sheets (very important!). d. that displays the totals for each product and quarter over all 3 regions (East, Central, and West). On the Q4 Summary sheet, use 3D formulas to create a summary e. product in each quarter. Each cluster should represent a quarter, and each individual column should represent sales of a product within that quarter. Place your chart on the Q4 Summary sheet to the right of the data. Give the chart an appropriate title. Create a clustered column chart that shows total sales of each f. changing over time. Add a trend line to the chart to show how sales of alarm clocks are g. paragraph) analysis report to the manager of the Cool Clocks business. In your report, explain your findings on the trend in sales of alarm clocks, and any other patterns you observed in sales of the product In the space provided on the Q4 Summary sheet, write a brief (2-3

  7. categories; and recommend at least one specific action that Cool Clocks should take regarding the alarm clocks product line. Save your work (CTRL+s) - (but leave it open to continue). 5. What-if analysis (TCO 6; 40 points) a. worksheet stating “Detailed Sales What-if Analysis”. Make sure it is centered across columns A – G and has a heading format. On the Q5 sheet, please format the Title row at the top of the b. total cost, and net income for a new product line, based on the projected unit sales, unit price and unit cost provided. Enter formulas in cells B6, B7, and B8 to calculate the total sales, c. sales as currency with no decimal places. Adjust column widths as needed and format all values except unit d. Varying Unit Sales on Total Sales, Total Cost, and Net Income” to calculate the total sales, total cost and net income for projected sales of 80, 90, 100, 110, and 120 units. Complete the one-variable data table under the heading “Effect of e. on Net Income of Varying Unit Sales and Unit Price” to calculate the net income for projected sales of 80, 90, 100, 110, and 120 units at unit prices of $4, $5, $6, $7, and $8. Complete the two-variable data table under the heading “Effect f. Format cells A3 – B8 with a thick box border.

  8. . sure the Initial values, Total Sales, Total Cost and Net Income have an accent color. Format the one-variable data table with themed cell style. Make h. sure the initial values for the horizontal and vertical have an accent color. Format the two-variable data table with a themed cell style. Make i. the top 10% with green fill. Format the bottom 10% with red fill. Add conditional formatting to the one-variable data table. Format Save your work (CTRL+s) and close. ************************************************************************ BIS 155 Final Exam Guide (Music on Demand) For more course tutorials visit www.tutorialrank.com Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook, or other resources such as the Web You may not get interactive help from any other person, either in person or via email, text messaging, instant messaging, or other communications channels. I strongly recommend that you save your work frequently. You have 4 hours to complete this exam.

  9. Note: Be sure to spell check on every worksheet and correct all spelling errors. Getting started Open Excel (either on your desktop or in Citrix) If you are using Citrix, you need to upload the spreadsheet to your virtual drive before you can open it in Excel Open the Practical Exam spreadsheet you downloaded from the course Exam page.Save the spreadsheet as LastnameFirstInitial Week8 FinalExam.xlsx. (i.e., if your name were Jane Doe, your file name would be: DoeJ Week8 FinalExam.xlsx) Questions/Problems Formatting and charts (TCOs 1, 2, and 3; 30 points) Using the Documentation sheet: a. Enter your name and today's date. Using the ‘Quest 1 & 2’ worksheet: Adjust the widths of the columns so all data is visible. Center the title "Music on Demand - Sales by Product" across columns A-D. Increase the font size to 16, and change the font color to red.

  10. Bold the column headings "Product", "Unit Price", "Quantity", and "Total Sales". Add formulas to the Total Sales column to calculate the total sales for each product. Add a grand total at the bottom of the Total Sales and Quantity columns. Format grand totals so that they are easily read. Format the numbers in the Unit Price and Total Sales columns as accounting or currency format with a dollar sign and two decimal places. Format appropriately. Correct spelling by using the Spell Check feature. Create a 3-D pie chart on a separate sheet (Chart1) that displays the total sales for each product. (Do not include the grand total in the pie chart!) Move the chart to a new worksheet titled “Chart 1”. Make the title of the chart "Total Sales by Product". Place each product name and its percentage of sales at or inside its pie slice, with product names off to the right as well. Move the Chart1 pie chart worksheet immediately after the Quest 1&2 sheet. Save your work (CTRL+s).

  11. Formulas, applications and statistical functions (TCOs 2, 3, 6, and 10; 30 points) Using the ‘Quest 1 & 2’ worksheet. Draw a box around the table at A22-B25, with a light shading color for the column labels. In cell E18, add a formula using a lookup function that will look up the grand total in cell D18 in the table of sales and assessments in A22:B25, and display the corresponding assessment. (For example, if the grand total of $505 is displayed in cell D18, "Good" should be displayed in cell E18.) In cell E19 add the label Highest Sales. In F19 add the function formula for Maximum based on the Total Sales column. In cell E20 add the label Lowest Sales. In F20 add the function formula for Minimum based on the Total Sales column. In cell E21 add the label Average Sales. In F21 add the function formula for Average based on the Total Sales column. Place a border around the statistics data and widen columns as needed for readability. Save your work (CTRL+s). Lists, sorting, charts, and conditional formatting (Excel; TCOs 4 and 6; 40 points) Using the ‘Quest 1 & 2’ Worksheet:

  12. Convert the list of products, unit prices, quantities, and total sales in A3:D17 into a table on the Quest 3 – Lists & Sorting sheet starting in cell A1. Using the ‘Quest 3 – Lists & Sorting’ worksheet: Sort the table into descending order (highest to lowest) by Total Sales. Apply conditional formatting to the Total Sales column so that sales greater than $1,000 are highlighted in green. Create a pivot table using the table data from Quest 1 & 2 on a new sheet and name it MoD-3b. Then move it after ‘Quest 3 Lists & Sorting’. Create a pivot showing the list of products in alphabetical order, showing total sales by product with appropriate currency formatting. Create, on this same sheet, a line graph using the sales data from the pivot table. Add chart title and labels as appropriate. Save your work (CTRL+s). Functions and Financials (TCO’s 2, 6, and 7; 30 points) Using the Quest 4-Financials worksheet: Complete the Profit and Loss Statement provided by adding formulas (referenced shaded cell locations) and using the product and Total Sales data already included in Quest 1 & 2. Note: Do not just re-key the data, but use references from the original data. Format the Total Revenue, Expenses, and Net Income in bold, and choose a fill color for the Net Income.

  13. Perform a one-way analysis (i.e., Week5, iLab 6) to see at what sales volume level MP3 player sales add at least $1,000 to net income. Apply conditional formatting to the options that produce at least $1,000 in net income in the above table. Format all the numeric data and labels on the worksheet using consistent and business like formatting options. Save your work (CTRL+s). Continue using the ‘Quest 4 – Financials’ worksheet: Music on Demand (MoD) wants to apply for a 20-year loan and they need to know how much the monthly payment will be with a $10,000 down payment or a $20,000 down payment on a loan of $160,000. The annual interest rate is 3% and payment is assumed to be made at the end of the period. Complete the chart and calculate the monthly payment, using an Excel function. Secure/Protect, without a password, the Quest 4-Financials worksheet tab. Save your work (CTRL+s). Organization, Planning and Consolidation Strategies (TCO’s 5, 7, & 8; 40 Points) Using the ‘Quest 5 – Organizing Data’ worksheet:

  14. Consolidate the information on the Quest 5-Organizing Data sheet from the three (3) worksheets (DC Branch, Houston Branch, and LA Branch), using consolidation strategies that include: Group sheets and use the auto-fill to complete the list of months in Column A and add totals for rows and columns. Apply formatting to column headings and merge and center a title across row 1 for each sheet. Open the blank worksheet, Quest 5-Organizing Data, and on the data tab click consolidate. Choose the sum function to sum the data. Add each worksheet to the consolidated Summary. As an alternative to steps 3-5 above, use 3-D referencing to summarize the data. Add row and column summary totals on the Quest 5-Organizing Data Worksheet. Summarize your results using an appropriate chart type and place it below the summarized data. Spell check the worksheet. Save your work (CTRL+s). Analysis, Summary and Recommendation (TCO’s 8, and 9; 30 Points).

  15. Analyze the results from Quest 5-Organizing Data worksheet and create a report/memo with the following: Identify the top two selling items and the two lowest selling items. Add a Chart/Table (s) to reflect your results – or use the chart created in step 5 Offer management a recommendation to improve the business based upon your analysis. List at least two Lessons Learned from your course experience. Save and close your Excel file. ************************************************************************ BIS 155 iLab 1 Upper Saddle River Marching Band (New) For more course tutorials visit www.tutorialrank.com 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

  16. 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 STEP 1: Opening and Saving Download the spreadsheet, 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. 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

  17. Creator Your Name Date Date Purpose date Board of Directors Presentation on Fundraising Sales to 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.

  18. STEP 4: Add Formulas A. item: Create the formulas needed to calculate the following for each 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. columns. Create Grand Totals for the Total Sales and the Total Profit C. Create the formula that will average all of the item profit %. D. the Marching Band goal for the fundraising campaign. Create formula to calculate the Remaining Profit needed to make 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.

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

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

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

  22. BIS 155 iLab 2: First National Bank (New) For more course tutorials visit www.tutorialrank.com 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 the Lesson page. Deliverables Step 1: Open, Save, and Document the Workbook A. 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] Download the Week2_iLab_FirstNatlBank_Students.xlsx

  23. B. 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.). It is recommended that as you progress through this assignment, C. 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. Add a documentation sheet that provides a spreadsheet title, 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. column D. There is a tutorial video on creating and using VLookup tables at the end of the Lesson page. Use a VLOOKUP function to determine the interest rates in B. VLOOKUP function by the selling price. Enter the formula in column E. Calculate the down payment by multiplying the results of a C. payment from the selling price. Enter the formula in column F. Calculate the amount to be financed by subtracting the down

  24. D. 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. Use a PMT function to determine the monthly payments in E. statistics for the month. Use appropriate formulas and functions to calculate our loan • 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. percentages as percents. Format all money figures as currency with two decimals and all B. Adjust columns sizes to fit the data.

  25. C. Merge and center titles and size appropriately. D. Change font color to dark blue. E. worksheet. Add a small graphic appropriate for the purpose of the F. appropriate. Separate sections of the worksheet and provide borders as Step 4: Create an Excel Application With Macro and Macro Button A. calculator. Add a new worksheet to the workbook, and name it Loan 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. calculates the mortgage payments for a 15-year loan, a 20-year loan, and a 30-year loan. Create an Output section that uses the user-provided data and D. $50,000 for the cost of the home and answer Yes/No to the payment question. Add user input validation to ensure that users input at least

  26. E. protect the sheet to keep users from changing other areas. Unlock the cells in which users will input data so that you may F. Create a Macro. G. clear values and start over. Name the button Enter New Values. Use Insert Shapes to add a Macro button that will allow users to H. Protect the worksheet, but do NOT require a password. I. The following is a sample of what your final worksheet might look like: Format the worksheet so that it is easy to understand and use. 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. ************************************************************************ BIS 155 iLab 3: Luke Liberty Realty (New) For more course tutorials visit www.tutorialrank.com 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

  27. 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 You must open a workbook that lists home sales for 3 months. A. Download the workbook Week3_iLab_LibertyRealty_Student.xlsx B. naming convention above. Open the workbook in Excel, and save it according to the file C. 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." Insert a documentation sheet that includes title, author, date, 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. divided by asking price). Enter the formula in Column F. Format Column F as a percentage with one decimal. Calculate the percent of asking price for each sale (selling price B. Columns G and H as dates so just the day and month (e.g., 5-May) are Format Columns D and E as currency, with no decimals. Format

  28. visible. You will need to use the Number Format dropdown list, select More Number Formats, and select the Date category. C. titles as appropriate. Bold and center the column headings. Widen columns to make all data and headings visible. Format Your Sales Data worksheet will look something like this. STEP 3: Sort Data and Create Summary Report and Chart A. 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. Before analyzing data, it is a good idea to copy the data to a new B. data table. Format the data table to look attractive. In order to sort the data, you may want to convert them to a C. (largest to smallest). Sort the data by selling agent (A to Z) and then by asking price D. report showing the total asking price and total selling price by selling agent. Convert the sorted data back into a range, and create a summary E. between asking price and selling price. Create a column chart that shows the difference by agent STEP 4: Filter the Data

  29. A. Filtered Data. Create another copy of the original sales data. Rename the sheet B. properties sold in Miami by agent Carey. Convert the range to a table, and then filter to show only those 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. formatting rules for the field Percent of Asking Price. Use Advanced Formatting to set up the following conditional • Greater than or equal to 100% = GREEN • 95% to 99.9% = YELLOW • Less than 95% = RED C. with green first, then yellow, and then red. Use Percent of Asking Price as the Sort Key, and sort by color STEP 6: Create a Pivot Table

  30. 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. (A3:H33). From the Formulas tab, select Define Name from the Defined Names group. Name the range of cells SalesData. Open the Sales Data worksheet. Select the Sales Data range B. Create the pivot table in a new worksheet. Create a pivot table based on the named range, SalesData. C. 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. Select Selling Agent as your Row Labels and City as your Column D. tab in the worksheet. Rename the sheet PivotTable, and move it so that it is the last 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.

  31. Submit your completed workbook to the Dropbox. Make sure you post a comment about what you learned when submitting the file. ************************************************************************ BIS 155 iLab 4 Student Education Trust Create Address Labels (New) For more course tutorials visit www.tutorialrank.com 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

  32. • Number of tickets requested STEP 1: Import Text File Into a Spreadsheet and Clean up Data A. complete the download, right click on the file name and select Save Link As. The file will save to your local drive. Download the text file: Week4_iLab_MailingData_Student.txt. To B. you will need for your letter (i.e., excluding unnecessary columns). Open a new Excel file and import only the data from the txt file C. may use the first name in a Mail Merge. Make sure the fields are named LastName and FirstName. Separate the first and last names into two columns so that you D. Proofing group. Check the accuracy of all data by using the Spelling tool in the E. 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. Add a documentation sheet that provides a spreadsheet title, STEP 2: Create Merge Document A 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. Open the Week4_iLab_Trust_Template_Student.docx document

  33. • listed above. Note: Be sure to name this file, using the naming conventions B. document. Your completed merge will look like the following when complete. Merge information from your Excel spreadsheet into the Word 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.

  34. 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. document. You can assume that a standard address block is required for the labels. Merge data from your Excel spreadsheet into the new label

  35. 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. ************************************************************************ BIS 155 iLab 5: Carina's Pizza Analysis (New) For more course tutorials visit www.tutorialrank.com 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 qu​ arter 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. Lab Steps

  36. STEP 1: Open all Worksheets and consolidate into a summary worksheet. You will be using Week5_Lab5_Carinas_Summary_Student.xlsx the partially completed Summary workbook and the completed Northside Workbook, Southside Workbook, and Downtown Workbook. Consolidate the data from the three completed worksheets into the Summary workbook (Week5_Lab5_Carinas_Summary_Student.xlsx). A. 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. The individual worksheets for the three locations have been B. consolidation of the three locations into the provided summary worksheet page. Using the Consolidate feature and perform a summary C. worksheet. File naming convention: If your name is Jane Doe, then your files should be named very similar to: Doe_J_Week5_iLab.xlsx. Rename the file that includes your consolidated summary D. Leave your Summary workbook open. Close the Northside, Southside and Downtown Workbooks. 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.

  37. A. on the Summary Sheet. Create formulas to calculate sales by dining category and quarter B. and by Quarter. Group the worksheets and add Grand Totals by Dining Category STEP 3: Create Graph Carina is more of a visual person, and you want to present your data in an alternate way. You will create a chart to summarize your data. A. 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. Create a chart on the separate sheet already in your workbook. B. Include a title, legend, and data labels on your chart. C. the same colors, fonts, titles, and clip art. Format the chart to coordinate with your summary table, using 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). 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.

  38. A. Documentation. Insert a new worksheet and rename the sheet tab B. 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. Include the following on your documentation worksheet: Your C. font used in other worksheets and charts. Format the documentation worksheet using the same colors and D. Layout tab, Sheet Options group. Hide the gridlines on the Documentation Sheet using the Page E. name on the left, the page number in the middle, and the instructor's name on the right. Group the worksheets and create a custom footer with your F. store to her chain? Why or why not? Provide a comment on the Documentation Sheet. Based on your analysis, do you think Carina should add another Submit your completed workbook to the Dropbox. Make sure you post a comment about what you learned when submitting the file. IMPORTANT: There are two iLabs this week, and there is a separate Dropbox basket for each one. Be sure to submit your work for this iLab to the Dropbox basket labeled Week 5: iLab 5. ************************************************************************

  39. BIS 155 iLab 6 (Week 5) Day Care Center (New) For more course tutorials visit www.tutorialrank.com Senario/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. 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.

  40. A. has provided the basic assumptions concerning her costs and her revenue. Open Week5_Lab6_Adair_Daycare_Student.xlsx. Notice that Adair B. Complete the following calculations. Revenue • number of children. Total Revenue: Multiply tuition per day times number of days by Variable expenses are those expenses that will depend on the number of children served each year. Jane has provided the assumptions. Define annual costs based on the cost per day multiplied by the number of children multiplied by the number of days. • Food Expenses • Supply Expenses Teacher Cost: Create a VLOOKUP function to determine the annual teacher cost. The Lookup Value is the number of children. The Lookup Table is in cells E3:F7. The Index Column is the second column in the table. You want the closest match. Multiply the LOOKUP function times the annual teacher salary. Total Variable expenses: This will be the SUM of all of the Variable Expenses (B17:B19).

  41. 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 C. 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.Image Description Rename the file according to the file naming conventions as 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. A. through 15. Add a descriptive title to the Data Table. Set up the Data Table to display number of children from 6

  42. B. children. Show the Expenses and Net Income for each change in number of C. Table tool. Populate the Data Table using the Data tab, What-If analysis, Data D. 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. A. row input (6–15) and charge per student (35–75 in $5 increments) as the column input. Set up the Data Table to display number of children as the 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. B. no data. Enter Net Income in the result cell, and format this cell to display

  43. C. Add a descriptive heading. D. Income values over $75,000. Create the data table and apply conditional formatting to Net 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. A. Name the cells that will be used in the Scenario. B. Create the three Scenarios. C. Create a Scenario Summary. D. Move the Scenario Summary to the end of the workbook.

  44. STEP 5: Create a Documentation Sheet and Provide a Recommendation A. Include Author, date Created, Last Modified, and Contents sections. Add a documentation sheet to the beginning of the workbook. B. Provide a recommendation to Adair. C. other sheets in the workbook. Format the documentation sheet to match the look and feel of the D. 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 labeled Week 5: iLab 6. ************************************************************************ BIS 155 iLab 7 (Week 6) Analysis of a business Scenario For more course tutorials visit www.tutorialrank.com Scenario/Summary

  45. 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. STEP 1: Import Your Data Into Excel The ultimate goal is to prepare a memo to management, using the 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 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

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

  47. 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. • Is there any discount level that appears to be more effective than others? Sort by discount level. Have discounts increased sales volumes? • lows in sales? Is there any time of year in which sales are highest? Lowest? Graph sales over time to see trends. Are there any peaks and • and salesperson. Are there any highs? Are there any lows that need to be addressed? Pivot the data to see total sales by quarter, country, category, • salesperson? By country? Subtotal the data. How are quarterly sales totals? Sales totals by • raised by a certain percentage with a slight decline in sales? What combination of price increases and decline in sales makes the most sense? Challenge Option: Perform What-If analysis. What if prices were STEP 4: Prepare the Management Analysis Memo

  48. 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 labeled Week 6: iLab 7 ************************************************************************

  49. BIS 155 iLab 8 (Week 7) Descriptive statistics, formatting, graphs, and regression analysis For more course tutorials visit www.tutorialrank.com 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

  50. 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. STEP 1: Getting Started—Worksheet Template Please download this week's iLab file: 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 The Data_1971_2000 worksheet 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 the averagetemperature 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. you scroll through the data. Freeze the top row, so that the column headers are visible as

More Related