Calculate the total charges for all customer service calls

Assignment Help Management Information Sys

Reference no: EM131915881

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

Reference no: EM131915881

Questions Cloud

How gender may have influenced the communication : Distinguish how gender may have influenced the communication within your family. How about birth order (youngest, middle, oldest, only)? Other factors?

What are the barriers that prevent technologys use : Technology is integral to successful implementation in many projects, through either support or integration or both. What are the barriers that prevent its use?

Experience with socialization and culture : Your relationship as a victim and/or perpetrator of oppression. Be sure to define oppression. Your definition must come from a credible source

Describe the air quality incident : Describe the air quality incident and highlight the unique attributes of this situation and why it is a public health concern.

Calculate the total charges for all customer service calls : Enter an appropriate formula in the blue cells of Column F to compute the Total Cost charged to each customer.

Analyze future trends in health care : Analyze future trends in health care. Analyze historical data and forecasts to identify opportunities for change

Discuss the rational-legal model : Discuss the Rational-Legal model with relevant examples of two types of human services agencies currently using this approach.

Analysis based upon race - ethnicity and gender : Family of origin refers to the significant caretakers and siblings that a person grows up with. This includes: Biological family, adopted family, caregivers.

How you would respond to the discussions : Respond to the following discussions (8X). Write how you would respond to their discussions with approximately 150 words or more each.

User Account

All Pages