Capstone Integration Project Word, Excel, Access, and PowerPoint ...

Capstone Integration Project Word, Excel, Access, and PowerPoint For this project, imagine that you are working for an electronics store. You will create various files that summarize the business activities. Submit files per the instructions once entire project has been completed. Do not submit files as each task is completed. You determine the company name, which should appear at the top of each document (including worksheets within a workbook) that you create and submit. Use the same font and formatting for the company name and headings on all documents. In addition, student name(s) should appear as a subtitle along with an appropriate title and date(s) for the data being represented. These headings are to be merged and centered above the worksheet data on the spreadsheets, included in a letterheading for the mailable letters, and included in the title area of the PowerPoint presentation. Project Overview Your supervisor asks you to create a database to track employees, an electronic workbook representing sales data, an electronic presentation, and a mail merge letter project to convey important information relating to the quarter review process. The administrative meeting will look at the company’s success over the past three months—be sure to include a correct year. The following table outlines the applications you will use to complete this project. Task 1 MS Application Used Details Access You will create a database with one table to maintain employee data and run one query. 2–3 Excel You will create one workbook with 3 worksheets compiling information about employee sales and product sales, including charts illustrating the data. Each worksheet exercise should appear on a separate tab in the same workbook. Rename tabs as appropriate. 4 Word You will create a mail merge letter project, embedding or copying/pasting an Excel chart, identifying employees who have been with the company more than ten years, the employee who has the most sales, and the average sales for one department. You will submit a screen shot from within the Word mailmerge process showing the recipients in the data source file. 5 PowerPoint You will create a presentation summarizing the data for your supervisor to use for the quarter review administrative meeting. The basic data is provided for the database exercise and each worksheet exercise. The worksheet and database files you create will be used to create the content of the letter and the electronic presentation. It is up to you to present the information in an appealing, correct format that is easy to understand. Use bold, shading, borders, appropriate alignment, etc. to make your documents look attractive and professional. Be consistent. Correct spelling, grammar, and consistent formatting is a required. Spreadsheet Notes  If the data given is consistently whole dollar amounts, NO decimal places should appear unless specific task instructions state differently. When using Excel, consider whether your calculations will work better as rows or columns. Percentage columns should show no decimal places, be formatted with a percent sign, and include a total formula at the end.  Follow specific task instructions for column headings. If not specified, column headings should either be horizontally centered or left-aligned over text entries and right-aligned over numerical entries.  Format the first rows of data and total rows of data with dollar amounts to show a fixed dollar sign aligned at the left edge of the cell, dollar signs, and two decimal places if amounts consistently show a “cents” amount.  Check the alignment of all values before printing. Adjustments may be necessary. Text entries are typically aligned at the left, while values are aligned at the right. If using dollar signs for some entries and not others, an accounting style with the dollar sign removed may allow all numerical entries to right align consistently.  Keep a common thread throughout the spreadsheets. Be consistent. Once you have created the database file and the spreadsheets, you will view the data and analyze it. Then you will compose a mail merge letter to the board members and create an electronic presentation to use at the upcoming administrative meeting. The letter and the presentation should explain what the data represented. Both files will be authored by the student(s). Points to Look for When Analyzing the Data  Which employees have been with the company for more than ten years?  Which employee has the most sales?  What was the average sales for Computer Equipment? Project Checklist  The database file is named with the company name  The spreadsheet worksheets follow the criteria stated above and within each task for the headings  The workbook file is named with student names (LastName()s_Integration) and the sheet tab names are appropriate for the data they contain  The correct data and formulas appear  All documents look professional and are easy to read; worksheets are formatted per the Spreadsheet Notes section in the project instructions  The content of the letter answers the three questions stated in the project instructions  The presentation file contains the information per the instructions including an additional trend that the data shows  All documents have been proofread – no spelling, punctuation, or grammar errors! Page 2 Task 1: Personnel Table (Access) 1. Create a new database file using your company name as the filename. i.e. GerkenElectronics 2. Allow Access to set an automatic primary key field and create a table called Personnel with the following fields: Last Name First Name Address City State Zip Code Date Hired All field types should be set to Text except for the Date Hired field, which should be set to Date/Time. 3. Enter the following employee data: Lastname Firstname Address City State Zip Yearhired Schoo Jim 103 Tennis Court Marathon FL 33051 1/12/2007 Beeche Sandy Crophone Mike Osofer Phil 12 S. Shell Street 23 West 34th Street 34 Winding Road Destin Bell Niceville FL 32541 2/1/2012 FL 32619 6/24/2013 FL 32578 9/12/2013 Vakashion Anita 1400 Shady Lane Frostproof FL 33843 4/15/1998 Wire Barb 185 Picket Road Safety Harbor FL 34695 8/25/2004 4. Run a query listing all employees who have been with the company for ten or more years. Hint: Subtract 10 from current year for criteria, i.e. set the Date Hired criteria to <01/01/2006. 5. Include the First Name, Last Name, and Date Hired fields in the query. 6. Save the query as 10YearsPlus. 7. When you have completed all tasks, submit the Access file via Blackboard. Page 4 Task 2: Employee Sales (Excel) 1. Create a new Excel workbook file and save the workbook as LastName Integration. 2. Rename the first tab as appropriate to represent the data the worksheet will hold (for example, Employee Sales). 3. Create a worksheet that includes the following columns:  Employee  Each of the past three months (for example, February, March, April (if this is May)  Quarter Total  Quarter Average  Percent (%) of Total 4. Enter the following employees’ sales amounts showing two decimal places: Schoo Vakashion Wire 109531.50 147484.20 163075.60 188306.30 145462.90 155759.50 100532.00 123164.00 119326.40 Beeche 89706.50 133775.90 127001.60 Crophone 60481.55 62001.55 40606.40 Osofer 90974.70 108084.60 97252.00 5. Calculate the following, creating appropriate labels:  Quarter total for each employee  Total for each month, each employee, and a grand total of all sales  Each employee’s monthly average (quarterly average) 6. Calculate the percent of total sales for each employee. Hint: Divide the three-month total for each employee by the grand total of sales. Create each of these formulas manually per person, or use an absolute value for the grand total (i.e. $E$12) when entering the formula and then copy the formula. 7. Use the Merge Cells feature and center and bold the main heading information including any subtitles over the spreadsheet. Bold and center the column headings. 8. Create an embedded line chart to illustrate each employee’s sales over the three-month period to appear under the worksheet data. Display the data series in rows. Hint: Select the three months of data and click the Switch Row/Column button listed in the Chart Tools ribbon. 9. Label the category axis (x-axis) in the chart as Months, label the value axis (y-axis) in the chart as Sales, and include an appropriate main title for the chart. Hint: Go to Chart Tools → Layout tab → Axis Titles. Select the Primary Horizontal Axis Title for the category axis (x-axis) and choose Title Below. Select the Primary Vertical Axis Title for the value axis (y-axis and select Rotated Title. 10. Adjust the size of the chart if necessary. You can double-click the category axis (x-axis) and the value axis (y-axis) to shrink the font size of any text on the axis. Clicking any object selects it. Right-clicking displays a context menu that allows various formats to be adjusted. Center the worksheet and chart horizontally on the page. 11. Save and submit the file when the entire project is completed. Page 5 Task 3: Department Average Monthly Sales for the Three-Month Period (Excel) 1. In the LastName Integration workbook, rename the second tab as appropriate to represent the data the worksheet will hold, for example, Avg Monthly Sales. 2. Create a worksheet and include the following columns in the order listed:       Code Department Each of the past three months (for example, June, July, August if this is September) Average Item Sales Total Sales Percent (%) of Total Sales A110 DVDs B110 C110 DVD Players D110 E110 Computer Accessories F110 58900.50 91963.50 11958.50 636120.54 99320.58 12915.18 54188.46 84606.42 1001.82 Video Games 20124.25 21734.19 18514.31 Computer Equipment 258646.80 279338.50 237955.00 Video & Digital Cameras 146848.80 158596.70 135100.90 3. Calculate the following:  Total sales for each month  Total sales per department  Average sales for each department 4. Calculate the percent of total sales for each item. Hint: Divide the total sales for each item by the grand total of sales. Create each of these formulas manually per person or use an absolute value for the grand total ($F$12) when entering the formula and then copy the formula. 5. Center and bold main and secondary headings. Bold and left-align the first two column headings. Bold and right-align other column headings. 6. Set the page orientation to Landscape and center horizontally on the page. 7. Create a pie chart on a separate sheet to show the percentage of sales for each item to the total. Add an appropriate title to the pie chart. 8. Use a data label to show the percent. Hint: Go to Chart Tools → Layout → Labels → Data Labels → More Data Labels and check the Percentage box. 9. Add an appropriate main title and adjust the size of the legend if necessary. 10. Save and submit the file when the entire project has been completed. Page 6 Task 4: Summary Mail Merge Project (Word) 1. Begin the mail merge process by creating the data source (list of the board members). Use the names listed below. Create a screen shot of the data source file showing each of the recipient’s information to submit. Save the data source file as: StudentLnameCapstoneProj Mr. Pete Zah, 123 Mozarella Ct., Chicago, IL 62634 Mr. Wynn Dough, 456 Payne Drive, Pella, IA 54678 Mrs. Rita Booke, P.O. Box 125, Boulder, CO 80302 2. Create a one-page main letter document summarizing your findings. The body of the letter should include a paragraph explaining the answers to each of the questions below. Include the proper merge field codes and the current date. The letter is to be from you. The Greeting Line should contain a title, the last name of the recipient, and be followed by a colon. Use proper letter formatting—points will be deducted for improper business letter formatting. Be sure the following items are included in the content of your letter:  Which employees have been with the company for more than ten years?  Which employee has the most sales?  What was the average sales for Computer Equipment? 3. At the end of the letter, embed (or use copy/paste if you do not know how to embed) the chart created in Task 2 showing the total sales per each employee. 4. Complete and finish the merge, saving the merged letters as StudentName(s)MrgLetter. The merged letter file should contain 3 customized letters to each name in the data source file. Task 6: Success Presentation (PowerPoint) 1. For an upcoming administrative meeting, create a presentation that summarizes the company’s success over the past three months. Using the data in the previous exercises, compose and create a brief presentation. Explain which software was used to determine the information requested in each of the statements below. Include a design theme, a minimum of three graphics, three different slide layouts, two bulleted lists, and the following:  Title slide a. Main title: Company name b. Subtitles: Specific months and years represented and student name(s)  Slide 1-recognize which employees who have been with the company ten or more years.  Slide 2-recognize the employee who has the most sales.  Slide 3-list the average sales for Computer Equipment.  Slide 4-Create a concluding slide showing one other trend you notice from analyzing the data including a summary statement.  Additional slides as necessary to convey the required information stated above. 2. Proofread your presentation carefully. Points will be deducted for improper grammar, punctuation, spelling, and consistency in the display of the information. Save and submit the file as LastName(s)_Presentation. Page 7