Practical Assignment: Microsoft Excel for Accounting Information Systems
Module: (ACCN203/ARMGT202) Accounting Information Systems
Objectives:
• To develop hands-on experience in using Microsoft Excel for financial data analysis,
reporting, and decision-making.
• To understand the use of Excel tools in preparing and analysing financial reports,
budgets, and forecasts.
• To apply financial formulas, data validation, and charts to create a dynamic financial
model.
Instructions:
1. Download the provided dataset (financial_transactions.xlsx).
2. Each task must be answered on a new sheet.
3. Use one Workbook.
4. Save your workbook as: Surname_StudentID.xlsx
Tasks:
Task 1: Preparing Financial Data (20 Marks)
1. Data Formatting (5 Marks):
o Open the dataset in Excel. Format appropriate columns to display currency
values in USD, and ensure that dates are properly formatted
(DD/MM/YYYY).
2. Data Sorting & Filtering (5 Marks):
o Sort the transaction data by Date in ascending order.
o Add filters to each column heading where all values/items are selected.
o Filter out transactions that occurred before January 1, 2014.
3. Data Validation (5 Marks):
o Apply data validation to the column for Discounts to allow only valid entries.
4. Conditional Formatting (5 Marks):
o Highlight Sales greater than $300,000 in red.
Task 2: Summarizing Financial Data (25 Marks)
1. Pivot Table (10 Marks):
o Create a Pivot Table to summarize the total Profit by Product and Month.
o Add a slicer for filtering data by Product.
o Create an appropriate pivot chart for the pivot table.
2. Formulas (10 Marks):
o Calculate the total Sales for Paseo and Montana for the year 2014.
o Mark Profit as “High Value” if the amount exceeds $150,000, “Medium
Value” if the amount exceeds $90000 and “Low Value” otherwise.
o Count the number of times Canada is appearing in Country column.
3. Subtotal (5 Marks):
o Group the data by Segment and calculate the subtotal for each segment.
Task 3: Financial Analysis (30 Marks)
1. Profit and Loss Statement (15 Marks):
o Create a new sheet called "P&L Statement".
o Using the financial data, prepare a monthly Profit and Loss (P&L) statement.
▪ List total income, total expenses, and calculate net profit for each
month.
2. Break-Even Analysis (5 Marks):
o Assume fixed costs of $5,000 and variable costs per transaction of $20.
Calculate the break-even point using Excel's formula.
3. Scenario Analysis (10 Marks):
o Create a simple scenario manager to assess different sales growth rates (5%,
10%, and 15%) and their impact on profit.
o Use the “What-If Analysis” tool to show the results for each scenario.
Task 4: Data Visualization (25 Marks)
1. Charts (15 Marks):
o Create a line chart for monthly income, expenses, and net profit.
o Insert a bar chart comparing total income and expenses by Product.
2. Dashboard (10 Marks):
o Create a dashboard on a new sheet. This dashboard should include:
▪ A Pivot Chart from Task 2.
▪ A summary of monthly net profits.
▪ A dynamic slicer to filter data by Transaction Type.
Submission Guidelines:
• Save the final Excel file with all tasks completed.
• Submit your file via google class by 20 October 2024.
Marking Criteria:
Task Maximum Marks Student Marks
Task 1: Preparing Data 20
Task 2: Summarizing Data 25
Task 3: Financial Analysis 30
Task 4: Data Visualization 25
Total 100
Note:
• Ensure that formulas are correctly applied and functional in your Excel sheet.
• The assignment should be your original work; plagiarism will result in penalties or
your work not marked.
