Excel & VBA Project Instructions

Starting File $($Excel File$).$ If you need additional information$/$data to complete this assignment, you should be able to find it with a simple Google search. I am being intentionally vague with these instructions. The challenge of this assignment is working through all the technical difficulties to complete the assignment.

Project Guidelines:

$$ For this project, you are required to leave all your formulas in the cells. No cells should be $$hard$-$coded$$ on the Report by State tab. If you have a cell hard$-$coded, you will receive zero points for that section.

$$ While there are many ways to accomplish the tasks the macro will accomplish, you are not allowed to use any other technique to accomplish what the macro is supposed to accomplish. Your macro should complete every task requested in Step $5.$ No additional actions should be needed. $($I$$ll be grading your assignment based on your VBA code, so I will be able to tell if you used any other techniques.$)$

o Your macro should complete all tasks with a single run. You will lose points if multiple runs of the macro are required to complete a task.

$$ You are allowed to use other techniques to verify your answers. $($You can test your answers with filters, etc.$)$

o This is great practice when doing something new. It is called independent verification. If you reach the same answer using two completely different approaches, you likely reached the correct answer.

$$ While it is not required, I highly recommend that everyone in the group work through this assignment independently. Then, you can check your answers against each other$$s answers. Again, this provides independent verification. It also helps each person in the group learn some highly useful skills.

$$ The grading rubric is loaded to Canvas. All point values reflect the final submission.

Required Tasks:

$1.$ Rename the Excel Group Project $$ Starting File to $$Excel Group Project $$ Group #$$

a$.$ Renaming the file to include your group number will help me to keep track of the submissions.

b$.$ Be sure to leave the file format as $.$xlsm $($Excel Macro$-$Enabled Workbook$).$ Otherwise, your VBA macro$($s$)$ will be deleted when you close your file.

$2.$ List at least three group rules you will be following to complete this project.

a$.$ You will create the rules.

b$.$ Please list the rules on the Group Participation tab in the Excel workbook.

$3.$ Populate the Pivot Data tab with a pivot table that references data on the $$Loans $$ Accepted and Rejected$$ tab.

a$.$ You can format your pivot table however you think best.

$4.$ Populate the table on the Report by State tab in the Excel file.

a$.$ Each requested column on this tab should be populated with a formula.

b$.$ The main data you will be using to populate this tab in the pivot table you created in Step $3.$

c$.$ The final view of the tab should be the same as the initial tab I provided. $($Except all the columns will be populated in your version.$)$

i$.$ Note: You are not allowed to sort this tab.

d$.$ The column descriptions are as follows:

i$.$ State Abbreviation: the two$-$letter abbreviation for the state.

$1.$ You are not allowed to sort this tab. You will lose points if you sort the tab.

ii$.$ State Name: the state name.

$1.$ Hint: You may need to look up this information.

iii. Total Applications: a count of all loan applications received.

iv$.$ Accepted: a count of loan applications that were accepted.

v$.$ Rejected: a count of loan applications that were rejected.

vi$.$ Acceptance Rate: The count of loans that were accepted divided by the count of all loan applications received.

$1.$ You should convert any #Div$/0$ errors to zeros.

vii. Expected Acceptance Rate: This is the expected acceptance rate for each state by year.

viii. Variance: The difference between the Acceptance Rate and the Expected Acceptance Rate.

$1.$ Note: A positive variance is when the actual acceptance rate is greater than the expected acceptance rate.

e$.$ Apply conditional formatting to the Variance column.

i$.$ negative variances are highlighted red

ii$.$ positive variances are highlighted blue

f$.$ Note: Be sure to only include $2007$ data in columns C$-$H$,2008$ data in columns J$-$O$,$ etc.

g$.$ Note: If done properly, you can simply write formulas for cells C$3$ H$3.$ Then, you can copy these formulas to all other cells for the other years. You will lose points if you need to update your formulas.

h$.$ Hint: I$$m able to complete the project to this point in about $10$ minutes.

Step $4$ Guiding Principles:

$$ Nothing should be $$hard$-$coded$$ in the columns you are expected to populate. Everything should be populated with a formula.

$$ You are not allowed to use the order of states to perform any lookups. All lookups must either reference the state abbreviation or state name.

$$ You are not allowed to $$hard$-$code$$ the state names into any formulas.

$$ You are not allowed to re$-$sort the data on the Expected Acceptance Rates tab.

$$ You should not have to change your formulas when copied for other state