Excel formulas for financial analysis: calculating balance impact, total loan disbursement, total savings, and maximum single savings deposit.

TASK B: SPREADSHEET MANAGEMENT (Continuation)

1. Data Manipulation:

• b) In column I, heading "Balance Impact", enter a formula in I2 that shows the amount as positive for savings and negative for loans/repayments. Copy the formula down. Write the formula used in cell I7.
  • Assuming TransactionType is in column E and Amount is in column F.
  • Formula for cell I7: $$\boxed{\text{=IF(E7="Savings Deposit", F7, -F7)}}$$

2. Financial Analysis (Use the Analysis sheet):

• a) In cell B2, calculate the Total Loan Amount Disbursed to members.

  • Assuming TransactionType is in column E and Amount is in column F on the DataEntry sheet.
  • Formula for B2: $$\boxed{\text{=SUMIF(DataEntry!E:E, "Loan Disbursement", DataEntry!F:F)}}$$

• b) In cell B3, calculate the Total Savings Collected from all members.

  • Assuming TransactionType is in column E and Amount is in column F on the DataEntry sheet.
  • Formula for B3: $$\boxed{\text{=SUMIF(DataEntry!E:E, "Savings Deposit", DataEntry!F:F)}}$$

• c) In cell B4, use a function to find the Maximum Single Savings Deposit made.

  • This is an array formula, entered by pressing CTRL+SHIFT+ENTER.
  • Assuming TransactionType is in column E and Amount is in column F on the DataEntry sheet.
  • Formula for B4: $$\boxed{\text{{=MAX(IF(DataEntry!E:E="Savings Deposit", DataEntry!F:F))}}}$$

3. Data Visualization:

• a) Create a summary table for chart data in columns A12 to B15 and fill the cells with the data (A12: Transaction Type, B12: Count, A13: Savings Deposit, A14: Loan Disbursement, A15: Loan Repayment).

  • Action: In your spreadsheet, create the following table starting at cell A12:

    Transaction Type	Count
    Savings Deposit
    Loan Disbursement
    Loan Repayment

• b) Enter a formula in B13, B14, B15 that calculate the number of Savings Deposit, Loan Disbursement, Loan Repayment respectively made.

  • Assuming TransactionType is in column E on the DataEntry sheet.
  • Formula for B13: $$\boxed{\text{=COUNTIF(DataEntry!E:E, "Savings Deposit")}}$$
  • Formula for B14: $$\boxed{\text{=COUNTIF(DataEntry!E:E, "Loan Disbursement")}}$$
  • Formula for B15: $$\boxed{\text{=COUNTIF(DataEntry!E:E, "Loan Repayment")}}$$

• c) Using data in the cell range A13:B15, insert a Pie (2-D) chart.

  • Action: Select the range A13:B15 (the transaction types and their counts). Go to the "Insert" tab, then "Charts", and select a "2-D Pie" chart.

• d) Add Data Labels.

  • Action: With the chart selected, go to "Chart Design" (or "Chart Tools" -> "Layout") and add "Data Labels".

• e) Format Data Labels to include Percentage and Category Name.

  • Action: Right-click on the data labels, select "Format Data Labels", and ensure "Category Name" and "Percentage" are checked.

• f) Add chart title: "Transaction Type Distribution".

  • Action: Select the chart title and type in "Transaction Type Distribution".

4. Advanced Functions:

• a) In cell J1, create a dropdown list (Data Validation) for the BranchLocation (Buea, Limbe, Tiko) for data entry.

  • Action: Select cell J1. Go to the "Data" tab, then "Data Tools" -> "Data Validation". In the "Settings" tab, set "Allow" to "List". In the "Source" box, type Buea,Limbe,Tiko. Click "OK".

• b) Enter the various BranchLocation using the dropdown list.

  • Action: Click on cell J1 and select one of the branch locations from the dropdown list.

5. Formatting and Presentation:

• a) Apply cell borders to the data range A1:J9 on the DataEntry sheet.

  • Action: Select cells A1 to J9 on the DataEntry sheet. Go to the "Home" tab, in the "Font" group, click the "Borders" dropdown, and select "All Borders".

• b) Format all amounts (CFA) to display with a thousand separator and no decimal places.

  • Action: Select the column(s) containing amounts (e.g., column F). Right-click, select "Format Cells". In the "Number" tab, choose "Number" category, check "Use 1000 Separator", and set "Decimal places" to 0.

• c) Bold the row 1 containing the column headers.

  • Action: Select row 1. Go to the "Home" tab and click the "Bold" button (B).

• d) Save and print the DataEntry and Analysis sheet.

  • Action: Save your Excel workbook. Go to "File" -> "Print". Select the DataEntry sheet and print it. Then select the Analysis sheet and print it.

TASK C: PRESENTATION

1. Slide Setup:

• a) Create a new PowerPoint presentation. Save it as BYEC_Presentation.pptx.

  • Action: Open Microsoft PowerPoint. Go to "File" -> "New" -> "Blank Presentation". Then "File" -> "Save As" and save it as BYEC_Presentation.pptx.

• b) Choose an appropriate Title and Content slide layout for the first slide.

  • Action: On the first slide, go to the "Home" tab, click "Layout", and select "Title and Content".

2. Title Creation:

• a) Add the title: "BYEC PERFORMANCE DASHBOARD (2024)".

  • Action: Click on the title placeholder on the first slide and type: BYEC PERFORMANCE DASHBOARD (2024).

• b) Format the title font to Algerian, size 44, and center it.

  • Action: Select the title text. Go to the "Home" tab. Change the font to "Algerian", set the font size to "44", and click the "Center" alignment button.

3. Table Creation:

• a) Insert a table with 4 columns and 4 rows to present the data provided.

  • Action: In the content placeholder of the slide, click the "Insert Table" icon (or go to "Insert" tab -> "Table"). Specify 4 columns and 4 rows.

• b) Enter the data accurately into the table.

  • Action: Enter the following data into the table you just created:

    Branch	Total Members	Total Savings (FCFA)	Total Loans (FCFA)
    Buea	45	5,250,000	3,800,000
    Limbe	32	3,100,000	2,500,000
    Tiko	28	2,800,000	1,950,000