# Inventory Analysis Excel Spre

In this project, you will create a worksheet to analyze inventory and financial data for a sporting goods store.

Skills needed to complete this project:

- Extracting Text with RIGHT, LEFT, and MID (Skill 6.1)
- Calculating Totals with SUMPRODUCT (Skill 6.3)
- Finding the Middle Value with MEDIAN (Skill 6.7)
- Finding the Most Common Value(s) with MODE.SNGL and MODE.MULT (Skill 6.10)
- Using SUMIF and SUMIFS (Skill 6.4)
- Using AVERAGEIF and AVERAGEIFS (Skill 6.5)
- Using COUNTIF and COUNTIFS (Skill 6.6)
- Using Database Functions (Skill 6.20)
- Finding Data with MATCH and INDEX (Skill 6.19)
- Managing Errors with the IFERROR Function (Skill 6.21)
- Analyzing Complex Formulas Using Evaluate Formula (Skill 6.22)
- Calculating Future Value with the FV Function (Skill 6.14)
- Calculating the Number of Payments with NPER (Skill 6.17)
- Rounding with Functions (Skill 6.2)
- Using NPV to Calculate Present Value When Payments Are Variable (Skill 6.16)
- Creating a Depreciation Schedule (Skill 6.18)

- Start with the
*Inventory*worksheet. Use the LEFT function to extract the category ID from the inventory number. - Switch to the
*Analysis*worksheet. Use the SUMPRODUCT function to calculate the total value of the inventory. Use the values in the*Selling Price*column and the corresponding values in the*Stock*column as the*Array*arguments. - Enter a formula to calculate the average selling price.
- Enter a formula to find the middle selling price.
- Enter a formula to find the most common selling price.
- What if there are multiple selling prices that are the most common? Enter an array formula in cells G2:G5 to find up to four most common selling prices.
- Use the SUMIFS function to calculate the total number of blue shoes in inventory. Use the values in the
*Stock*column as the*Sum_range*argument. Use the values in the*Item Description*column as the*Criteria_range1*argument and use the criteria **shoes*to find all items that end in the word*shoes*. Use the values in the*Color*column as the*Criteria_range2*argument and use the criteria*blue*. - Use the AVERAGEIFS function to find the average selling price of blue shoes in inventory. Use the values in the
*Selling Price*column as the*Average_range*argument. Use the values in the*Item Description*column as the*Criteria_range1*argument and use the criteria **shoes*to find all items that end in the word*shoes*. Use the values in the*Color*column as the*Criteria_range2*argument and use the criteria*blue*. - Use the COUNTIFS function to find the number of blue shoe inventory items. Use the values in the
*Item Description*column as the*Criteria_range1*argument and use the criteria**shoes*to find all items that end in the word*shoes*. Use the values in the*Color*column as the*Criteria_range2*argument and use the criteria*blue*. - Now use database functions to analyze inventory data. This method gives you more flexibility in your analysis. Once you set up the formulas, you can change the criteria in the worksheet without changing the formulas. The
*InventoryDB*named range has been created for you to use as the*Database*argument. It references A2:I25 on the*Inventory*worksheet. Notice this named range*includes*the label row.Use the DAVERAGE database function to calculate the average selling price for all items with the word

*Football*in the item description. Use the wildcard character * before and after the word*Football*to find all item descriptions with*Football*anywhere in the text. Use the column label*Selling Price*as the*Field*argument. Remember to enclose the column label in quotation marks. Set up the criteria range. - Use the DCOUNT database function to calculate the number of items in stock where the item description includes the word
*Football*and the selling price is less than $20. Use the wildcard character * before and after the word*Football*to find all item descriptions with*Football*anywhere in the text. Use the column label*Stock*as the*Field*argument. Remember to enclose the column label in quotation marks.- Set up the criteria range.

- Set up the criteria range.
- Now use MATCH and INDEX to look up the item description, quantity in stock, and selling price of an item based on the inventory number. The
*Inventory*named range has been created for you to use in these formulas. It references A3:I25 on the*Inventory*worksheet. Notice this named range does*not*include the label row.First, use MATCH to find the row position for the item listed in cell B15. Remember, the

*Lookup_array*argument must be a single column. Require an exact match. Use absolute references so you can copy the formula. - Now add an INDEX function around the MATCH function to find the item description (column 1 ) for the row position identified by the MATCH function. Use the named range Inventory as the
*Array*argument. - Add an IFERROR function around the INDEX function to hide any error messages by displaying an empty text string (
`""`). - Enter a formula using RANK.EQ to calculate the ranking of this item’s price.
- If you are having trouble with these formulas, use the Evaluate Formula feature to review the results of the nested formulas.
- Hank’s Sporting Goods is considering taking out a $25,000 loan to pay for inventory. The details of that loan are shown on the
*Financials*worksheet in cells C2:D6. You will help Hank calculate how much money he would make if he invested those same monthly payments in a money market account instead. Calculate the theoretical future value of the same amount deposited monthly in a money market account. - Hank would like to consider another loan option with the same loan amount and the same interest rate. How many months would it take to pay off the loan if he paid a flat $1,000 every month?
- Hank is easily confused by too many digits after the decimal point, so add a ROUND function to round the number of months to zero decimal places.
- Hank has another financial question for you. He needs to decide between three equipment purchase options. Option 1 is the least expensive and will require minimal repairs. It will generate the lowest increase in revenue. Option 2 is $10,000 more than Option 1 and will generate a higher increase in revenue, but it will require significant repairs in year 5 and will begin declining in productivity in year 7. Option 3 is the most expensive. It will also generate the highest increase in revenue. However, it will require more frequent and expensive repairs than the other two options.
Enter the formula to calculate net present value for Option 1 using the revenue schedule in cells B9:B18. Use a discount rate of 1.5%.

- Calculate the true cost of the investment by adding the initial investment (the purchase cost) to the calculated NPV amount:
- Now calculate two alternative depreciation schedules for the selected equipment option. First, set up the values that will be used for the common arguments
*Cost*,*Life*, and*Salvage.* - In cell J4, enter the formula to calculate depreciation for year 1 using the declining balance method.
- In cell K4, enter the formula to calculate depreciation for year 1 using the straight-line method.