## Description

Project 1: Microsoft Excel

This project prepares a monthly revenue information ( profit/loss ) for salespersons in your company

for the month of April, 2019. The partially completed workbook used for this project is stored in the

file “Commissions.xlsx”. You must use the supplied file “Commissions.xlsx” as your workbook or you

will lose major marks if you use any other file.

The Net Earnings for each Salesperson minus the Per Unit Costs to the Salesperson will compute the

profit or loss for each individual Salesperson.

Your finished work should resemble the image below exactly (but with the correct answers of

course… and the colors and column spacings do NOT have match exactly, close enough is good

enough in regards to the actual colors and widths. So, do not get too worried about it…)

For this scenario we assume the Salesperson acts as an agent for your company and is not an

employee. As such, they have to pay your company for the product at a much reduced price, then

they sell the product to a customer. This scenario benefits your company by making the Salesperson

into another profit center for your organization.

Each salesperson sells two of the items that your company manufactures. This is the ‘Niblick’ and the

‘Pit Mashie’. Each salesperson also offers the customer an optional Warrantee on each item they sell.

This Warrantee does not cost the Salesperson any money, so the commission on the warrantees is

pure profit for the salesperson. This incentivizes them to sell more Warrantees. The Warrantees are

the same cost for either product.

The workbook is intended to compute the revenue profit/loss for each salesperson.

The Product Sales Revenue includes ONLY the total from the sales of the two products (the ‘Niblick’

and the ‘Pit Mashie’). It is based on the number of units sold times the price to the consumer for each

product.

The Product Sales Commission the salesperson earns is based on the total sales of ONLY both of

the items. This column does not include the commissions from the Warrantee sales. This commission

is based on the sliding scale shown in the table starting at cell B21.

The Warrantee Sales Revenue is based on the total number of warrantees sold times the warrantee

cost to the customer.

The Warrantee Sales Commission the salesperson earns is based on the total sales of ONLY the

number of warrantees sold that month. This column does not include the commissions from the

Product sales. This commission is based on the sliding scale shown in the table starting at cell F21.

The salesperson’s Net Earnings for the month is the total of all the commissions they earned in the

month.

The Unit Cost to the Salesperson is the total cost the salesperson paid your company for the two

items they sold in the month based on the table starting at cell F15.

The numbers in the tables may change month by month, so the current values are stored in the

worksheet, BUT you must use cell referencing in ALL your equations. (hint: you cannot use the actual

numbers in the calculations)

The Revenue Profit/Loss is then simply computed as the difference between the Net Earnings and

the Unit Costs to the Salesperson.

All calculations must be written so they can be copied to each corresponding cell using the Absolute

and Relative cell referencing as covered in class. (i.e. write the formula in cell E4 so as it can be

copied to cells E5 to E11 without any changes)

Complete the following instructions and save your workbook in a file named:

“youraccountname_Commissions.xlsx” and attach this file to your submission.

A) Develop the formulas for the Product Sales Revenue, Product Sales Commissions, Warrantee

Sales Revenue, Warrantee Sales Commissions, Net Earning, Unit Costs to the Salesperson

and the Revenue Profit and/Lost columns. The formulas for these calculations will use the

information provided in the Purchase Price to Customer, Product Sales Commission Scale,

Per Unit Costs to Salesperson and Warrantee Commission Scale tables of the worksheet. The

formulas must be created using cell references.

B) Copy the formulas for the first salesperson (Isabelle Ringing) to the remaining seven

salespersons.

C) Calculate the Totals row (row 12) using the values in each associated column.

D) Delete any unused sheets in the workbook.

E) Format the worksheet as follows:

a. Display all dollar amounts with the Canadian currency symbol and two decimal places

b. Display all percentages as percent (%) values as in the image above.

c. Change the name of the worksheet to April 2019

d. Change the first row so it is spread out over all the used columns (A through K) and

center the name of the company in row 1. Bold and Increase the font size of the

Company Name Title to 14 or 16 (your choice).

e. Highlight (change the background color) and bold the titles of the row of Labels (row 3),

the row of Totals (row 12)

f. Make sure to add the grid lines so they appear for the row of Labels (row 3) and the row

of Totals (row 12) and the Profit/Lost column (Column K) exactly as in the image above.

g. Highlight (change the background color) and bold the titles of the table labels (rows 15

and 21) exactly as shown in the image above. (note – the highlighting is JUST in the

table titles in these two specific rows as in the image above.

h. Put a bold line around each of the table as shown in the image and make sure the grids

are set as in the image above.

i. Adjust the column sizes to fit the information contained in them. All values must be

displayed in all resolutions (no ####### due to too narrow cell spacing).

j. Add your name to the worksheet following “Prepared by:” (B28)

k. Add the current date (format Month Day, year – example: July 6, 2023) to the worksheet

following “Date:” (K28)

l. Change the Revenue Profit/Loss column so the text is black and regular and the

background is light yellow for positive values and text is yellow and italics and the

background is red for negative values. You must set this so the colors will automatically

change if the values change from positive to negative or from negative to positive.

(note: image above does not have the correct answers…

notice some of the values are different from the actual assignment file)

Project 2: Microsoft Excel

The partially completed workbook is stored in the file “MortgageCalculator _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. You must

use the supplied file “MortgageCalculator _BLANK.xlsx” as your workbook or you will lose major

marks if you use any other file.

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_ MortgageCalculator.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 (change the background color) the cells in the range of A15 up to F15 and the

range of A20 to F20 as shown in the image above.

h. Put a box outline in the range of A15 to F18 and line all the boxes in that range

i. Highlight (change the background colors ) of all the four (4) label rows so they look like

the image above. (i.e. the dark red, red, blue and light orange cells).

j. Change the font color of any cell that can be changed by the user to a blue color text.

– Cells B4, B5, F5 and F6

k. Rename the worksheet labeled “Sheet1” to “Payments”

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. This should be three or four short paragraphs in length.

You are not graded on what you write, only that the imbedded Excel is included with some text.

Describe the location of the house, the layout, how many rooms and bathrooms, etc.

Complete the following instructions. Save your Word document in a file named:

“youraccountname_ MortgageCalculator.doc(x)”.

Link the mortgage portion of Project 2 (Cells A1 to C13) to that document.

MORTGAGE CALCULATION TABLE

House Price $1,245,450.00

Down Payment $320,000.00

Amount Borrowed $925,450.00

Monthly

Payments

Total

Amount

Paid

Beginning of Pay Period $6,587.49 $2,371,497.44

End of Pay Period $6,630.04 $2,386,813.37

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 instantly be updated in an

opened Word document or will be reflected next time the Word document is opened. These

updates will occur 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 checking 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 checking your

Word document.

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 – dernt373@uwo.ca 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_Commisssions.xlsx (for later versions) (.xls for earlier version)

youraccountname_ MortgageCalculator.xlsx (for later versions) (.xls for earlier version)

youraccountname_ MortgageCalculator.docx (for later versions) (.doc for earlier version)

youraccountname_companyname_A6.docx (.doc for earlier version)

AND FOR THE FINAL TIME, PLEASE: Do not cheat or copy or work in groups.

Remember, you will need to know how to perform these functions for the

final exam. Remember, you must achieve at least a 49% on the final

exam in order to receive a passing grade