# flam

Topics: Spreadsheet, Lawn, Lawns Pages: 5 (1264 words) Published: April 6, 2014
Case: Green Scene Landscaping (GSL)
Customer Tracking
Objectives
In this case you will learn how to:
Enter formulas in cells
Apply functions: COUNTIF, PMT, SUMIF, VLOOKUP
Apply formatting: Cell and Worksheet
Create charts: Pie
Chris Zelinski started Green Scene Landscaping (GSL) five years ago, offering a wide range of lawn care services. In the early years of the business, Chris kept up with his customers, price quotes, service dates, and payment information using his memory and some paper-based records. As the business has grown, Chris has begun to realize that a better system for tracking customers is required. After considering several options, he has decided to replace his current system with an Excel workbook. Chris has begun to create the workbook with a worksheet for customer information and a worksheet for payment information. The Customer worksheet contains a number of fields which are described in Figure 1. FIELD NAMEDESCRIPTION

CUSTOMER IDCustomer identifier
LAST NAMECustomer last name
FIRST NAMECustomer first name
LAWN SIZE Customer lawn size
SERVICE DAYDay of the week when service is scheduled
STANDARD CHARGEStandard charge for service based on lawn size (Use VLOOKUP to retrieve the STANDARD CHARGE based on the LAWN SIZE) Format as currency with 2 decimals
Format as currency with 2 decimals
TOTAL WEEKLY CHARGESTotal of standard and additional charges (STANDARD CHARGE + ADDITIONAL CHARGES)
Format as currency with 2 decimals
START DATEFirst date of the service contract

Figure 1: Customer Field Descriptions

The Payment worksheet contains a number of fields which are described in Figure 2. FIELD NAMEDESCRIPTION
CUSTOMER IDCustomer identifier
TOTAL WEEKLY CHARGES Total charges
(Use VLOOKUP to retrieve TOTAL WEEKLY CHARGES from the Customer worksheet based of CUSTOMER ID) Format as currency with 2 decimals
ONE TIME CHARGESCharges based on one time services
Format as currency with 2 decimals
TOTAL CHARGESTotal of weekly and one time charges
(TOTAL WEEKLY CHARGES + ONE TIME CHARGES)
Format as currency with 2 decimals
AMOUNT PAIDAmount paid by customer to date
Format as currency with 2 decimals
REMAINING BALANCEAmount remaining after payment
(TOTAL CHARGES – AMOUNT PAID)
Format as currency with 2 decimals
Figure 2: Payment Field Descriptions
Perform the following tasks to finish the Customer Tracking workbook for Chris. All tasks should be labeled and data formatted to look clean and professional. When finished, submit your spreadsheet in Blackboard. Task 1:Retrieve the GSL case data file containing Chris’ customer and payment information. Open up Excel and copy the first page of data into a worksheet labeled Customer and copy the 2nd page of data into a worksheet labeled Payment. Save the workbook as (Your Last Name)_GSL. Task 2:The table in Figure 3 reflects the charges for service based of lawn size. Add this table to the workbook on a separate worksheet named Lookup Table. LAWN SIZESTANDARD CHARGE

Small\$35.00
Medium\$40.00
Large\$50.00
Commercial\$75.00

Figure 3: Standard Charges based on Lawn Size

Task 3:Complete the Customer worksheet by adding the formulas for Standard Charge and Total Weekly Charges as described in Figure 1. Make sure to format the cells as specified. Task 4:Complete the Payment worksheet by adding the formulas for Total Weekly Charges, Total Charges, and Remaining Balance as described in Figure 2. Make sure to format the cells as specified. Once the new columns are added insert 3 rows above the columns and place the title of the company in the first row, the title of the worksheet in the second row and today’s date in row 3 (using today date function). Merge & center the titles, shade the background a color of your choosing, increase the font size to 14 pt. or larger for rows 1 & 2 and then bold the...