Ensure the Sales worksheet is active. Enter a function in cell B8 to create a custom transaction number. The transaction number should be comprised of the item number listed in cell C8 combined with the quantity in cell D8 and the first initial of the payment type in cell E8. Use Auto Fill to copy the function down, completing the data in column B. Enter a nested function in cell G8 that displays the word Flag if the Payment Type is Credit and the Amount is greater than or equal to $4000. Otherwise, the function will display a blank cell. Use Auto Fill to copy the function down, completing the data in column G.
Create a data validation list in cell D5 that displays Quantity, Payment Type, and Amount.
Type the Trans# 30038C in cell B5, and select Quantity from the validation list in cell D5.
Enter a nested lookup function in cell F5 that evaluates the Trans # in cell B5 as well as the Category in cell D5, and returns the results based on the data in the range A8:F32.
Create a PivotTable based on the range A7:G32. Place the PivotTable in cell I17 on the current worksheet. Place Payment Type in the Rows box and Amount in the Values box. Format the Amount with Accounting Number Format.
Insert a PivotChart using the Pie chart type based on the data. Place the upper-left corner of the chart inside cell I22. Format the Legend of the chart to appear at the bottom of the chart area. Format the Data Labels to appear on the Outside end of the chart.
Note, Mac users, select the range I18:J20, on the Insert tab, click Recommended Charts, and then click Pie. Format the legend, and apply the data labels as specified.
Insert a Slicer based on Date. Place the upper-left corner of the Slicer inside cell L8.
Ensure the Inventory worksheet is active. Import the Access database eApp_Cap2_Inventory. accdb into the worksheet starting in cell A3.
Note, Mac users, download and import the delimited Inventory. txt file into the worksheet starting in cell A3.
Sales Sheet
Beta Manufacturing
March Transactions
Trans # Category Results
Date Trans # Item Quantity Payment
Type Amount Status
3/1/2018 3003 8 Credit $4,800
3/2/2018 5005 4 Financed $2,800
3/3/2018 5005 1 Credit $700
3/3/2018 5005 10 Financed $7,000
3/5/2018 1001 6 Credit $1,500
3/7/2018 5005 5 Credit $3,500
3/9/2018 1001 5 Credit $1,250
3/10/2018 5005 3 Credit $2,100
3/10/2018 2002 1 Financed $500
3/10/2018 4004 4 Financed $2,600
3/11/2018 2002 9 Financed $4,500
3/11/2018 3003 8 Credit $4,800
3/12/2018 1001 9 Financed $2,250
3/13/2018 2002 8 Financed $4,000
3/13/2018 2002 9 Credit $4,500
3/14/2018 3003 9 Credit $5,400
3/18/2018 3003 1 Credit $600
3/24/2018 4004 1 Credit $650
3/24/2018 4004 3 Financed $1,950
3/24/2018 4004 10 Cash $6,500
3/28/2018 2002 10 Credit $5,000
3/28/2018 1001 9 Cash $2,250
3/30/2018 3003 8 Credit $4,800
3/30/2018 4004 6 Financed $3,900
3/30/2018 2002 9 Cash $4,500