Unformatted Attachment Preview
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Part 1 (30 points)
In ABC Plumbing, sales have to be greater $100K for a salesman to receive a bonus
of 12% on their sales. Create a spread sheet to determine the sale persons’
commission. Your spreadsheet will look like the following. You must use the “IF and
“AND” function in Excel to calculate the bonus. Save the worksheet and name it ABC
Plumbing.
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Part 2 (40 points)
PMT Function (10 points)
After graduating from Troy University with a business degree in Information Systems, you landed a great
job in Atlanta, Georgia. You decided to buy a new car to fit your live style. Research the web and pick
out a car that you really like. You need to work out the finances for your new purchases. Following are
some examples of what a cool car looks like.
Create the following spreadsheet and format the cells accordingly.
Cost of your car
Down payment
Interest
Number of Months
72
Monthly Payment
Use the PMT function to calculate your monthly payment. Save the work sheet as My Car
Goal Seek (10 points)
After looking over your finances you discovered that you can only afford to make a monthly payment of
about a $1000.00. But one of your wealthy relative has agreed to help out by giving you the down
payment to make it possible.
You will use Using Goal Seek to determine what amount you will need for a down payment in order to
obtain a monthly payment of $1000.00 for 72 months?
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Memo (20 points)
Since your wealthy relative is a very busy business person he will need you to write a memo telling
him/her how much you will need for the down payment and how you arrived at the down payment.
You may use any memo style (Microsoft Word template). To see a list of memo style:
In Microsoft Word click on Edit, New, select an appropriate memo format to use.
i.e.
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Part 3 (30 points)
ACME Inc.
ACME Inc. is a wholesaler of widgets both in the UK and USA. The Marketing department would like to motivate
their sales team to increase their sales volume. The Director of sales would like you to create a spread sheet to
calculate the commission that each sales person earned last year as a bonus to the sales team. Below are the
preliminary data.
The business rules are as follows:
1.
2.
3.
4.
5.
Each sales person’s commission is calculated by comparing their sales with the Average Sales volume that
you calculated. If their sales is more than or equal to the average, their commission is 3%, of their sales
volume, if it is less than the average, the rate is 2% of their sales volume. (10 points)
The bonus for the year is 5% of their own Sales Volume plus the commission they earned. (5 points)
The Director would like to know how many salesperson sold more than the average sales amount. (5
points)
The Director would like to know what amount of the sales are from the UK and and what amount are
from the US. (5 points)
How many sale person performed above the average sales volume? (5 points)
You MUST use the following functions for the above calculations: AVERAGE, IF, COUNTIF, and SUMIF. No points
will be given if this requirement is not satisfied.
Deliverables: The above workbook with the 3 work sheets named appropriately i.e. ACME Inc, My Car, etc..
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Part 1 (30 points)
In ABC Plumbing, sales have to be greater $100K for a salesman to receive a bonus
of 12% on their sales. Create a spread sheet to determine the sale persons’
commission. Your spreadsheet will look like the following. You must use the “IF and
“AND” function in Excel to calculate the bonus. Save the worksheet and name it ABC
Plumbing.
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Part 2 (40 points)
PMT Function (10 points)
After graduating from Troy University with a business degree in Information Systems, you landed a great
job in Atlanta, Georgia. You decided to buy a new car to fit your live style. Research the web and pick
out a car that you really like. You need to work out the finances for your new purchases. Following are
some examples of what a cool car looks like.
Create the following spreadsheet and format the cells accordingly.
Cost of your car
Down payment
Interest
Number of Months
72
Monthly Payment
Use the PMT function to calculate your monthly payment. Save the work sheet as My Car
Goal Seek (10 points)
After looking over your finances you discovered that you can only afford to make a monthly payment of
about a $1000.00. But one of your wealthy relative has agreed to help out by giving you the down
payment to make it possible.
You will use Using Goal Seek to determine what amount you will need for a down payment in order to
obtain a monthly payment of $1000.00 for 72 months?
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Memo (20 points)
Since your wealthy relative is a very busy business person he will need you to write a memo telling
him/her how much you will need for the down payment and how you arrived at the down payment.
You may use any memo style (Microsoft Word template). To see a list of memo style:
In Microsoft Word click on Edit, New, select an appropriate memo format to use.
i.e.
IS 3310 Introduction to Information Systems & Data Analytics
Assignment 3
Part 3 (30 points)
ACME Inc.
ACME Inc. is a wholesaler of widgets both in the UK and USA. The Marketing department would like to motivate
their sales team to increase their sales volume. The Director of sales would like you to create a spread sheet to
calculate the commission that each sales person earned last year as a bonus to the sales team. Below are the
preliminary data.
The business rules are as follows:
1.
2.
3.
4.
5.
Each sales person’s commission is calculated by comparing their sales with the Average Sales volume that
you calculated. If their sales is more than or equal to the average, their commission is 3%, of their sales
volume, if it is less than the average, the rate is 2% of their sales volume. (10 points)
The bonus for the year is 5% of their own Sales Volume plus the commission they earned. (5 points)
The Director would like to know how many salesperson sold more than the average sales amount. (5
points)
The Director would like to know what amount of the sales are from the UK and and what amount are
from the US. (5 points)
How many sale person performed above the average sales volume? (5 points)
You MUST use the following functions for the above calculations: AVERAGE, IF, COUNTIF, and SUMIF. No points
will be given if this requirement is not satisfied.
Deliverables: The above workbook with the 3 work sheets named appropriately i.e. ACME Inc, My Car, etc..