CMPE003

Personal Computer Concepts: Software and Hardware

Spring 1996

Lab 4: QuattroPro

You are the owner of a small bagel business that you run out of your home. Now that you have learned spreadsheet applications, you want to get rid of all those scraps of paper and use a QuattroPro spreadsheet for management of the books. You want to keep track of the customer records so that you can calculate bills and do some management analysis.

The basic data is:

Reference #     	Customer Name

1.			Cafe Dangereuse
2.			The Happy Carrot
3. 			Vic's Grocery
4. 			Bob's Bagel Bar
5. 			French Fine Deli
6. 			Woolworth Luncheonette

Reference letter	Bagel Type			Unit* Cost
___________________________________________________________________
A.			Poppy Seed			 $2.25
B.			Cheese & Dill			  3.75
C.			Mega Onion			  1.89
D. 			Mixed				  3.00

Sales for the fourth quarter of 1995 are:

	JANUARY, 1996		FEBRUARY, 1996	         MARCH, 1996

      # of   # Units  Type   # of     #Units    Type   # of   #Units  Type
Cust. Shipmt  Shipmt	     Shipmt   Shipmt	       Shipmt Shipmt
	
1.     15       10      B      14       10	 B      12	10     B
2.     18        8	A      18	 8       A      20      10     D
3.     22	12	D      20	12       D      20	10     D
4.     27       15	B      24	15	 B      30	18     B
5.     35	21	D      40	25       D      42	25     A
6.     12       15	C      12       15       C      16	15     C   	

Using the above data:

1. Create a QuattroPro spreadsheet that will display the monthly sales, by customer, shipments, units/shipment and bagel order type for January 1996. Turn in a printout.
2. Modify the spreadsheet in (A) to calculate the total charge per customer, for January. Assume that each shipment costs $4, that the sales tax is 8% and that these costs are passed on to the customer. Turn in a printout.
3. Create a graph showing revenue (total billing, either by revenue or percent) by bagel type for January. Turn in a printout.

Extra Credit (do either or both; 10 points each)

1. Repeat parts 1 and 2 for February and March. Use the three monthly bills to create a new spreadsheet showing the total quarterly sales, by customer. Turn in a printout.
2. What if the shipping charge were to increase by 10% to $4.50? For a typical month (say February) what would the resulting percentage change in total billing? Show how you arrived at your answer, via spreadsheet 'what-if" analysis techniques.

Return to CMPE003 Home Page