CIS 110
BUSINESS APPLICATIONS FOR MICROCOMPUTERS

computerletters

SEMESTER PROJECT


MS Office Logo Go to Excel Section Go to Word Section Go to PowerPoint Section Go to Access Section Go to Outlook Section

Click on one of the icons on the Program Tool Bar
above to jump to the section of the semester project
that uses that program.


Now that you have learned to create documents in Word, design presentations in PowerPoint, build worksheets in Excel, schedule appointments, create a contact list, and create an e-mail message in Outlook, and organize data in Access, it is time to put all of these skills to use.  This integrated project is a final assignment that allows you to apply the knowledge you have gained about the programs in the Office suite to produce a variety of documents you may come across in many business settings.   It will also allow you to use your newly acquired skills creatively.

SCENARIO

You are the vice president of a new business.  Your company operates two retail stores the sell products related to this business to the public.  One retail store is located in Bloomington and the other in Decatur.  The company is three years old and has seen approximately a 15 to 30 percent growth in profit each year.  Your duties as the vice president of the company include researching the market, studying buying trends, designing and implementing new projects, and supervising the marketing, sales and personnel managers.

Business Proposal

In order to get started, you will need to develop an idea for a business. This should be a retail business, something that sells products. After you decide on the type of business, you need to name it. Try to come up with something creative that reflects the type of products your business will sell. Your business concept will be submitted to the instructor for approval before you begin work on the rest of the project.


Word

Activity 1: Developing a Business Concept and Logo

In order to get started, you will need to develop an idea for a business. This should be a retail business, something that sells products. After you decide on the type of business, you need to name it. Try to come up with something creative that reflects the type of products your business will sell. Once you have a name, choose an appropriate graphic that will reflect the products your business sells (this can be something you design in a graphics program, clipart or something you find on the Internet; just make sure you record where the graphic came from). You will be using this graphic to develop a company logo. Write up your business concept in Word and save the document as SP-Business Concept.

Develop the company logo, using the company name and graphic (you may also use WordArt for the name, if you wish). Save your business logo as a graphics document (bmp, jpg, or gif) and name it SP-Logo.  Submit your business concept and logo documents to the instructor in WebCT.  

Activity 2: Composing a Memo - Writing Persuasively

Using Word, compose a memo to the president of the company, Faith Brenner, detailing your research and recommendations:

Save the completed memo and name it SP-RESEARCH MEMO. Submit on WebCT.

Activity 3: Designing a Letterhead

You are not satisfied with the current letterhead used by your company.   Design a new letterhead for the business with Word, using different alignments, fonts, and character enhancements.  (You may want to collect a couple of sample of letterheads from various companies to give you some ideas). This letterhead should be your own design and not from a template – create it from scratch.  Include the following information in your letterhead:

When the letterhead is completed, save it as SP-LETTERHEAD.   Submit your letterhead on WebCT.  You may want to print your letterhead for your portfolio.

Activity 4: Preparing a Notice Flyer

Using Word, prepare a notice about an upcoming marketing seminar.   Include the following information in the notice:

When the first notice is completed, save it as SP-NOTICE.  Print out the notice.  When the second notice is completed, save it as SP-NOTICE-WATERMARK.  Submit both on WebCT.


Excel

Activity 5: Building a Budget Worksheet

Using Excel, prepare a worksheet with the following information:
Annual Budget:  $1,450,000

Department

% of Budget

Total

Administration

10%

 

Purchasing

24%

 

Production

20%

 

Sales

11%

 

Marketing

13%

 

Personnel

12%

 

Training

10%

 

Insert formulas that will calculate the total amount for each department budget based on the specified percentage of the annual budget.   Insert a title on the worksheet, calling it “2007 Annual Budget” (the tab for this spreadsheet should have the same title).  Since you will be presenting this budget at a corporate meeting, apply formatting to make the worksheet look more professional.  You decide what formatting to use.  

