Assignment 6: Working with Microsoft Excel and Word
Due Date: Wednesday, December 06 by 6:00 pm.
Late Policy: This assignment will be accepted up to one (1) day late.
Assignments submitted after Dec 06 at 6:00 PM but before Dec 07 at 6:00 AM
will be deducted 10% of the total grade.
Assignments submitted after Dec 07 at 6:00 AM but before Dec 07 at 6:00 PM
will be deducted 25% of the total grade.
Assignments submission will be closed at 6:00 PM on Dec 07
and no assignments can be submitted to OWL after that time.
This description of the assignment contains instructions that tell you to create files with names that
have a specific format. In these file names; the “youraccountname” is your UWO username.
Project 1: Microsoft Excel
This project deals with the calculation of salaries for sale representatives at a prestigious shoe store.
Within the company, sale representatives are ranked and paid accordingly. Sales representatives are
paid a commission on all sales exceeding their established quota. As members of the sales staff,
they may work different number of hours on a given day, so the sales quota is based on the hours
worked. The partially completed workbook is stored in the file “ShoeStore.xlsx”. The completed
worksheet will look similar to the following:
Complete the following instructions and save your workbook in a file named
"youraccountname_ShoeStore.xls(x)" and attach this file to your submission.
Use cell references not constant values in all of the formulas.
A) Develop the formula to calculate Base Pay, for the first employee listed. Base Pay is
calculated by multiplying the number of hours worked by employee’s wage. Create the
formula so that it can be copied down to the other employees working at the Department Store.
B) Develop the formula to calculate the amount of commission for the first employee. Create the
formula so that it can be copied down to the other employees that work at the Department
Store. Use an “IF” statement to calculate the value of the commission. An employee is
entitled to a commission if they sell more than their quota. An employee’s quota amount is
calculated by multiplying their expected sales quota per hour (column E) by the number of
hours that they worked. If their sales for the week exceed this quota amount then they are
paid a commission on the amount that they have exceeded their quota. Their commission is
based on the weekly commission that is set and shown in cell B16.
C) Develop the formula to calculate the first employee’s Gross Pay. Gross Pay is calculated by
added the employee’s Base Pay and Commission. Create the formula so that it can be copied
down to the other employees that work at the Department Store.
D) Copy the formulas for Base Pay, Commission and Gross Pay down for all of the employees
working at the Department Store.
E) Create the formula for the total weekly amounts for Sales, Hours, Base Pay, Commission and
Gross Pay using the SUM function. Place these formulas in the appropriate row in the
worksheet.
F) In cell C18, create the formula to calculate the Maximum Commission paid that week.
G) In cell C19, create the formula to calculate the Average Commission paid that week.
In cell C20, create the formula to calculate the # of Employees paid a Commission that week.
H) Replace “Your Name” in the title with your last name. (example: “Smith’s Shoe Store”)
I) Format the worksheet similar to the one shown above:
a. Edit the first two rows. Increase and Bold the text, then merge and center the first two
rows over the displayed spreadsheet (to column J)
b. Display all appropriate amounts as currency with two decimal places and a dollar sign.
c. Change the background color (highlight) the row of headings (color of your choice)
d. Bold the text for the Title rows (row 4), “Employee”, “Department”, etc.
e. Display the Commission amount in cell B16 as a percentage
f. Adjust the columns sizes as needed to fit the information contained in them
g. Underline the Title row (row 4)
h. Box in the Totals row (row 14)
i. Bold Titles in cells A16 and A18
j. Put the employees in ascending alphabetical order based on their names.
k. Add any appropriate image or clip art to the spreadsheet
Project 2: Microsoft Excel
The partially completed workbook is stored in the file “mortgage_blank.xlsx”. The workbook is
intended to calculate the mortgage payments based on the price of the house, the amount of the
down payment and the duration of the loan. The workbook will also break down payments between
the amount that will go towards paying off the actual principle and amount of the payment that goes
towards the interest payment. Notice that your early payments are going almost entirely to paying the
interest of the loan. Conversely, the later payments are counted more towards paying the principal of
the loan. The banks make sure that they make their money up front.
The partially completed workbook contains two worksheets to provide the information necessary to
complete this project.
Complete the following instructions and save your workbook in a file named
"youraccountname_mortgage.xlsx" and attach this file to your submission.
Use cell references not cell values in all of the formulas.
On the first worksheet:
A) Calculate the amount borrowed in Cell B6.
B) Develop the formula to calculate interest rate of the loan in cell F4. The formula must use the
VLOOKUP function. It will base the Interest Rate on the value entered in cell F5 (the duration
of the loan in years) based on the table in the Information worksheet of this workbook.
C) In Cell F7, calculate the total number of loan payments (# Payment Periods).
D) In Cell B11, calculate the monthly payments based on paying at the beginning of the period
Monthly payment
i. calculated using the PMT function
ii. using cell references not cell values
iii. shown as a positive number
iv. payment at beginning of payment period
E) In Cell B12, calculate the monthly payments based on paying at the end of the payment period
Monthly payment
i. calculated using the PMT function
ii. using cell references not cell values
iii. shown as a positive number
iv. payment at end of payment period
F) In Cells C11 and C12 calculate the total amount of the loan based on the corresponding
payment structure. (C11 total amount paid based on beginning of period payments – C12
based on end of period payments.)
G) Loan payments are structured on a sliding scale of principle (what you borrowed) and interest
(what you pay in order to borrow the money). The scale starts with more of the payment going
towards interest than towards the principle borrowed.
The Cells B17 and B18 show the breakdown of how much of the payment is principle and how
much is interest in the first payment.
The following cells will show the breakdown at the quarter, half, three quarters and last
payment.
Cell C16 will calculate the payment number at the quarter point (25% point of paying back the
loan). This is simply the total number of payments multiplied by 25%. D16 will then be the
payment number at the half way point (50% point of paying back the loan). This is simply the
total number of payments multiplied by 50%. E16 will then be the payment number at the
three quarter point (75% point of paying back the loan). This is simply the total number of
payments multiplied by 75%. Finally, F16 is the last payment. This will just be the total number
of payments represents the value of the number of the last payment made.
Cells B17 and B18 have been prefilled with the formulas for the breakdown of principle and
interest. Edit these formulas so they can be copied to the corresponding cells (C17 to F18)
H) In cells B20 to F20 simply compute the totals of the principle and interest for each column to
demonstrate that the combination of the two do indeed add up to the monthly payment (based
on paying at the end of the period)
I) Format the first worksheet as follows;
a. Display all dollar amounts with currency symbol and appropriate decimal places
b. Merge and Center the label in A1 up to C1. Increase the font size and bold the label
c. Put a box outline in the range of A1 to C13 and underline the label in A1
d. Merge and Center the label in E3 up to F3. Increase the font size and bold the label
e. Put a box outline in the range of E3 to F7 and line all the boxes in that range
f. Add your name to the prepared by in Cell B23
g. Highlight the cells in the range of A15 up to F15 and the range of A20 to F20.
h. Put a box outline in the range of A15 to F18 and line all the boxes in that range
i. Change the background colors of all the four (4) label rows
j. Change the font color of any cell that can be changed by the user
- Cells B4, B5, F5 and F6
k. Rename the worksheet labeled “Sheet1” to “Mortgage”
l. remove any extra (unused) worksheets from the workbook
Project 3: Microsoft Word and Excel
For this project, create a Word and an Excel document that contain information about a house
(Residence or Condominium) you are planning to purchase. The document should be used to
provide information to the financial institution you are approaching to provide the funds needed to
purchase this item. The information contained in the Word document and the Excel spreadsheet can
be real or fictional.
Complete the following instructions. Save your Word document in a file named:
“youraccountname_realEstate.doc(x)”.
Link the mortgage portion of Project 2 (Cells A1 to C13) to that document.
MORTGAGE CALCULATION TABLE
House Price $250,000.00
Down Payment $20,000.00
Amount Borrowed $230,000.00
Monthly Payments Total Amount Paid
Beginning of Pay Period $1,981.95 $475,669.10
End of Pay Period $1,995.99 $479,038.42
In the Excel workbook, select all of the cells in your spreadsheet containing data and copy the
selected range to the clipboard. Open the Word document you created and insert your Excel
workbook into it by using the paste option that allows the Excel workbook to be linked into the Word
document.
NOTE: This is a ‘live’ link. So data changed in the Excel sheet will be reflected next time the
Word document is opened with no intervention, editing or change is required by the user. The
TA will test this link by making a change in your Excel file and then re-opening your Word file.
The values in the Word file MUST reflect the change in the Excel file.
You can test your link by changing values in the Excel document and then right clicking and
selecting the option. If this option is not available, then you incorrectly only
copied and pasted and not linked as required.
Project 4: Information Systems Questions about Your Company
Create a one page MS Word document and complete the following questions pertaining to the
business you described in Assignment One (1).
1.) Would allow a committee of your employees to make decisions or should you as the company
owner have the final vote and make all the decisions?
- briefly explain your answer
2.) Name one way you might use MS Excel in your company?
- briefly describe how it could be used and what need it would fulfill.
3.) After what you have learned in CS1032, do you think you would be interested in running a
company of your own?
- briefly explain your answer
The format of this document should be identical to format you used in Assignment One (1).
Place your name, followed by the company name at the top.
Fill in the required information after.
At the end of the document, include your name, Student number and Western ID (the first part of your
Western email (i.e. if your email is – your ID will be – dernt373)
Formatting is not important as long as the document is easy to follow:
This document must be a Word file saved and submitted as a .doc (or .docx) file
The name must be a combination of your Western Account Name and the name of your company.
The file name must be youraccountname_companyname_A6.doc (or .docx)
- example (from above) dernt373_MaggicSoftware_A6.docx
Submission Instructions:
Upload and submit the following 4 files using the assignment tool on the CS1032 OWL site:
• youraccountname_ShoeStore.xlsx (for later versions) (.xls for earlier version)
• youraccountname_mortgage.xlsx (for later versions) (.xls for earlier version)
• youraccountname_realEstate.docx (for later versions) (.doc for earlier version)
• youraccountname_companyname_A6.docx (.doc for earlier version)