Hint: : You are looking for transactions that were purchased with a credit card and were over $100, you use -100 as the criteria because the transactions in the ledger are deductions from the checking account. For all others, the function should return the text OK. If both conditions are met in the AND function, the function should return the text Flag. On the June2015 worksheet, in cell F7, nest an AND function within an IF function to determine if the transaction was paid using a credit card and the amount of the transaction is less than -100. On the June2015 worksheet, in cell I11, calculate the total amount spent on groceries using a credit card in cell I12, calculate the average spent on groceries using a credit card in cell I13, calculate the number of times groceries were purchased using a credit card during the month. On the June2015 worksheet, in cell I7, sum the AMOUNTS if the purchase in column C is groceries in cell I8, average the AMOUNTS if the purchase in column C is groceries in cell I9, calculate the number of times groceries were purchased during the month. Modify the numerical results to currency, two decimal places, if needed. You want the numbers from the Goal Seek Analysis to remain in your worksheet. Click OK and then close the dialogue box. Enter these values for the scenario: 280000, 24000. On the HomeLoan worksheet, create a scenario named Maximum using cell references for the Retail Cost, Down Payment, Interest Rate and Term (in months) ( in that order) as the changing cells. On the HomeLoan worksheet, perform a goal seek analysis to determine what the down payment in column B needs to be if you want the monthly payment in column B to be 2000. Create a two-variable data table in the range A12:H16, using the interest rate from column B as the Row input cell and the term in months from column B as the Column input cell. On the HomeLoan worksheet, in cell A12, enter a reference to the monthly payment from column B. Create a one-variable data table for the range A9:H10 using the interest rate from column B as the Row input cell. On the HomeLoan worksheet, in cell A10, enter a reference to the monthly payment from column B. Reposition the chart so that the top left corner aligns with the top left corner of cell F13. Add data labels to the outside end position displaying only the category names and leader lines. Change the chart title text to Variable Expenses and remove the legend. Change the height of the slicer to 2″ and then reposition it so that the top left corner aligns with the top left corner of cell I2.Ĭreate a PivotChart in the worksheet based on the data in the PivotTable using the default Pie chart type. Insert a Year slicer in the worksheet and use the slicer to filter the data so that only data from 2013 is displayed. Hint: To Display the values as percentages use ANALZYE > FIELD SETTINGS > SHOW VALUES AS > Select the appropriate setting Display the values as percentages of the grand total. Filter the data so that only expenses in the Variable category are displayed. Use the Format Option in the Cells Category on the HOME tab to set columns F:J to AutoFit Column Width.Īdd the Category field to the Report Filter area of the PivotTable. Change the format of the values in the PivotTable to accounting with no decimal places and set colums F:J to AutoFit Column Width. Add the Expense field to the PivotTable as a row label add the Amount field as the value add the Year field as the column label. Collapse the outline to show the grand total and Category subtotals only.Ĭreate a PivotTable in cell F1 on the AnnualExp worksheet using the data in the range A1:D17. At each change in Category, use the Sum function to add subtotals to the data in the Amount column. On the JuneTotals worksheet, sort the data in the range A3:E16 in ascending order by Category. Open exploring_ecap_grader_c2_Transactions.xlsx and save the workbook as FinalExam_YourName **Make sure you read the entire question, including hints, before you begin each section.įor the purpose of grading the project you are required to perform the following tasks: Step You will format grouped worksheets, set up validation rules, and create functions. Additionally, you will sort and subtotal data, create data tables, and use Goal Seek and Scenario Manager. You will filter the PivotTable, format the values, display the values as calculations, and create a PivotChart using this data.
#Year over yaer of grandtotal update
In this project, you will update a workbook to display bank transactions as a PivotTable. That is 48 hours, 52 times a year, for a grand total of 2500 hours.
0 kommentar(er)
