Page 1 of 7 At-Home Final Exam Project Directions BPC110 – Computer Usage and Application SCENARIO You are the Sales Manager for the LLC Computer Store. The computer store buys and sells computers from a number of different suppliers. The owner wishes to eliminate some manual processes by automating procedures. He would like to have an Access database to store and retrieve supplier contact information and details about the computers in inventory for the upcoming sales promotion. He wants to use Word to create a flyer to distribute to customers announcing new promotions. He also needs an Excel file to track available inventory and inventory sales promotion information. Finally, he would like to have a PowerPoint presentation that will run continuously at the computer store kiosk to advertise ongoing or new sales promotions. NOTE: You will make up all of the data required for each portion of the project. Use real-world data by searching the Internet if you need ideas. Keep track of all Web sites or other sources from which you search and use information or images. You will be required to provide a mandatory References section using APA style in the last PowerPoint slide for the entire Final Project. For help with APA style, visit the Rio Salado College Citation Guides Web page. REQUIREMENTS For the Final Project, you will be required to apply the knowledge and skills gained from the four Microsoft Office programs covered in the course: Access, Excel, Word, and PowerPoint. You must submit the following four files to your instructor, as directed in the online lesson. Be sure to complete all components of the project and submit all files; otherwise your At-Home Final Exam Project may not be accepted. Be sure to replace “MEID” in the filename with your actual MEID. 1. ComputerSuppliers_MEID.accdb 2. ComputerInventory_MEID.xlsx 3. ComputerFlyer_MEID.docx 4. ComputerPresentation_MEID.pptx For each program, you must complete the following: 1. Microsoft Access a. Create tables with all necessary fields that contain suppliers’ contact and computer inventory information for LLC Computer Store. b. Create a form to allow new entries into the database. c. Create a report to list the inventory of computers. d. Create and run queries. 2. Microsoft Excel a. Create worksheets that contain inventory, sales, payment information, and a chart that provides a visual representation of the data. 3. Microsoft Word a. Create a flyer to advertise new promotional products. 4. Microsoft PowerPoint a. Create a presentation that shows the computers available for the current sales promotion. Page 2 of 7 TIP: Before starting the project, read through the directions for the individual sections so you understand how each file relates to the others. Complete the project in the sequence presented, starting with the Access database and ending with the PowerPoint presentation. MICROSOFT ACCESS Use Access to create a database to store and retrieve Supplier and Inventory information for the LLC Computer Store. 1. Create a new Access database. 2. Save the file as ComputerSuppliers_MEID.accdb. STRUCTURE OF THE DATABASE Read the requirements for the database below, design it, and understand how it should work before you create the design. Remember, Access is a relational database; be sure you reduce data redundancy during the design process. Once you have created the tables, you will need to join them and establish referential integrity. For this project, assume a Supplier can supply the computer store with multiple computers for the store’s inventory. Be sure to think about which fields you should include in the tables to properly store the data as some data elements may require more than one field. CREATE THE TABLES 1. Create a Table for Supplier Contact information and set the Primary Key and appropriate data types for the fields. The table will contain supplier contact information to be used to contact the inventory supply sources. (Hint: You will enter data in the table using the form you create later.) Your table should include the following at a minimum: a. Supplier ID b. Company Name (e.g., Dell) c. Company Address d. Sales Representative Name e. Phone Number f. Email Address 2. Create a Table for the Computers in Inventory and set the Primary key and appropriate data types for the fields. At a minimum, the table should contain the information below to describe each computer. The table will be used as a source of information when looking for specific computers, and it will contain valuable information for determining profit and possible sales. (Hint: You will enter data in the table using the form you create later.) Your table should include (but is not limited to) the following: a. Inventory ID b. Supplier ID c. Model (e.g., OptiPlex 7440) d. Computer Type (e.g., desktop or laptop) e. Operating System (e.g., Windows 10) f. Dealer Cost g. Retail Price NOTE: Remember that each table must have a Primary Key. Page 3 of 7 3. Establish table relationships: a. Join the tables b. Establish referential integrity CREATE A FORM 1. Create a Form that can be used to enter data into both Tables you designed 2. Use the form to enter at least four Suppliers. Include your name in one of the records as a Sales Representative for the Suppliers. 3. Use the form to enter at least two models for each Supplier. CREATE A QUERY 1. Create a Query that will SUM the Dealer Cost by each Company. 2. Create a Query that will show the Company Names and Sales Representatives from the Supplier Table, and all inventory information from the Inventory Table. The query must also sort the data by the Last Name of the Sales Representative. NOTE: Your database must contain two queries. CREATE A REPORT 1. Create a Report that lists all items in inventory. Group the report by company name and include the following items in the report: a. Company Name b. Model c. Computer Type d. Operating System e. Dealer Cost f. Retail Price 2. Format report so that it fits on one page. 3. Save the file. Page 4 of 7 MICROSOFT EXCEL Use Excel to create a workbook, composed of three worksheets, that contains the LLC Computer Store inventory and sales promotion information. The workbook should include worksheets for store information and computer inventory (Store Information), financing information (Payment Information) and a chart of computer prices (Price Chart). (TIP: When working in Excel, remember to use Font, Alignment, and Number Formatting features as appropriate.) 1. Create a new Excel workbook. 2. Save the file as ComputerInventory_MEID.xlsx. STORE INFORMATION 1. Name the first worksheet Store Information. 2. Assign the Sheet Tab a color of your choice. 3. At the top of the worksheet add the Title: LLC Computer Store. 4. Below the Title, create Row Labels for the following: a. Store Name: b. Store Address: c. Store Phone: d. Promotion Title: (You will determine the Promotion Title.) 5. Format the Row Labels and cells so they appear like the image to the right. The image depicts a range of cells that contains four rows and two columns. The entire range has an Outside Border. The first column contains the Row Labels from Step 4 and is right-aligned. The second column contains a Bottom Border for each cell of the column. 6. Fill in the information for the Store Name, Store Address, Store Phone, and Promotion Title in the second column. Note: Remember the above information as you will use it in other areas (e.g., PowerPoint and Word). 7. Below the Row Labels, import the Query with Company Names and Sales Representatives from the LLC ComputerSuppliers Access database. 8. Apply a Table Style of your choice to the imported table. 9. Add a Total Row to calculate the total for the Dealer Cost and Retail Price. 10. Apply Conditional Formatting to indicate the top two retail prices (Retail Price) with a unique cell shading and font color. 11. Add your name, MEID, course and section number in the Footer. 12. Format the worksheet to print on a single page. PAYMENT INFORMATION 1. Create a new worksheet named Payment Information. 2. Assign the Sheet Tab a color of your choice. 3. At the top of the worksheet add the Title: LLC Computer Store Payment Information. 4. Below the Title, create a table with column headers for the following items to calculate the payment information for the sales promotion. (Hint: Use all computers from the Store Information worksheet.) a. Inventory ID b. Company Name Page 5 of 7 c. Model d. Retail Price e. Down Payment (Use a formula to calculate a down payment of 10%.) f. Loan Amount (Use a formula to calculate the Loan Amount.) g. Interest Rate (Interest Rate is 2%.) h. Term (Finance for 5 years.) i. Monthly Payment (Use a payment function to calculate the monthly payment.) 5. Place formulas in an area below the Payment Information table to determine the following calculations for the Retail Price column: (Hint: Use an Excel built-in function for your formulas.) a. Minimum Retail Price b. Maximum Retail Price c. Average Retail Price 6. Add your name, MEID, course and section in the Footer. 7. Format the worksheet to print on a single page. 8. Create a Chart that includes all Models and the Retail Price of each computer. (TIP: Be sure to choose an appropriate chart type.) a. Move the Chart to a new worksheet named Prices Chart. (Hint: Use the Excel Ribbon feature to move the chart to a new worksheet.) PRICES CHART 1. Apply a style or effect that visually enhances the chart to give it a professional appearance. Your chart must include (but is not limited to) the following elements: a. A Title with your choice of formatting applied b. Data labels c. Axis titles 2. Assign the Sheet Tab a color of your choice. 3. Move the Prices Chart Worksheet so that it is the last worksheet in your workbook. 4. Add your name, MEID, course, and section number in the Footer. 5. Save the File. Page 6 of 7 MICROSOFT WORD Use Word to create a professional-looking flyer to inform customers about an upcoming promotion for the LLC Computer Store. 1. Create a new Word document. 2. Save the file as ComputerFlyer_MEID.docx. DOCUMENT PREP 1. Design a flyer from scratch. The flyer should include information about the sale of available inventory. (Hint: Do not use a Word built-in template.) a. Choose a Theme other than the Office default. b. Include a Page Border. 2. Create a business address block using the store name, address, and phone number from the Excel section of the project. Apply the following formatting to highlight the address block: a. Heading style b. Paragraph justification c. Paragraph border d. Paragraph shading of your choice 3. Insert text (minimum of 50 words) emphasizing the sale of the available inventory. a. Use your course end date as the date of the sale. b. Use your name as the contact person. 4. Use each of the following features to make your flyer eye-catching by applying special formatting to the text. a. Change font size and color b. Include at least one WordArt 5. Insert at least one Shape with a fill color of your choice and use the Promotional Title you created in Excel as the text. 6. From the Payment Information worksheet in Excel use Copy as Picture to paste the table into your flyer. a. Apply the appropriate text wrap for visual appeal. b. Apply at least one Picture Style or Effect of your choice. 7. Reposition or resize flyer elements to keep it on one page. 8. Insert a Watermark indicating that your design is a Sample. 9. Check spelling and grammar. 10. Create a Footer with your name, MEID, and course and section number. 11. Save the file. Page 7 of 7 MICROSOFT POWERPOINT Create a presentation that can be used to show on the store monitors to advertise the computers available for a promotional price. 1. Create a new PowerPoint presentation. 2. Save the file as ComputerPresentation_MEID.pptx. PRESENTATION 1. Apply an appropriate theme to the presentation. 2. On the title slide enter the name of the computer store (LLC Computer Store) as the main title and the Promotion Title from the Excel workbook as the subtitle. 3. Create three additional slides to showcase the available inventory. You will need to select the appropriate slide type to accomplish the different tasks. a. Select one of the Computer Models from the Store Information Worksheet and insert a computer image onto the slide. b. Include the Company Name and Model of the computer as the slide title. c. Include the Computer Type and Retail Price for the caption. d. Repeat Steps a. though c. above for the two other showcase slides. 4. Insert a new slide with an appropriate slide layout for testimonials from three customers who have purchased computers from the computer store. (TIP: You can make up the customers’ names.) a. The title should be “Testimonials from Previous Customers.” b. Include a quote from each of the three customers concerning their experiences with their purchases. The quotes can be made up but must be appropriate for the project. c. List the customer’s name below each quote. Use your own name for one of the customers. 5. Insert a new slide with an appropriate slide layout and a title of “LLC Computer Store Family of Employees” and add an image of the store employees. 6. Insert a new slide with an appropriate slide layout to add the list of References used for research during the creation of all content in the Final Project. You are required to use the APA style format for any Web site or other source from which you retrieved images or data. 7. Apply a transition between all the slides. 8. Apply an animation to the images on each slide so they enter when the slides change. 9. Within the slide notes for each slide: a. Identify the slide layout you selected. b. Explain why you felt the slide layout was appropriate for the presentation content. 10. In the Notes Master View, a. Add your Name and MEID to the Header of the document. b. Add your Course and Section to the Footer of the document. 11. Make sure that you set up the slide show to run continuously on the computer store monitors. You should have a total of seven slides. 12. Save the file.