Create a columnar graph as a new sheet named “Budget Chart”) from the information on the 2007 Annual Budget worksheet, using the total amounts of the budget (not the percentages) allocated to each department.  Title the graph 2007 Annual Budget.   Apply any type of formatting you wish to make the graph look professional.  When the graph is completed, save the worksheet with both sheets.  

Create a new budget spreadsheet (in the same workbook) for the company. Named “Budget”. Have an income section listing all sources of income for a given month on one worksheet (the total income should match your annual budget, so it should be $1,450,000).  In a separate part of the same worksheet, itemize expenses; you should come up with a minimum of 6 different items. Using Excel formulas and/or functions, total your projected income and expenses and then calculate net remaining dollars after all expenses are paid (which would be the profit for the company).

Once you have set up the first month the way you want, copy this worksheet so that you have 12 worksheets in the workbook, one representing each month. Be sure that the worksheets are in proper order also; you probably will have to move the worksheets after you create each one. Be sure that most of the numbers are different between each worksheet.
Rename each worksheet to denote the month and year, such as Jan 2002, Feb 2002, etc.

Go back through each month and adjust expenses accordingly. For example, your property tax for your car only comes once a year, so budget that in the month it is due. Some expenses such as water or sanitation may be billed every other month instead of every month.

Create a pie chart of one of the month's expenses.

When the worksheet is completed, save it as SP-BUDGET.  Submit on WebCT.

Remember a spreadsheet is just a tool, and the “Garbage In — Garbage Out” rule applies to it just like anything else. The benefit you derive from it all depends upon how you use it. If you track your actual expenses and compare to your budget, it should help you to adjust your future budgeted months and adequately be prepared for expenses or even help you to budget an amount to save for your future!

Activity 6: Determining Depreciation Using the Straight-Line Method – Class Project (we will do this one together in class)

Using Excel, prepare a worksheet with the following information that will determine depreciation using the straight-line method.  Also include formulas to determine the total initial costs and total salvage costs.   After the depreciation amounts are inserted, insert a formula that will calculate the average depreciation amount.
Business Name

Equipment

Initial Cost

Salvage Cost

Life of Asset

Depreciation

Copy Machine

$4,100

$650

5

 

Computer System

$25,350

$3,750

8

 

Telephone System

$15,350

$1,500

8

 

Packaging machine

$12,800

$1,250

10

 

Delivery truck

$45,000

$5,000

10

 

Total Initial Costs:
Total Salvage Costs:
Average Depreciation:

Save the completed worksheet as SP-DEPRECIATION.  Submit on WebCT.

Activity 7: Building a Sales Worksheet and Creating a Chart – Extra Credit

Using Excel, prepare a worksheet with the following information:
Total Sales:  $4,255,850

Type of Product

% of Sales

Total Sales

Product #1

22%

 

Product #2

12%

 

Product #3

17%

 

Product #4

10%

 

Product #5

25%

 

Product #6

14%

 

Replace the product numbers with the names of the products for your business.  Insert formulas that will calculate the total amount of sales for each product based on the specified percentage of the total sales.  Save the completed worksheet as SP-SALES.  Print the worksheet.  

With worksheet still displayed, create a pie chart as a new sheet with the percentage data in the worksheet.  Title the pie chart 2006 Current Percentage of Sales.  When the chart is completed, save the worksheet with both sheets.  Submit on WebCT.

Activity 8: Building a Projected Sales Worksheet and Creating a Chart – Extra Credit

Using Excel, prepare a worksheet with the following information:

Type of Product

% of Sales

Product #1

21%

Product #2

10%

Product #3

16%

Product #4

8%

Product #5

24%

Product #6

13%

Product #7

5%

Product #8

3%

Replace the product numbers with the names of the products for your business.  

Create a pie chart as a new sheet with the data in the worksheet.   Title the pie chart Year 2007 Projected Percentage of Sales.  When the chart is completed, save the worksheet both sheets) and name it SP-PROJ SALES.   Submit on WebCT.

