COMPUTER APPLICATIONS INTEGRATION (MICROSOFT WORD, SPREADSHEET AND DATABASE)

INTEGRATION (MICROSOFT WORD, SPREADSHEET AND DATABASE)

Daley’s Fruit Farm produces various fruits, which are sold to supermarkets, restaurants, etc. Mr. Daley has been communicating with his customers, as well as observing sales, to determine whether certain fruits should be discontinued, or additional fruits should be offered.

A spreadsheet is to be prepared to record sales over a 6-month period (January – June 2003), and also to keep records of customers. A database is to be prepared to give a breakdown of sales for the month of June. A letter will be prepared using a word processing application, to be sent to all customers who are shareholders.

Part A

You are required to create a spreadsheet to keep track of sales over the 6-month period (January – June 2003)

1. Reproduce the following spreadsheet and save it as FRUIT RECORDS.

2. Add a column for TOTALS to the left of June to display the total sales for each fruit. Enter formulas to calculate these totals, as well as the total sales for each month.

3. Sales for the month of April were accidentally omitted from the Accounts. Insert a column at the relevant position and enter the following sales figures for the month of April:

Bananas 1100 Lemons 1105

Pineapples 1000 Papayas 950

Watermelons 1620 Oranges 1400

4. Format all numeric columns as currency with zero decimal place.

5. Add a column at the end of the spreadsheet with the title % SALES, to calculate the percentage of total sales for each fruit. Format the percentage to 2 decimal places.

6. Center the heading across the spreadsheet. Save as FRUIT RECORDS 2.

7. Sort the spreadsheet in ascending order on fruits. Save as FRUIT RECORDS SORT.

8. Construct a column chart to compare the sales for each fruit for each month. Give the chart the title “MONTHLY SALES BY FRUIT.” Label the X and Y axes.

9. Create a pie chart to compare the percentage sales for each fruit. Give the chart the title “SALES.” Label each slice of the pie chart with the fruit and the percentage.

10. Create the following spreadsheet on a new sheet in the FRUIT RECORDS 2 File. Name the sheet JUNE FRUITS.

PART B

You are now required to create a database (FRUIT SALES IN JUNE) to keep a detailed record of sales for

the month of June.

1. Import the sheet JUNE FRUITS. Give the table the name JUNE FRUITS. PROD is the primary key. The table should have the structure.

2. (i) a. Create the following database tables:

CUSTOMERS

(i) b. Enter the following data to the CUSTOMERS table.

2. (ii) a. SALES

(ii) b. Create a value list with all the fruits (Produce), from which selections can be made.

Do the same for the Cust. Num. field.

3. Create the following queries:

(i) Display all sales with quantity exceeding 40 lbs. List the Name of the Customer, the Produce, and the Quantity. Save the query as MAX SALES.

(ii) Due to the decline in the sale of Papayas, this produce may be discontinued. List all sales of papayas for the month of June, including Customer Name, Date and Quantity (lbs.). Save the query as PAPAYAS.

(iii) Mr. Daley needs to correspond with shareholders. List the Company name, Address and City of all shareholders. Be sure to remove duplicates. (Do not display the shareholders field). Save this query as SHAREHOLDER LIST.

PART C

1. In a word processor, create a letterhead for Daley’s Fruit Farm. Include a farm-related graphic.

2. Using the letterhead, prepare the letter overleaf. Save it as FARM FORM, in preparation for a mail

merge. The query Shareholder List from the database segment will be used as the secondary

document (data source)

3. Set the line spacing to 1.5” and the margins should be set to 1.5 inches at the left and right. Justify

the document.

4. Insert the footer “Daley’s Sales Report” on each page. Make the footer italics and a point-size of 10.

5. Insert page numbers at the top center of each except the first page.

6. Insert the Spreadsheet and the charts at the correct positions in the document.

7. Insert the queries Max Salas and Papayas at the positions indicated.

8. The last sentence in the paragraph preceding the Papayas query is displaced. Make it a paragraph of its own immediately following the Papayas query.

9. Using the query Shareholder List as your secondary document, merge it with the main document. Save the merged letters as Share Letters.

FORM LETTER

Today’s date

Company Name

Address

City

Dear Sir/Madam:

I take this opportunity to inform you, as shareholder in Daley’s Fruit Farm, of the status of the organization.

Sales have been recorded for the past 6 months (Jan. – Jun 2003), and it is evident that some types of produce are doing better than others. Below is a copy of the spreadsheet used to conduct this analysis:

<Insert spreadsheet FRUIT RECORDS SORT here>

Pineapples and Watermelons are producing a greater income than the other fruits, particularly Papayas and Lemons. You can do your own analysis from the following charts:

<Insert Pie Chart here>

<Insert Column Chart here>

As a whole, Daley’s Fruit Farm is doing fairly well. Certain fruits are continually purchased in large quantities. The table below show the fruits purchased in quantities exceeding 40 lbs, and by whom, for the month of June.

<Insert query Max Sales here>

Based on the demand, I have decided to discontinue the sale of Papayas and start offering Cantaloupes, which have been highly requested by customers over the past months. The above query lists all sales of Papayas for the month of June.

<Insert query Papayas here>

Thank you for your kind patronage over the years. I anticipate your continued support and cooperation.

Sincerely,

Mike Daley

Manager