Description
Project 1: Microsoft Excel
This project assists the professor of History 1032 in calculating the final grade for students who have recently taken the course. The final grades are calculated as a weighted average of the marks obtained for the three tests and the final exam. The partially completed workbook used for this project is stored in the file “history.xls”. This workbook already contains the test and final exam marks for the students who have taken the course. Use cell references not cell values in all of the formulas.
The completed worksheet will look similar to the following:
Complete the following instructions and save your workbook in a file named “youraccountname_project1.xls” and attach this file to your submission.
A) Enter your name as the course instructor in cell B34 and date prepared in B35. B) In the worksheet enter a formula to calculate the final mark as a weighted average of the student’s three tests and final exam. Use the weights found in the cells C24:C27, matching each weight with the corresponding test and exam mark. Create the formula using the appropriate cell referencing, so that the formula can be copied to the other students. C) Copy the formula for calculating the final mark to remaining students in the course. D) In cell D24, enter a formula to calculate the average mark for “Test 1”. E) In cell E24, enter a formula to calculate the maximum score for “Test 1”. F) In cell F24, enter a formula to calculate the minimum score for the “Test 1”. G) Repeat steps E) through G) for the other two tests, the final exam and the final mark.
H) In cell G24, enter a formula to calculate the range of marks for “Test 1”, which is equal to the difference between the maximum and the minimum mark. I) Copy the range formula from the first test to the other two tests, final exam and final mark. J) In cell C30, enter a formula to count the number of students in the course. K) In cell C 31, enter a formula to count the number of students that failed. (final mark< 50) L) In cell C32, enter a formula to calculate the passing average. ( the average final mark for only the students that passed (final mark =50) M) Format the worksheet as follows; a. Display all marks with no decimals and the weights as percentages. b. Sort the student marks portion of the worksheet in ascending order by Student ID c. Change the background colour of the header rows (4, 5 & 23) d. Conditionally format the final mark column to show all failures (final mark<50) in Red. e. Adjust the columns sizes as needed to fit the information contained in them f. Increase the font size of the Title (cell A1) and center it within the columns
Project 2: Microsoft Word and Excel For this project you will be creating a Word document that contains information about a high-definition entertainment system that you wish to purchase. The entertainment system should consist of a TV, sound system, and installation. The information contained in the Word document and the Excel spreadsheet can be real or made up. Use cell references not cell values in all of the Excel formulas.
Complete the following instructions. Save your Word document in a file named “youraccountname_project2.doc(x)” and your workbook in a file named "youraccountname_project2.xls(x)" and attach these files to your submission.
A) Create a one page Word document that provides descriptive information about a high-end entertainment system that you are planning to purchase. The one page Word document must include the following: a. A title at the top of the page containing the TV manufacturer’s name, assignment due date and your name. b. At least one paragraph of information related the TV that is to be purchased. c. A hyperlink to a related Web site. B) Create an Excel spreadsheet that contains the following information: a. Price of each part of the system (TV, sound system, installation, etc.) b. Total cost of the system (using the SUM function) c. Down Payment d. Amount to finance (calculate using a formula using cell references) e. Annual Interest Rate f. Term of the Loan (in years) g. Monthly payment i. calculated using the PMT function ii. using cell references not cell values iii. shown as a positive number h. Format the worksheet as follows: i. dollar amounts displayed as currency (dollar sign and 2 decimal places) ii. percentages displayed with a percentage sign (%) C) 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 created in Part A) and insert your Excel workbook into it by using the paste option that allows the Excel workbook to linked into the Word document.
Project 3: Microsoft Excel
You have been asked by the Lenders Loan Company to create a workbook that contains one worksheet for loan payment calculations and a second worksheet that displays a table that shows loan payments for varying interest rates. The workbook to be used for this project is stored in the file “lenders.xls”. Use cell references not cell values in all of the formulas.
Complete the following instructions and save your workbook in a file named "youraccountname_project3.xls" and attach this file to your submission.
A) On Sheet1 add in your own amounts for Purchase Price in cell C4, Down Payment in cell C5, Interest Rate in cell C7 (between 0.01125 and 0.04) and Term of the Loan in cell C8 (in Years). B) On Sheet1 create a formula to calculate the Loan Amount in cell C6. C) On Sheet2 create a formula to calculate the Monthly Payment for the first interest rate shown (0.01125). Use the PMT function using arguments taken from both the first and second worksheet, ensuring the result is shown as a positive number and the calculation is based on a monthly payment to be paid at the beginning of the month. The formula must be created in a way that it can be copied to the other interest rates as listed. D) On Sheet2 create a formula to calculate Total Interest for the first interest rate shown (0.01125). The Total Interest is equal to the number of payments times the Monthly Payment, less the Loan Amount. The formula will need to use cell references from both worksheets and must be created in a way that it can be copied to the other interest rates as listed. E) On Sheet2 create a formula to calculate Total Cost for the first interest rate shown (0.01125). The Total Cost is equal to the Purchase Price plus the Total Interest. The formula will need to use cell references from both worksheets and must be created in a way that it can be copied to the other interest rates as listed. F) Copy the formulas for Monthly Payment, Total Interest and Total Cost from the first interest rate to all of the interest rates shown on Sheet2. G) On Sheet1 use the Vlookup function based on the interest rate to show the Monthly Payment in cell C10. The Vlookup formula will need to use cell references from both worksheets. H) On Sheet1 use the Vlookup function based on the interest rate to show the Total Interest in cell C11. The Vlookup formula will need to use cell references from both worksheets. I) On Sheet1 use the Vlookup function based on the interest rate to show the Total Cost in cell C12. The Vlookup formula will need to use cell references from both worksheets. J) Format the Sheet1 as follows; a. Display all dollar amounts with a currency symbol and two decimal places b. Display the interest amount as a percentage c. Change the “Lenders” in the title (cell A1) of the worksheet to your last name d. Adjust the columns sizes to fit the information contained in them e. Increase the font size of the title and center the title over the columns f. Rename the worksheet labeled “Sheet1” to “amounts” K) Format Sheet2 as follows: a. Display all dollar amounts with a currency symbol and two decimal places b. Display all interest amounts as a percentage with two decimal places c. Adjust the column sizes to fit the information contained in them d. Change the background colour of the Header Row (2) e. Increase the font size of the title and center the title (cell A1) over the columns f. Rename the worksheet labeled “Sheet2” to “schedule”
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.) Which of Porter’s Four Competitive Strategies applies to your company (page 68 in the text)? - briefly explain why you choose this strategy
2.) Name one way you might use MS Excel in your company? - briefly describe how it could be used and what need it would fufil.
3.) What scenario (why would you) request a hardware upgrade for your company. - under what condition do you think it might be required to suggest a capital expenditure on computer hardware? (Unless you think it is never necessary, then explain your reasoning.)
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 was - derntwis@uwo.ca your ID would be - derntwis) 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.doc (or .docx)
Submission Instructions: Upload and submit the following files using the assignment tool on the CS1032 OWL/Sakai site: • youraccountname_project1.xls (2003) or youraccountname_project1.xlsx (later versions) • youraccountname _ project2.xls (2003) or youraccountname_project2.xlsx (later versions) • youraccountname _project2.doc (2003) or youraccoutname_project2.doc (later versions) • youraccountname _project3.xls (2003) or youraccountname_project3.xlsx (later versions) • youraccountname_companyname.doc or youraccountname_companyname.docx