CASE 3 for BUS 160 and CIS 110
Fernando Francisco owns and operates a smali, growing iawn care business called Francisco’s Lawn Care. Record keeping for the business has reached a point where Mr. Francisco needs a better system for tracking his customers, service dates, customer payments, and revenue. After considering several options for improving his record keeping system, Mr. Fernando decided that a Microsoft Excel workbook will be beneficial for him. The Lawn Care workbook contains several worksheets, including Customer, Payment, and Amortization worksheets. The preparation of this workbook requires you to create and format multiple worksheets, create several formulas, use several functions, and prepare charts.
GET ANSWER FOR THIS PAPER AT https://bestwriters.org/order
As Mr. Francisco’s client base continues to grow, he does not want to rely on his memory for price quotes, service dates, payments, and business revenue. Instead he will use Microsoft Excel to track this information.’ As you are familiar with Microsoft Excel, you agree to develop Mr. Francisco’s Lawn Care workbook.
GET ANSWER FOR THIS PAPER AT https://bestwriters.org/order
Currently, Mr. Francisco keeps basic information about his lawn care customers in a spiral notebook. In the notebook, he records the customer’s first and last names, standard fee, additional weekly charges, total weekly charges, service day, lawn size, and start date. When a customer requests an estimate, Mr. Francisco provides an initial price quote based on the standard fees shown in Figure 1. The standard fee is the minimum price charged for a certain lawn size. Before giving a final quote, Mr. Francisco visits the customer’s site and may adjust the initial quote based on his site visit. Any additional weekly charges are determined after Mr. Francisco visits the customer’s site. Additional charges include such things as extra weed eating, yard fertilization, or cleaning a flower bed. For each customer, the total weekly charges include the standard fee plus additional weekly charges.
GET ANSWER FOR THIS PAPER AT https://bestwriters.org/order
After Mr. Francisco’s site visit, the customer is given a final quote for the total weekly charges. If the customer agrees to the final quote, a weekly lawn maintenance schedule is arranged. Mr. Francisco wants a quote sheet developed, summing the weekly charges. After he returns to his office, he will prepare the quote sheet and mail it to the customer.
In one of your conversations with Mr. Francisco, he mentions that he needs to purchase new lawn care equipment, and he will purchase the lawn care equipment on credit. Before purchasing the lawn care equipment, he will evaluate different payment scenarios. As part of the workbook’s design, he asks you to build an amortization schedule for him.
The Lawn Care workbook will store the customer, service, and payment information that is currently kept in the spiral notebook. In the Lawn Care workbook, a worksheet should be created and named Customers. The Customers worksheet will store customer information. The Customers worksheet should have columns for customer first and last names, standard fee, additional weekly charges, total weekly charges, service day, lawn size, start date, and comments. Mr. Francisco will use the Comments column to record relevant comments about the customer’s service, such as why additional charges were applied. When viewing the Customers worksheet, Mr. Francisco wants to see a total count of his customers, anticipated total revenue by day, anticipated total weekly revenue, and anticipated total monthly revenue. As the standard fee is based oh lawn size, Mr. Francisco recommends that you recreate the standard pricing guide in the Customers worksheet, and then use the VLOOKUP function to retrieve the standard lawn size price. (If you are unfamiliar with the VLOOKUP function, consider using your system’s online help feature to learn more about it.) The customer entries should be sorted alphabetically by last name.
Mr. Francisco asks you to create a Payment worksheet for him. The Payment worksheet enables Mr. Francisco to track customer payments. The Payment worksheet should include the payment date, customer’s first and last name, total weekly charges, one-time fees, this week’s total charges, amount paid, remaining balance, and comments columns.
Periodically, a customer will ask Mr. Francisco if he can remove a tree limb or trim a hedge. As this one-time fee is not reflected in the established total weekly charges, Mr. Francisco wants a one-time fee column included in the Payment worksheet. Sometimes a customer does not pay the correct amount, so Mr. Francisco wants a column to track the remaining balance. The Comments column enables Mr. Francisco to enter any comments about the transaction for later reference. For the total weekly charges column, Mr. Francisco recommends that you look up this value from the Customer worksheet.
Mr. Francisco asks you to prepare a quote sheet for him. After the initial site visit, Mr. Francisco will use the quote sheet to prepare a summary of the quoted charges. The quote sheet should shoW the standard fee, any additional weekly charges and the total weekly charges. Mr. Francisco wants the business name, current date and start date provided on the quote sheet.
Mr. Francisco will purchase new lawn care equipment on credit. To help him manage his payments, he asks you to create an amortization schedule. For each month, the schedule shows how much of the payment applies to interest, how much of the payment applies to the principle, and the remaining loan balance. Mr. Francisco will periodically make additional monthly payments of differing amounts, so he wants this capability built into the schedule.
In addition to the information requirements specified above, Mr. Francisco requests answers
to the following questions.
1. Mr. Francisco wants a list of his customers based on service day. Provide Mr. Francisco with this list.
2. Mr. Francisco wants to compare the business’s anticipated revenue by week day. Prepare a chart that compares the anticipated revenue by week day. (You select the chart.)
3. Mr. Francisco wants to compare the business’s weekly revenue by lawn size. Prepare a chart that compares weekly revenue by lawn size. (You select the chart.)
4. A local hardware store is advertising zero percent interest on all purchases, as long as the balance is paid within one year of the purchase date. If Mr. Francisco takes advantage of this offer, what will his monthly payments be? (Assume he spends $12,000 on new equipment and makes monthly payments.)
5. If the interest rate is 5 percent and the loan period is for two years, what are Mr. Francisco’s monthly payments?
Although you are free to work with the design of your workbook, it should have a consistent, professional appearance. You should apply appropriate formatting to the cells and worksheets. For instance, all cells containing dollar values should use a currency format and be formatted to two decimal places. Your worksheets should have appropriate headers, as well as appropriate column and row headings.
To prepare the Lawn Care workbook according to the specifications provided above, you will design several worksheets, construct formulas, may use the IF, COUNTIF, COUNTA, SUM, SUMIF, and VLOOKUP functions, and create charts. If you are not familiar with any of these skills, use your system’s online help feature to learn more about the skill.)
After creating your workbook, you should test your design. Perform the following steps.
1. If the lawn care business operates seven months out of the year, what is the projected revenue for the year? (Assume no new clients are added.)
2. Mr. Francisco has five new clients. Add the following new clients to your Lawn Care workbook. (Make any additional assumptions that you feel are necessary.)
Last Name
First Name
Additional
Weekly
Charges
Scheduled
Day
Lawn Size
Start Date
Your Last Name
Your First Name
$10.00
Tuesday
Small
5/6/2008
Malcom
Loretta
$15.00
Tuesday
Large
5/6/2008
Orosco
Amilso
Tuesday
Commercial
5/6/2008
Popoola
Rajesh
Wednesday
Medium
5/7/2008
Rishel
Ravi
$25.00
Wednesday
Medium
5/7/2008
3. Mr. Francisco may raise his prices for next year. Using his current customer data for analysis, what impact will these prices have on next year’s revenue?
New Lawn Care Prices
Commercial
$90.00
Large
$60,00
Medium
$45.00
Small
$35.00
In order to satisfactorily complete this case, you should build the worksheet as described in
the case scenario and then prepare both written and oral presentations. Unless otherwise
specified, submit the following deliverables to your professor.
1. A written report discussing any assumptions you have made about the case and the key elements of the case. Additionally, what features did you add to make the worksheet(s) more functional? User friendly? (Please note that these assumptions cannot violate any of the requirements specified above and must be approved by your professor.)
2. A printout of each worksheet and chart.
3. A printout of each worksheet’s formulas.
4. An electronic, working copy of your workbook that meets the criteria mentioned in the case scenario and specifications sections.
5. Results for each question posed above. (A memo to your instructor discussing these results should also be provided.)
6. As mentioned above, you should prepare an oral presentation. (Your instructor will establish the time allocated for your presentation.) You should use a presentation package and discuss the key features of your workbook. Also, discuss how the workbook is beneficial for Mr. Francisco. What additional information should be included in the workbook to make it more useful?
