Problem Sheet A
Green Lawns provides yard service and maintenance for homes in the Mount Vernon, Ohio area. Gary Taylor manages the accounts for Green Lawns and wants to use Excel to record monthly service calls made by the company.
Gary provides you with the data of a list of service calls made in the month of August 2012.
The first column contains the customer's name. The second column is the last Service Date.
The third column is the Base Fee charged for this customer (a setup fee). The fourth column is the number of Service Person Hours on the service call (for example, 2 people working 3 hours results in 6 Service Person Hours).
The fifth column is the Hourly Rate. In the next column,you must compute the Total Cost (without tax) charged by Green Lawns to each customer: The Total Cost(without tax) is computed as the Base Fee plus the Number of ServicePerson Hours multiplied by the Hourly Rate.
1. Enter an appropriate formula in the blue cells of Column F to compute the Total Cost charged to each customer. Start by entering the formula in the first row, and then copy it down for all the Service Calls
2. The Total Cost (without tax) for the first customer in the table is:
A. $96.85
B. $166.78
C. $185.36
D. $75.06
E. None of the other answers is correct.
3. Enter a formula to calculate the Total Charges for All Customer Service Calls(without tax) in the first blue cell above the data table.
A. $24,025.77
B. $24,642.83
C. $20,828.10
D. $20,083.26
E. None of the other answers is correct.
Some of Green Lawns' customers are government contractors. The State of Ohio recently passed legislation that exempts government contracts from tax liability on lawn maintenance costs.
Column G lists those contracts that are and are not government contracts. For those contracts that are not government contracts, the tax rate is 7%.
4. The spreadsheet currently uses government codes where "1" is governmental status and "2" is non-governmental status. In the blue cells in Column H, use the lookup table and a function to convert the government code to the words "Government" or "Non-Government."
5. Using a reference to the tax rate in cell I5, determine the Total Cost (with tax) charged to each customer (formula should take into account that some customers are exempt from taxation). Start by entering the formula in the first row and then copy it down for all of the Service Calls.
6. The Total Cost (with tax) for the first customer in the table is:
A. $244.56
B. $246.14
C. $198.34
D. $200.65
E. None of the other answers is correct.
7. Enter a formula to calculate the Total Charges for All Customer Service Calls (with tax) in the second blue cell above the data.
A. $21,489.08
B. $26.367.82
C. $26.292.76
D. $20,083.26
E. None of the other answers is correct.
Problem Sheet B
Argy Russell, the owner of Agora Boutique, wants to look at some of her retail sales data using Excel Charts. She has identified several items and their sales in dollars over the past 6 years (she labeled the Years 1 through 6). These appear in the data in the next worksheet.
8. First, create a pie chart that shows the sales of all items from 2002 only and move the chart to a new worksheet named "Pie Chart."
9. Add a chart title and "explode" the outerwear slice of the chart to emphasize the sales in the outerwear segment.
10. Next, create a scatter chart that plots Tuxedo Sales by year only and move the chart to a new worksheet named "Scatter Chart."
11. Add a best fit line, R2 value, and equation to the chart.
12. Argy would like to predict what her Tuxedo sales will be for the year 2003. Using the equation you just calculated, calculate Argy's expected Tuxedo sales for 2003. Show your work in cell A10 in the Problem Set B worksheet.
A. $8476.91
B. $8363.46
C. $8623.64
D. $8265.97
E. None of the other answers is correct.
Attachment:- Problem Sheet Data.rar