This business/management problem is solved step by step below, with detailed explanations to help you understand the method and arrive at the correct answer.
ScanSolve AI Answer
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: =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: =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: =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: =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: =COUNTIF(DataEntry!E:E, "Savings Deposit") Formula for B14: =COUNTIF(DataEntry!E:E, "Loan Disbursement") Formula for B15: =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 | What's next?