Computer Homework Using Excel, Access, PowerPoint, and Word

Project Case Study Directions

Select a small business that is related to your major or some type of business you are interested in. For example, students in majors related to:

In an effort to make the business more profitable, your boss has asked you to determine which of products or inventory items should not be carried any more because they do not bring in enough revenue or what items can be added to the inventory to help make the business more profitable.

You will use Excel, Access, Word and Powerpoint to identify products that are performing well, products that are losing money, to present a plan to your boss about how you can make the business more profitable, and create a document to send to your customers to increase sales.

Step 1: Start with Excel

Open a blank Excel workbook and create a spread sheet that a business would typically capture about their products. Use the worksheet to create a minimum of 40 records of the products and other information about what your company sells.

Feel free to add columns of data as you feel necessary. You may want to capture the vendor you purchase the product from, how much your profit is on each product, etc.

Requirements for Grading:

Again, you should use this opportunity to “really show your stuff”. The more you put into this worksheet, the better. You must meet the following requirements:

· DO NOT just type in all of the information about the products you sell into the worksheet. Use a minimum of 5 formulas and/or functions in the worksheet. The following are suggestions of what you might do, but you can use whatever formulas you would like:

· Add columns to capture additional data as you see fit. Add rows to capture things like total amounts, averages, minimums, maximums, etc. Use formulas to determine prices, profits, inventory value, etc. Use a function to indicate the date on the worksheet

· Note: Functions = sum, average, min, max, IF, date, etc.; Formulas = your own calculations

· Format the worksheet. Add a title, date, headers, rename the tabs and use other elements that will make this worksheet look more professional. Use font, shading, borders, merge and center, themes, colors, etc. effectively. Your worksheet should be visually pleasing. These elements should be added.

· Use conditional formatting, spark lines or data bars at least once to highlight information

· Create at least 1 chart or graph for your worksheet. (Remember you can add data, additional worksheets, etc. to the workbook if it will make it easier to insert a chart of graph).

When you are done move to Access!

Import your Excel data into Access. See the instructor’s video instruction and ask the instructor for help to import your data. Once you have completed your import, do the following in Access:

Check the Formatting of Your Data in the Table:

Make sure that text is set as text, that currency is set to currency, etc. Do you have a primary key set? Do the field widths of the fields make sense?

Crate Queries:

Create a minimum of 3 queries showing select sets of data. (Note: Do Not use the Query Wizzard). For example, you might run a Query that shows only products that are purchased from a specific vendor; you might run a Query that shows vendors who is located in a specific state; you might run a Query that shows the top 5 most expensive products, etc.

Create 1 Report

Use the Report Wizard to create a report. Leave the default setting for views and do not add any grouping levels. Apply a Wisp Theme to the report. Change the color of the report title to Blue and make it Bold. Resize the fields as necessary so that the field names and the data are not cutoff.

Create 1 Form

Create a simple form. In Layout View resize the data fields to better fit the information. Apply the Wisp Theme to the form. Save the Form and name it.

Step 2. Import the Data From Excel to Access:

To move your data into Access, follow these instructions.

1. Highlight the DATA AND DATA LABELS ONLY on your worksheet. Do not copy things like titles with special formatting and total lines. They will not import into Access and it will give you errors in your Access Database. Only capture the data and the data labels here.

2. Select Copy on the Home Tab of the ribbon.

3. Open a new, blank worksheet in Excel.

4. Select cell A1.

5. Select Paste on the Home Tab of the ribbon.

6. Make note of the name of the tab in Excel where your “clean data is”.

7. Save and close the Excel Workbook.

EXAMPLE:

Worksheet with titles, totals, etc. Worksheet with “clean data ready to import

In Access…

1. Open, name and save a blank Access Database.

2. Switch to the External Data Tab.

3. From the Import section on the tab select Excel.

4. In the Browse window find your Excel file from step 1 and choose “Import the source data into a new table”.

5. Select Next and on the next window find and highlight the name of the tab with your clean data from the listing of worksheet names at the top. Do not accept the default tab here. Make sure you change the worksheet.

6. Work through the rest of the import screens to complete the import.

7. Open your new table. View the table in Design View. Make edits to the table if needed. For example, the Product Name probably does not need to be 255 characters long so this should be adjusted; the Price fields is probably referring to money so it should have a data type of Currency.

8. Save and close the table.

Crate Queries:

Create a minimum of 2 queries showing select sets of data. (Note: Do Not use the Query Wizard). For example, you might run a Query that shows only products that are purchased from a specific vendor; you might run a Query that shows the products that are making the least amount of profit; you might run a Query that shows the top 5 most expensive products, etc.

Create 1 Report

Use the Report Wizard to create a report. Leave the default setting for views and do not add any grouping levels. Apply a Wisp Theme to the report. Change the color of the report title to Blue and make it Bold. Resize and reposition the fields as necessary so that the field names and the data are not cutoff.

Create 1 Form

Create a simple form. In Layout View resize the data fields to better fit the information. Apply the Wisp Theme to the form. Save the Form and name it.

Step 3: Create Word Documents:

In Word, design a letterhead for your business using at least 1 graphic element such as clipart, word art, etc. Use a variety of elements to format and stylize the font. Use Borders and Shading, etc.

Then write a form letter (2 or 3 short paragraphs) using your letterhead. Your letter should be to your customers advertising some sort of big sale, purchase opportunity, new product, etc.