Analyze by comparing and contrasting the pie charts created in Activity 7 and Activity 8.  What areas in the projected sales percentages have changed?  What do these changes indicate?  Assume that the projected annual income for the business is $6,200,000.  What amount of that income will come from the new product line?  Does this amount warrant marketing this new product?  Prepare a memo in Word to Faith Brenner that includes your analysis.  Add any other interpretations you can make from analyzing the data.  Save the memo as SP-ANALYSIS MEMO.  Submit on WebCT.

PowerPoint

Activity 9: Designing and Creating a Presentation

Using PowerPoint, prepare a marketing slide presentation. Include the following information in the presentation (each major bullet is one slide):

 

President

 

 

Vice President

 

Marketing Manager

Sales Manager

Personnel Manager

Marketing Assistants

Sales Associates

Assistant Manager

When preparing the slide presentation, you determine the presentation design and the autolayouts.  Use the company logo you created throughout the slides.  Include any clipart images that might be appropriate and add animation/transition effects to the slides.  Be sure to include the bullet animation effects on any slides that use bullets.   When the presentation is completed, save it and name it SP-PRESENTATION.  Run the presentation and then print the presentation with three slides on a page. Submit the electronic version on WebCT.


Access

Activity 10: Preparing a Sample Database and Organizing Data - This project will be completed in class as a group project

In Access, create a sample database with the Contact Management Database Wizard.    Save the database and name it SP-DATABASE.  Be sure to include sample data by adding 5 new records to the database using the provided options in the Main Switchboard.

When the database is completed, close the Main Switchboard dialog box and then display the Database dialog box.  Click the Reports tab, then open and print the Alphabetical Contact Listing report.  Close the report.

With the Database dialog box displayed, click the Forms tab.   Add ten new records to the Contacts form.  Be sure to fill in all fields (Display the second page of the form by clicking the 2 button located toward the bottom of the form).  Make sure you include contacts in both Decatur and Bloomington, as well as the surrounding areas.

After adding the records, save the form again, then close the form.

With the Database dialog box displayed, click the Tables tab, and then display the Contacts table.  Automatically adjust the width of all columns in the table, change the page orientation to landscape, and then print the table.

Merge the records of those contacts in the Contacts table that are located in Decatur and Bloomington to a blank Word screen.  You determine the fields to use in the inside address and an appropriate salutation.   Compose a business letter that will be sent to the contacts in Decatur and Bloomington that includes the following information:

Merge the new document then save the document with the name SP-MERGE LETTER.  Print the new document and then close it.  Save the main document as SP-Letter md and close the main document.  Submit on WebCT.


Outlook

Activity 11: Scheduling Meetings and Appointments - Extra Credit

Use Outlook to schedule the following appointments for the week of December 4, 2005:

When all appointments are scheduled, save as a webpage called SP-SCHEDULE. Submit on WebCT.

Activity 12: Designing a Web Page - Extra Credit

At a recent corporate meeting, a decision was made to create a Web site home page for the business.  You have been asked to design a preliminary Web page.  Create the Web page using the Web Page Wizard in Word.  You determine the formatting for the page.   Include the following on the page:

When the Web page is completed, save it and name it SP-WEB PAGE. Submit on WebCT.

Activity 13: Creating a Newsletter - Extra Credit

(Note:  This last activity will incorporate several of the application programs in the MS Office 2007 suite).
At a marketing meeting, the marketing department indicated that they would like to have some marketing materials that they could distribute to potential customers.  It was decided that a short informational newsletter, in which changing information could be updated readily, would serve this purpose.   You have been asked to design a preliminary informational newsletter for the business.  Create a newsletter in Word.   The newsletter should be formatted with two columns (you decide whether or not the columns are even or uneven) and should be no longer than 2 pages (so that it will fit on the front and back of one piece of paper).  You determine the formatting for most of the page, but make sure you include the following formatting features and information in the newsletter:

When the newsletter is completed, save it as SP-NEWSLETTER.   Print the newsletter for your portfolio if you wish.  Submit the electronic version on WebCT.