Assignment
Questions
1) A tool in Access that is used to control how data is entered into a database, it ensures that the data entered will fit the format you design and fit the kind of data you want entered.
2) What base amount is used for vertical analysis of a balance sheet?
3) What Excel function would you use to calculate straight-line depreciation?
4) What Excel function would you use to calculate bond proceeds?
5) What allows you to set the location of a desired value, specify the desired value, and then identify the location of the variable value to be changed to achieve the desired value?
6) What is the interest expense formula for a bond?
7) What are the five variables in present value problems?
8) What Excel functions are used in the Hi-Lo method of predicting costs?
9) In Access, what does OLE stand for?
10) What are the three key comparison operators used in an Access query?
11) These represent the ability of a company to pay its bills
12) What is ACL's filter capability used for?
13) What is my motto?
14) What ACL command allows a filter to be set to select only open sales invoices and summarize invoice amounts for each record based on the customer number?
15) What are ACL's two most frequently used sampling methods for statistical analysis
Problems
1) Insert a Pivot Table into a new worksheet using the Problem 1 Data that shows, for each school, the total enrollment, average grant aid (in percentages) and the average overall debt (in currency). Add filters for state as well as overall rank.
Rename the pivot table sheet "Problem 1 Answer".
2) Insert a worksheet and create a Loan Amortization table like the one we did in class for a $165,000 mortgage at a 4.9% interest rate with a 30 year fixed term.
Show the monthly payment schedule for the length of the loan.
Format all your monetary fields to currency.
In cell D2, calculate the additional payment you'd make each month if you rounded your payment to $1400.00
In cell D3, put the number of payments you would take off the end of the mortgage by rounding.
In cell D4, translate that to the number of years those payments represent.
Rename your worksheet "Problem 2 Answer".
3) Copy the Problem 3A Data sheet. Rename the copy "Problem 3 Answer".
Use a formula to combine the first and last name (with a space between them) in Column C. (Do this for all the names)
Copy Column C and paste values in Column D.
Use a lookup formula in Column E to populate the numeric grade from the Problem 3B Data sheet based on the information in Column D
Copy Column E and paste values in Column F.
In column G, write an IF formula to convert each of the numeric grades to an letter grade, using the following grade scale:
|
A+
|
97.00 -100
|
|
A
|
93.00 - 96.99
|
|
A-
|
90.00 - 92.99
|
|
B+
|
87.00 - 89.99
|
|
B
|
83.00 - 86.99
|
|
B-
|
80.00 - 82.99
|
|
C+
|
77.00 - 79.99
|
|
C
|
70.00 - 76.99
|
|
D
|
65.00 - 69.99
|
|
E
|
below 65.00
|
Copy column G and paste values in Column H
Add the following column headers to each column:
Last Name First Name Full Name Pasted Full Name Calculated Numeric Grade Numeric Grade Calculation
Insert a pivot table into the same worksheet, in cell J3, to show how many of each letter grade there are.
Insert a chart (based on the pivot table), below the pivot table. Use a 2D clustered chart and add data labels.
Note** I know that we didn't do charts in class, but it was covered both in your book and in the sample excel dashboard section.
4) Copy the Problem 4 Data sheet. Rename the copy "Problem 4 Answer".
Record a macro (name your macro Problem 4) to do the following:
Insert two columns between the Name and Numeric Grade columns.
Use Text to Columns on the Name column to split the name in two columns and get rid of the comma.
Use a formula to combine the first and last name (with a space between them) in Column C. (Do this for all the names)
In column E, write an IF formula to convert each of the numeric grades to an letter grade, using the following grade scale:
|
A+
|
97.00 -100
|
|
A
|
93.00 - 96.99
|
|
A-
|
90.00 - 92.99
|
|
B+
|
87.00 - 89.99
|
|
B
|
83.00 - 86.99
|
|
B-
|
80.00 - 82.99
|
|
C+
|
77.00 - 79.99
|
|
C
|
70.00 - 76.99
|
|
D
|
65.00 - 69.99
|
|
E
|
below 65.00
|
Copy column E and paste values in Column F
Add the following column headers to each column:
Last Name First Name Full Name Numeric Grade Calculation Letter Grade
5) Copy the Problem 5 Data sheet. Rename the copy "Problem 5 Answer".
Make it so the colored cells cannot be negative.
Make it so the Employee Incentives field can also not be more than 25% of Employee Benefits
The maximum amount allowed for Small Equipment should be 700.
Add a warning to Small Equipment that stops the entry - title it Maximum Amount Exceeded with a message that reads:
"Small equipment cannot be more than 700 dollars - anything greater is considered a capital outlay."
Make only the colored cells editable. No other cells should be allowed to be selected or altered without the password "cis360".
Attachment:- Problem-Assignment.rar