BA 325 Excel SIMpath 3

On the Find Price worksheet, modify the VLOOKUP formula in cell B2 using IFERROR so if the VLOOKUP formula results in an error, the message item not found will display instead of the #N/A error.

=IFERROR(VLOOKUP(B1,STOCK,3,FALSE),"ITEM NOT FOUND")

In cell B3, enter a formula using INDEX to look up the price of the item at the row position in cell B2. Use the named range UpdatedStock as the Array argument. The price is found in the third column of the array.

Array: UpdatedStock Row_num: B2 Column_num: 3 On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Click OK to select the array,row_num,column_num option. In the Function Arguments dialog, in the Array box enter UpdatedStock. In the Row_num box, enter B2. In the Column_num box, enter 3. Click OK.

Accept Solver's solution

Click OK.

Install the Solver add-in

Click the File tab. Click Options. Click Add-Ins. Click Go... Click the Solver Add-in check box. Click OK.

The Old Town Spa worksheet has been protected. Unprotect it so users can make changes to locked cells.

Click the Old Town Spa worksheet tab. On the Review tab, in the Changes group, click the Unprotect Sheet button.

Run Solver

Click the Solve button

In cell F9, enter a formula using the INDEX function to return the ending loan balance for the row listed in cell F8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Lookup & Reference button. In the Lookup & Reference menu, you clicked the INDEX menu item. Inside the Select Arguments dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed schedule in the Array input, typed f8 in the Row_num input, typed 6 in the Column_num input, and clicked the OK button.

In cell D16, enter a formula using a counting function to count the number of blank cells in the Billable? Column (cells D2:D14).

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the COUNTA menu item. Inside the Function Arguments dialog, you typed D2:D14 in the Value1 input, clicked the OK button.

Use Solver to find the combination of units sold for each product that will result in the maximum profit in cell B1. Use the range name UnitsSold as the variable cells, and then open the Add Constraints dialog.

On the Data tab, in the Analysis group, click the Solver button. Type B1 in the Set Objective box. (The Max radio button is selected by default.) Type UnitsSold in the By Changing Variable Cells box, and then click the Add button.

Add the text Price must be a whole number greater than 30. as an error alert to the data validation rule for the selected cells. Do not include a title.

On the Data tab, in the Data Tools group, click the Data Validation button. Click the Error Alert tab. Type: Price must be a whole number greater than 30. (Include the period). Click OK.

Add the text Enter the service price. as an input message to the data validation rule for the selected cells. Do not include a title.

On the Data tab, in the Data Tools group, click the Data Validation button. Click the Input Message tab. Type: Enter the service price. (With a period) Click OK.

Create a scenario summary report. Accept the recommended results cells.

On the Data tab, in the Forecast group, click the What-If Analysis button, and click Scenario Manager. Click the Summary button. Click OK.

Create a new scenario named 5% Tax to change the value of cell B1 to 0.05. Note that in the Scenario Values dialog, the cell name Tax is displayed instead of the cell address B1.

On the Data tab, in the Forecast group, click the What-If Analysis button, and then click Scenario Manager. Click the Add button. Type 5% Tax in the Scenario name box. Click OK. Type 0.05 in the text box. Click OK.

Enter a formula in cell H3 using COUNTIF to count the number of cells where the number of items in inventory is less than five. Use the range name InStock for the Range argument.

On the Formulas tab, in the Function Library group, click More Functions. Point to Statistical, and select COUNTIF. In the Range argument box, type InStock. In the Criteria argument box, type "<5". Click OK.

Now enter a formula in cell H4 using COUNTIFS to calculate the number of rows where the value in the InStock named range is less than five and the value in the DeliveryTime named range is greater than7.

On the Formulas tab, in the Function Library group, click More Functions. Point to Statistical, and select COUNTIFS. In the Criteria_range1 argument box, type InStock. In the Criteria1 argument box, type "<5". In the Criteria_range2 argument box, type DeliveryTime. In the Criteria2 argument box, type ">7". Click OK.

Enter a formula using PV in cell B12 on the Retirement Planning worksheet to calculate the present value needed to pay a monthly annuity over the next twenty years. Cell B10 is the expected annual interest rate. Cell B11 is the number of years over which the monthly payments will be made. Cell B9 is the amount of each monthly payment. Omit the Fv argument. Payment will be at the beginning of each period, so set the Type argument to the appropriate value. Pay attention to the time periods for the interest rate, payout period, and payment schedule. Remember to express the Pmt argument as a negative

On the Formulas tab, in the Function Library group, click the Financial button. Click PV. Enter B10/12 in the Rate box. Enter B11*12 in the Nper box. Enter -B9 in the Pmt box. Type 1 in the Type box. Click OK.

