Microsoft 360 ( use attached data file to complete each task)
Assignment 2 Excel Chapter 2: Formulas and Functions Capstone Exercise Page 503 13 marks Excel Chapter 3: Charts Capstone Exercise Page 554 15 marks Word Chapter 3: Document Productivity Capstone Exercise Pages 311–312 10 marks Notes: At the end of Step c under Merge Therapists Information into a Welcome Letter, save and then close w03c1Welcome. In Step e under Produce a Reminder Letter for Patients, the second sentence should be as follows: "Change the formatting of the entire document so that the complete letter is single spaced with no additional spacing before or after any paragraph." In Step f under Produce a Reminder Letter for Patients, save the original mail merge document containing the main document as w03c1Reminder_LastFirst. The following documents should be submitted for marking: w03c1Therapists_SmithJane w03c1Welcome_SmithJane w03c1MergedWelcome_SmithJane w03c1Reminder_SmithJane w03c1Appointments_SmithJane PLEASE NOTE I DID A,B,C. SECTION FOR THE Assignment ************************************* Excel Chapter 2: Formulas and Functions Capstone Exercise Page 503 Perform Preliminary Work You need to open the starting workbook you created, acknowledge the existing circular reference error, and assign a range name to the membership lookup table. You will correct the circular reference error later. a. Open the e02c1Gym workbook, click Help, read about circular references, close the Help window that displays, and then save the workbook as e02c1Gym_LastFirst. b. Assign the name Membership to the range A18:C20. c. Insert a function to display the current date in cell B2. Calculate Cost, Annual Total, and Total Due You are ready to calculate the basic annual membership cost and the total annual cost. The basic annual membership is determined based on each client’s membership type, using the lookup table. a. Insert a lookup function in cell C5 to display the basic annual membership cost for the first client. b. Use an IF function in cell E5 to calculate the annual total amount, which is the sum of the basic cost and locker fees for those who rent a locker. For people who do not rent a locker, the annual cost is only the cost shown in column C. The Locker column displays Yes for clients who rent a locker and No for those who don’t. c. Calculate the total amount due in cell G5 for the first client based on the annual total and the number of years in the contract. d. Copy the three formulas down their respective columns. Determine the Down Payment and Balance You need to collect a down payment based on the type of membership for each new client. Then you must determine how much each client owes. a. Insert a lookup function in cell H5 to display the amount of down payment for the first client based on the membership type. b. Find and correct the circular reference for the balance. The balance is the difference between the total due and the down payment. c. Copy the two formulas for the rest of the clients. Calculate the Monthly Payment Clients pay the remainder by making monthly payments. Monthly payments are based on the number of years specified in the client’s contract and a standard interest rate. a. Insert the function in cell J5 to calculate the first client’s monthly payment, using appropriate relative and absolute cell references. b. Copy the formula down the column. c. Edit the formula by changing the appropriate cell reference to a mixed cell reference. Copy the formula down. Finalize the Workbook You need to perform some basic statistical calculations and finalize the workbook with formatting and page setup options. a. Calculate totals on row 14. b. Insert the appropriate functions in the Summary Statistics section of the worksheet: cells H18:H22. Format the payments with Accounting Number Format and format the number of new members appropriately. c. Format the other column headings on rows 4 and 17 to match the fill color in the range E17:H17. Wrap text for the column headings. d. Format the monetary values for Andrews and the total row with Accounting Number Format. Use zero decimal places for whole amounts and display two decimal places for the monthly payment. Apply Comma Style to the internal monetary values. Underline the values before the totals and apply Double Accounting Underline (found in the Format Cells dialog box) for the totals. e. Set 0.3″ left and right margins and ensure the page prints on only one page. f. Insert a footer with your name on the left side, the date code in the center, and the file name code on the right side. g. Save and close the workbook, and submit based on your instructor’s directions. ************************************** Excel Chapter 3: Charts Capstone Exercise Page 554 Set Chart Filters, Position, and Size You created a clustered column chart, but you selected too many cells for the data source. You need to open the workbook and set chart filters to exclude extraneous data sources. In addition, you want to position and size the chart below the data. a. Open the e03c1Movies workbook and save it as e03c1Movies_LastFirst. b. Set chart filters to remove the Category Totals and the Weekly Totals. c. Position and size the chart to fill the range A18:K37. d. Change the row and column orientation so that the weeks appear in the category axis and the genres appear in the legend. Add Chart Labels You need to enter text for the chart title and add a value axis title. In addition, you want to position the legend on the right side because it is easier to read a vertical, alphabetical list rather than a horizontal list of genres. a. Enter the text November 2016 Downloads by Genre as the chart title, bold the title, and then apply Black, Text 1 font color. b. Add a value axis title: Number of Downloads. Apply Black, Text 1 font color. c. Move the legend to the right side of the chart. Format Chart Elements You are ready to apply the finishing touches to the clustered column chart. You will format the category axis by adjusting the font size and applying a darker font color. You will add and adjust data labels to the Drama data series to emphasize this series. a. Format the category axis with 11-pt size and Black, Text 1 font color. b. Select the Drama data series and add data labels in the Outside End position. c. Add a Gradient fill to the data labels. Insert and Format Sparklines You want to show weekly trends for each genre by inserting spark-lines in the column to the right of Category Totals. a. Click cell G5 and insert Line Sparklines for the weekly data for each category and the weekly totals, but do not include the category totals for the data range. The location range should be G5:G15. b. Apply the Sparkline Style Accent 3 (no dark or light) sparkline style. c. Show the high point and markers. d. Change the high point marker color to Red. Create a Stacked Bar Chart You want to create a bar chart to show how the weekly totals contribute to the month totals by genre. a. Select the range A4:E14. Create a clustered bar chart. b. Move the chart to its own sheet named Bar Chart. c. Change the chart type to a stacked bar chart. d. Add a chart title above the chart and enter November 2016 Weekly Downloads. Format the Bar Chart You want to enhance the appearance of the chart by applying a chart style and adjusting the axis values. a. Apply bold and Blue, Accent 5 font color to the chart title. b. Apply 11-pt font size to the category axis, value axis, and the legend. c. Use the AXIS OPTIONS to display the value axis in units of Thousands, set the Major Units to 500, and apply the Number format with 1 decimal place. d. Use the AXIS OPTIONS to format the category axis so that the category labels are in reverse order. Finalizing the Charts You want to prepare the workbook in case someone wants to print the data and charts. To ensure the worksheet data and chart print on the same page, you need to adjust the page setup options. a. Create a footer on each worksheet with your name, the sheet name code, and the file name code. b. Apply landscape orientation for the Data worksheet. c. Set 0.2″ left, right, top, and bottom margins for the original worksheet. d. Change the scaling so that the worksheet fits on only one page. e. Save and close the workbook, and submit based on your instructor’s directions. ************************************** Word Chapter 3: Document Productivity Capstone Exercise Pages 311–312 Create a Table Containing Therapist Information You will create a new document that includes a table of information about the new physical therapists. Then you will personalize the welcome letter created for the therapists and use information from the table to create a personal letter for each person. a. Open a new blank document and save it as w03c1Therapists_LastFirst. b. Create a table with the following information: Name Credentials Street Address Days Working Salary Mike Salat M.S., ATC 2342 W. Cardinal Street Monday-Thursday $65,000 Justin Ebert M.S., ATC 34234 S. Callie Place Monday-Friday $68,000 Karen Rakowski ATC, PT 98234 E. Shepherd Lane Monday-Friday $65,000 c. Separate each name, including the column heading, into two columns, because it will be easier to use in form letters using mail merge features. (Hint: Uncheck the option Merge cells before split in the Split Cells dialog box.) Make necessary changes to the table to display the therapists’ first and last names in two separate columns. The first column should include first names, while the second column contains last names. The first cell in the first row should include First Name, and the second cell in the first row should include Last Name. d. Insert three new columns after the Street column for the City, State, and Zip information. Add a column heading to each new column, with City, State, and Zip, from left to right. Center entries in row 1 both horizontally and vertically. Populate each cell in the City column with Conway. Each cell in the State column should contain AR, and each cell in the Zip column should contain 72032. Center all entries in rows 2, 3, and 4 both horizontally and vertically. e. Create a new row at the end of the table. Type the word Average in the eighth cell on the last row and right align it. Use a formula to average the Salary column in the next cell on the last row. (Hint: In the Formula dialog box, remove the suggested function, but leave the = and paste an Average function, indicating that the average should include the numbers Above.) Select a currency number format, displaying a dollar sign and two places to the right of the decimal. f. Select a table style of Grid Table 6 Colorful - Accent 6 (sixth row, seventh column, under Grid Tables). Select a pen color of Green, Accent 6 and a double underline line style (seventh selection under Line Style). Brush the border dividing the first row from the second. Press Esc. Remove bold formatting from the first names in column 1. g. Click the Border Styles arrow and select Border Sampler. Click to sample the new border dividing the first and second rows and brush it along the bottom border of the table. Press Esc. h. Sort the data in the table (but do not include the header or total rows) by Last Name in ascending order. Save and close the document. Merge Therapist Information into a Welcome Letter You have documented information about the new physical therapists; you can use it as a source for the welcome letter. a. Open w03c1Welcome. Start a mail merge using the welcome letter as the source document. The recipient information will come from w03c1Therapists_LastFirst. Be sure to deselect the last record in the data source so the Average row is not included in the merge. b. Replace the bracketed Current Date with today’s date. Replace the starred placeholders in the letter with fields from the recipient table. Insert an Address Block using the default settings. Include a first name in the salutation and replace days of week in the body of the letter with data from the Days_Working field. Replace Firstname Lastname in the closing with your first and last names. c. Complete the merge of all records, producing a document containing three letters, each addressed to a recipient in the data source. Save the merged letters as w03c1MergedWelcome_LastFirst and close the document. Close w03c1Welcome without saving it. Produce a Reminder Letter for Patients Your second project for the day is the generation of a letter to remind patients of their appointment with therapists. For this project, you will use an Access database as the source because that is how your office stores patient information. a. Begin a new document. You do not need to save the document at this time. Start a mail merge letter, using the current document, and pull your recipients from w03c1Patients.accdb. When you select the database file, use the Patients table. b. Press Enter twice and insert today’s date, aligned at the left margin. Press Enter three times and insert an Address Block, using default settings. Press Enter twice and type RE: Upcoming Appointment. c. Press Enter twice and type Please remember that you have an appointment at the Sports Medicine Clinic of Conway on *date*, at *time*. If you have paperwork to fill out, please arrive at our office 15 minutes prior to your appointment time stated above. Thank you! d. Press Enter twice and finish the letter by typing: Sincerely, The Sports Medicine Clinic of Conway (501) 555-5555 e. Insert the fields for date and time in the first sentence and remove the markers. Change the formatting of the document so the letter is single spaced with no additional spacing before or after any paragraph. Insert three blank paragraphs between Sincerely and The Sports Medicine Clinic of Conway. f. Merge all records from the data source into a new document. Save the document as w03c1Appointments_ LastFirst and close it. Close the original mail merge document without saving it. Exit Word. g. Submit w03c1Welcome_LastFirst, w03c1MergedWelcome_ LastFirst, and w03c1Appointments_LastFirst based on your instructor’s directions.