flam
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 NAME DESCRIPTION
CUSTOMER ID Customer identifier
LAST NAME Customer last name
FIRST NAME Customer first name
LAWN SIZE Customer lawn size
SERVICE DAY Day of the week when service is scheduled
STANDARD CHARGE Standard 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
ADDITIONAL CHARGES Charges based on additional services
Format as currency with 2 decimals
TOTAL WEEKLY CHARGES Total of standard and additional charges
(STANDARD CHARGE + ADDITIONAL CHARGES)
Format as currency with 2 decimals
START DATE First 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 NAME DESCRIPTION
PAYMENT DATE Date payment was received
CUSTOMER ID Customer 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
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 NAME DESCRIPTION
CUSTOMER ID Customer identifier
LAST NAME Customer last name
FIRST NAME Customer first name
LAWN SIZE Customer lawn size
SERVICE DAY Day of the week when service is scheduled
STANDARD CHARGE Standard 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
ADDITIONAL CHARGES Charges based on additional services
Format as currency with 2 decimals
TOTAL WEEKLY CHARGES Total of standard and additional charges
(STANDARD CHARGE + ADDITIONAL CHARGES)
Format as currency with 2 decimals
START DATE First 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 NAME DESCRIPTION
PAYMENT DATE Date payment was received
CUSTOMER ID Customer 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