Enter a nested function in cell F9 using INDEX and MATCH to find the ending balance for the date listed in cell C8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6. In the INDEX Row_num function argument, use MATCH to look up the row number for the date listed in C8. Use the named range PayDates to reference the cell range for the Payment Date column. Require an exact match.

On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter Schedule. In the Column_num box, enter 6. Click in the Row_num box, and then click the arrow in the Name box to the left of the formula bar and select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter C8. In the Lookup_array box, enter PayDates. In the Match_Type argument box, enter 0. Click OK.

In cell D3, enter a formula using MATCH nested within INDEX to find the price of the item listed in cell D1. In the INDEX function, use the named range UpdatedStock as the Array argument. For the Row_num argument, use the MATCH function to look up the row position of the item listed in cell D1 in the array named ItemNames. Require an exact match. The price is found in the third column of the array used in the INDEX function.

On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter UpdatedStock. In the Column_num box, enter 3. Click in the Row_num box, and then click the arrow in the Name box to the left of the formula bar and select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter D1. In the Lookup_array box, enter ItemNames. In the Match_Type argument box, enter 0. Click OK.

Enter a formula in cell D1 using SUMIF to calculate the total number of items ordered for the item number specified in cell B1. Use the range name JuneItems for the Range argument and JuneOrdered for the Sum_range argument.

On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIF. In the Range argument box, type JuneItems. In the Criteria argument box, type B1. In the Sum_range argument box, type JuneOrdered. Click OK.

Now enter a formula in cell D2 using SUMIFS to calculate the number of items ordered (use the named range JuneOrdered) where the value in the JuneItems named range is equal to the value in cell B1 and the value in the JunePOs named range is equal to B2.

On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIFS. In the Sum_range argument box, type JuneOrdered. In the Criteria_range1 argument box, type JuneItems. In the Criteria1 argument box, type B1. In the Criteria_range2 argument box, type JunePOs. In the Criteria2 argument box, type B2. Click OK.

Enter a formula in cell H1 using AVERAGEIF to calculate the average delivery time for items currently on order (where the value in the On Order? column is yes). Use the range name ReorderStatus for the Range argument and DeliveryTime for the Average_range argument.

On the Formulas tab, in the Function Library group, click the More Functions button. Under Statistical, click AVERAGEIF. In the Range argument box, type ReorderStatus. In the Criteria argument box, type "yes". In the Average_range argument box, type DeliveryTime. Click OK.

Now enter a formula in cell H2 using AVERAGEIFS to calculate the average delivery time (use the named range DeliveryTime) where the value in the ReorderStatus named range is equal to yes and the value in the InStock named range is equal to 0.

On the Formulas tab, in the Function Library group, click the More Functions button. Under Statistical, click AVERAGEIFS. In the Average_range argument box, type DeliveryTime. In the Criteria_range1 argument box, type ReorderStatus. In the Criteria1 argument box, type "yes". In the Criteria_range2 argument box, type InStock. In the Criteria2 argument box, type 0. Click OK.

Unlock the cells B3:D6 so the user can edit the cells when the worksheet is protected

On the Home tab, in the Cells group, click the Format button. Click Lock Cell to remove the highlight.

Enable worksheet protection for the Summary worksheet so users can select both locked cells and unlocked cells.

On the Review tab, in the Changes group, click the Protect Sheet button. Click OK.

Remove workbook protection so users can add new worksheets

On the Review tab, in the Changes group, click the Protect Workbook button.

Enable workbook protection so users cannot change worksheet names.

On the Review tab, in the Changes group, click the Protect Workbook button. Click OK.

In cell B2 on the Find Item worksheet, enter a formula using MATCH to look up the row position of the item listed in cell B1 in the array named ItemNames. Require an exact match

On the formulas tab, in the function library group, click the LOOKUP & REFERENCE button. Select MATCH. In the function arguments dialog, in the Lookup_value box, enter B1. In the lookup_array box, enter ItemNames. In the Match_Type argument box, enter 0. Click OK.

Add a constraint to limit values in the OrderWeight named range to be less than or equal to the maximum weight for the order as defined in the MaxWeight named range.

Type OrderWeight in the Cell Reference box. Type MaxWeight in the Constraint box. Click Add.

Add a constraint to limit values in the UnitsSold named range to be whole numbers only

Type UnitsSold in the Cell Reference box. Expand the drop-down list in the middle box and select Int. Click OK.

Add a constraint to limit values in the UnitsSold named range to be less than or equal to the values in the Stock named range.

Type UnitsSold in the Cell Reference box. Type Stock in the Constraint box. Click Add.