Can you re make it$?$

Problem solutions must make full use of defined name ranges using the labels indicated for each

cell$/$range$.$ Excepting Problem $4$: Forecasting, cell addresses should not be used in the formulas.

MAIN TASK: READ CAREFULLY BEFORE PROCEDING

Create one Excel Workbook containing separate worksheets to each of the problems below.

Save the workbook as your full name, e$.$g$.,$ anna$\_$watson.xlsx $($all lowercase$).$ Be sure to save

your workbook as a normal Excel workbook file with $.$xlsx extension. To confirm, after the

submission, download your file into a new location on your computer $($or $-$ better $-$ another

computer$)$ and try to open it$.$ If it opens in Excel, you're probably fine.

Each problem must be solved in its own worksheet within one workbook, so rename each sheet

tab with the specific problem title, e$.$g$.,$ Loan, Students, Tiers, etc. $($Ensure to keep everything

compact so that they are easily viewable when opened.$)$

You may use the raw data as provided in the examples and supplementary files to test your

solutions.

Be sure to include all required components in appropriate format for each problem solution.

Up to $50\%$ penalty will be applied for not using named ranges optimally.

Important note about named ranges in Office $365$: Due to some new features added to Excel recently,

named references may result in #N$/$A or #SPILL errors. If these are returned Problem $2$: Students

Re$-$create the model below that searches the students table and returns the data on the student, given a

student id$.$

Download database file Students.csv and import into your workbook $($Figure $4).$

The model works by having the user enter the ID and Excel returns the details stored in the

database table. Figure $5$: for example, ID k$0962$ is entered by the user and Excel returns the related

Info data.

Use the ID value input as the lookup value for VLOOKUP.

The solution should be a single 'master' formula that would work for any attribute i$.$e$.,$ the

formula that returns the correct Name in the searcher Info is appropriate for being Auto$-$Filled

down the column to return the rest of the attributes available in the Database.

Place the searcher at the top of the worksheet if the database and the Searcher components are

in the same worksheet.

Note that the contents in the cells in the first row of Figure $3$ and the contents in the first

column of Figure $4$ are identical.

Key functions to use: VLOOKUP, MATCH; possibly INDEX

Figure $3$ students table

Figure $4$: searcher because of names in your

formulae, begin the named reference with an $\text{'}$at$\text{'}$ sign $($@$),$ e$.$g$.,$ @SALES instead of just SALES.