Chapter 6 Simulation
In cell A10, enter a formula using OR to display TRUE if net profit before tax in 2019 (cell B5) are greater than 750000 (seven hundred, fifty thousand) or net profit before tax in 2020 (cell C5) are greater than 750000 (seven hundred, fifty thousand). Use cell references where appropriate and enter the arguments exactly as described in this question.
Click cell A10, type =or, double-click OR in the Formula AutoComplete list, type =OR(B5>750000,C5>750000 , press Enter
In cell A8, enter a formula using AND to display TRUE if sales in 2019 (cell B2) are greater than 1500000 (one million, five hundred thousand) and sales in 2020 (cell C2) are greater than 1500000 (one million, five hundred thousand). Use cell references where appropriate and enter the arguments exactly as described in this question.
Click cell A8, type =and, double-click AND in the Formula AutoComplete list, type =AND(B2>1500000,C2>1500000 , press Enter
Enter a formula in cell E2 to calculate the absolute value of C2-D2.
Click cell E2, type =ab, double-click ABS in the Formula AutoComplete list, type =ABS(C2-D2 , and press Enter
Enter a formula in cell F1 to find the rank of the value in cell C4 compared to the values in cells C2:C13.
Click cell F1, type =rank, double-click RANK.EQ in the Formula AutoComplete list, type =RANK.EQ(C4,C2:C13 , and press Enter
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.
Click cell F9. On the Formulas tab, in the Function Library group, click the Lookup & Reference button. In the Lookup & Reference menu, click the INDEX menu item. Inside the Select Arguments dialog, click the OK button. Inside the Function Arguments dialog, type Schedule in the Array input, press Tab 3x, type 6 in the Column_num input. Click the Name Box. In the Recently Used menu, click the MATCH menu item. Inside the Function Arguments dialog, type C8 in the Lookup_value input, type PayDates in the Lookup_array input, type 0 in the Match_type input, and click the OK button
Enter a formula in cell H1 to calculate the mean of the differences in cells E2:E13.
Click cell H1, type =av, double-click AVERAGE in the Formula AutoComplete list, type =AVERAGE(E2:E13 , and press Enter
Edit the MODE.SNGL formula in cell G3 using IFERROR so if the formula results in an error, the message none will display instead of the #N/A error. Edit the formula directly in the cell or the formula bar.
Click the Formula bar, type =IFERROR(MODE.SNGL(D2:D14),"none"), press Enter
Create a new watch in the Watch Window for the selected cells.
On the Formulas tab, in the Formula Auditing group, click the Watch Window button. Inside the Watch Window dialog, click the Add Watch... button. Inside the Add Watch dialog, click the Add button
In cell C5, enter a formula to calculate the future value of this investment. Use cell references wherever possible. The interest rate is stored in cell C4, the number of payments in cell C2, and the monthly investment amount in cell C3. Remember to use a negative value for the Pmt argument.
On the Formulas tab, in the Function Library group, click the Financial button. In the Financial menu, click the FV menu item. Inside the Function Arguments dialog, type C4/12 in the Rate input, type C2 in the Nper input, type -C3 in the Pmt input, and press Enter
In cell B9, enter a formula using NPV to calculate the present value of a payment plan with variable annual payments as shown in cells B11:B14. The interest rate is stored in cell B2. Use a cell range as a single Value argument.
On the Formulas tab, in the Function Library group, click the Financial button. In the Financial menu, click the NPV menu item. Inside the Function Arguments dialog, type B2 in the Rate input, type B11:B14 in the Value1 input, and press Enter
Enter a formula using PV in cell B6 to calculate the present value needed for this pension fund. Cell B3 is the expected annual interest rate. Cell B4 is the total number of monthly payments that will be made. Cell B2 is the amount of each monthly payment. Payments will be made at the beginning of every period. Remember to express the Pmt argument as a negative.
On the Formulas tab, in the Function Library group, click the Financial button. In the Financial menu, click the PV menu item. Inside the Function Arguments dialog, type B3/12 in the Rate input, type B4 in the Nper input, type -B2 in the Pmt input, press the Tab key 3x, type 1 in the Type input, and click OK
Enter a formula using a database function to calculate the average value in the Cost column for expenses that meet the criteria in the criteria range A2:E3. The database is defined by the named range Expenses.
On the Formulas tab, in the Function Library group, click the Insert Function button. Inside the Insert Function dialog in the Or select a category drop-down, select Database. Inside the Insert Function dialog, click OK. Inside the Function Arguments dialog, type Expenses in the Database input, type A2:E23 in the Criteria input, and press Enter
Enter formula in cell B9 using the IFS function to calculate the owner's draw from the company. If the value in cell B7 is greater than or equal to 500000, the draw amount is 50000. If the value in cell B7 is greater than or equal to 150000, the bonus is 5000. If the value in cell B7 is less than 150000, display the text "no draw".
On the Formulas tab, in the Function Library group, click the Logical button. In the Logical menu, click the IFS menu item. Inside the Function Arguments dialog, type B7>=500000 in the Logical_test1 input, type 50000 in the Value_if_true1 input, type B7>=150000 in the Logical_test2 input, type 5000 in the Value_if_true2 input, type B7<150000 in the Logical_test3 input, type no draw in the Value_if_true3 input, and press Enter
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.
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. In the Lookup & Reference menu, click the INDEX menu item. Inside the Select Arguments dialog, click the OK button. Inside the Function Arguments dialog, type Schedule in the Array input, type F8 in the Row_num input, type 6 in the Column_num input, and click OK
In cell F8, enter a formula using the MATCH function to return the row number of the payment date listed in cell 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. In the Lookup & Reference menu, click the MATCH menu item. Inside the Function Arguments dialog, type C8 in the Lookup_value input, type PayDates in the Lookup_arrary input, type 0 in the Match_type input, and press Enter
Enter a formula in cell F2 using SUMIFS to calculate the total expense (use the named range Cost) where the value in the Category named range is equal to the text string "Office Expense" and the value in the SubCategory named range is equal to the text string "Parking".
On the Formulas tab, in the Function Library group, click the Math & Trig button. In the Math & Trig menu, you clicked the SUMIFS menu item. Inside the Function Arguments dialog, type Cost in the Sum_range input, type Category in the Criteria_range1 input, type Office Expense in the Criteria1 input, type SubCategory in the Criteria_range2 input, type Parking in the Criteria2 input, and press Enter
Enter a formula in the selected cell using AVERAGEIFS to calculate the average expense (use the named range Cost) where the value in the Category named range is equal to the text string "Computer Expense" and the value in the SubCategory named range is equal to the test string "Internet Access".
On the Formulas tab, in the Function Library group, click the More Functions button. In the More Functions menu, in the Statistical menu, click the AVERAGEIFS menu item. Inside the Function Arguments dialog, type Cost in the Average_range input, type Category in the Criteria_range1 input, type Computer Expense in the Criteria1 input, type SubCategory in the Criteria_range2 input, type Internet Access in the Criteria2 input, and press Enter
In cell F2, enter a formula using COUNTIFS to count the number of rows where values in the range named Cost have a value less than 500 and cells in the range named Category have the value "Computer Expense".
On the Formulas tab, in the Function Library group, click the More Functions button. In the More Functions menu, in the Statistical menu, click the COUNTIFS menu item. Inside the Function Arguments dialog, type Cost in the Criteria_range1 input, type <500 in the Criteria1 input, type Category in the Criteria_range2 input, type Computer Expense in the Criteria2 input, and press Enter
Enter a formula in cell G1 to calculate the standard deviation of the values in cells D2:D13. Assume this array is a sample of a larger set of values.
On the Formulas tab, in the Function Library group, click the More Functions button. In the More Functions menu, in the Statistical menu, click the STDEV.S menu item. Inside the Function Arguments dialog, type D2:D13 in the Number1 input, click OK
In cell E2, enter a formula using TEXTJOIN to combine the text from cells B2:D2 into a single text string. Use the # character as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument.
On the Formulas tab, in the Function Library group, click the Text button. In the Text menu, click the TEXTJOIN menu item. Inside the Function Arguments dialog, type # in the Delimiter input, type TRUE in the Ignore_empty input, type B2:D2 in the Text1 input, and click OK
In cell D2, use CONCAT to combine the text from cell B2 with the text from cell C2, with a colon : in between. The result of the formula should look like this: Rent:Parking
On the Formulas tab, in the Function group, click the Text button. In the Text menu, click the CONCAT menu item. Inside the Function Arguments dialog, type B2 in the Text1 input, type : in the Text2 input, type C2 in the Text3 input, and press Enter