**Introduction to
Information Systems in Business**

**
Microsoft Excel Assignment
(due Tuesday, June 21 at 8:00 pm)**

**
**

**Click here to Download your Microsoft Excel spreadsheet Assignment**- Save the Spreadsheet into a folder that you have created in your computer (Do not "Open" the assignment from the "File Download.")
- Save
Your Spreadsheet as
*your3randomnumbers_assignmentcode_yourlastname*(For Example, my Assignment would be named 777_EHW_DELGADILLO)**(***** NOTE: You will only get 50% credit if you don't save your file correctly!!!!*****)**

**EXCEL Assignment Instructions:**

** **

**Absolute and Relative References, AND
3-Dimensional References (20 points total)**

On Sheet1 through Sheet4:

- Determine the commission for each employee. (5 points)
- Determine the total amount for Commission A through Commission J (5 points)
- Using 3-D referencing on the sheet titled "3-D" determine the Total Sales AND the Total Commission A, Total Commission B, ..... Total Commission J, from June through September.(5 points)
Chart (5 points total): Create a Clustered Column Chart on the 3-D Sheet comparing the sales for June, July, August and September, of Melissa Leann Acosta, Ryan Keith Acuff, Jordan Chance Angerstein and Amy Rachelle Applegate

**Loan Amortization (50 points total):**

A.- On the Loan Amortization Sheet:

- Enter: Purchase price = 250,000
dollars, Down Payment =3000 dollars, Credit Rating= 620, Loan Term = 30
years, Application Date =
**6/01/201**6.

B.- Your Information - Formatted (3 points)

- Customer Name is
*Your Name* - Your 3-digit random number in the
format of a Social Security Number. You should only have to type in
your 3 digit number hand have Excel enter the rest for you.
*For example, mine would be 000-00-0777*

C.- Formatting (2 points)

- Formatting: Format all titles so that the CUSTOMER INFORMATION, EXTERNAL DATA, and LOAN INFORMATION are centered across the colored cells. Also format the titles on the bottom table so that the titles "wrap around" the cells and centered within the cells. Also give all currency amounts a dollar value with two decimal places and all percentage amounts a percentage value with two decimal places
- Use a time function to give the current date and time on cell B6 and give it a mm/dd/yyyy hh:mm format

D.- Functions (20 points)

- Use the IF function to determine the Assessment. If the Purchase Price is greater than $300,000, then there is no assessment fee. Otherwise, there is an assessment fee of $1,000 and then set the Loan Amount equal to Purchase Price - Down Payment + Assessment
- For Interest Rate use the VLOOKUP function where the Interest rate is based on the Credit Rating and the External Data Table
- For Payment use the PMT function
- Determine the Interest Payment for each payment
- Determine the Principal Payment for each payment

E.- Filling the Loan Amortization Table (20 points)

- Fill the rest of the Amortization
table up to Period #360 (the 360th month) using Absolute and Relative References. Start
with
**June 201**6 as your first month

F.- In cell B17, add an additional $300 per month to the
principal to each and every monthly payment and change the credit rating to
710. Answer in the space available
how a change in the credit rating affects the monthly payment and how adding $300 to the principal every month affects the loan duration.
**Save your results with the $0 extra to the principal for each month and
the Credit Rating at 710 (5
points).**

**PIVOT Table (10 points total):**

- Create a Sheet Called Pivot Table Results from Pivot Table Sheet given where the total amount of sales is summarized by Customer (on the columns) and Product (on the rows).
- Display on the columns only the last names of ANTON, OTTIK, and DELGADILLO (5 points)
- Display on the rows only the products: "Alice Mutton", "Konbu", "Gumbär Gummibärchen" and "Perth Pasties".
- Remember to rename your output Sheet to "PIVOT Table Results"

**STOCK VALUATION**** (20 points total):**

- Find the price per share, P/E
(trailing) ratio, P/E (forward) ratio, PEG ratio, Current Ratio, P/S Ratio,
P/Book Ratio, for the companies listed in your spreadsheet (try looking at
http://finance.yahoo.com/ and enter
the name under "Get Quotes". You can find all ratios and
figures under "Key Statistics." Don't e-mail and say that they are not all
there.
Based on the ratios and on the tables on the "Valuation Table" tab, use the VLOOKUP function to analyze each ratio and to give an overall assessment of each stock, that includes the Valuation Points and Recommendation.

**
E-Mail Rules for the Excel Assignment**

1. - I'll give you +5 extra credit points if your assignment is turned in by 8:00 pm on the due date. If it is not turned in by 8:00 pm, I'll give you a buffer of 4 hours to get 100% of your grade. You'll get 75% credit if you turn it in up to 24 hours late, and 0% after that.

2.- In the **Subject** of your email enter "**EHW**" (one
word without any spaces in between)

3.- If you turn in a second version of your assignment, you
need to type as subject **EHW UPDATE. **Otherwise, your first assignment
submitted will
be graded.

**4****.- Send a copy of an e-mail to yourself** (by typing in
your e-mail where it says "CC")

5.- E-mail the assignment **as an ATTACHMENT** (**NOT
Office 365 link**) to:

**
**