Assignment: Create a Financial Forecast and MIS Report

What This Assignment Is About

You will use AI tools in Excel to create a financial forecast and build a management information system (MIS) report. This assignment tests your ability to use AI assistants to analyse data, generate predictions, summarise insights, and present information clearly for decision-making.

Scenario

You work as a business analyst at a growing online retail company. Your manager has asked you to prepare a financial forecast for the next quarter and create an MIS report that shows sales performance, identifies trends, and highlights areas that need attention. You have a dataset containing monthly sales data, expenses, and profit margins from the past 12 months. Your task is to use AI tools in Excel to build the forecast and prepare a professional report for the leadership team.

Tasks

Task 1: Generate a Sales Forecast Using AI

Open Microsoft Excel with an AI assistant available (such as Copilot or another AI-powered Excel tool). Load your sales data into a spreadsheet, or create a sample dataset with 12 months of monthly sales figures. Use the AI assistant to generate a forecast for the next three months based on the historical data you provided.

Your goal is to ask the AI to create this forecast for you. Do not manually calculate it yourself.

Task 2: Ask AI to Explain the Forecast Method

Now that you have a forecast, use the AI assistant again to find out what method or approach it used to generate the prediction. Ask it to explain how it calculated the forecasted values.

Your goal is to understand the logic behind the forecast, not just see the numbers.

Task 3: Create a Summary Table of Key Metrics

Use the AI assistant to build a summary table that shows the following metrics for each month in your dataset:

• Total Sales
• Total Expenses
• Profit Margin (%)
• Month-over-month growth rate (%)

Once the table is created, compare the AI-generated growth rates with what you would expect by looking at the raw numbers. Notice whether the AI included any formatting, labels, or additional insights you did not explicitly request.

Task 4: Identify Trends and Anomalies with AI

Ask the AI assistant to analyse your sales and expense data and identify any trends, patterns, or unusual values (anomalies). Instruct it to point out months where performance was significantly different from the average.

Your goal is to observe what the AI highlights and how it explains the variance. Compare its findings with your own visual scan of the data and see if the AI caught something you missed or described it differently than you would have.

Task 5: Build a Complete MIS Report

Now create a full MIS report in Excel that your manager can present to the leadership team. The report should include:

• A visual dashboard with at least two charts showing sales trends and profit margins
• The summary table you created earlier
• The three-month sales forecast
• A short written insight section summarising performance and recommendations

Use the AI assistant wherever you find it helpful-whether for creating charts, writing summaries, formatting the report, or generating insights. Your goal is to deliver a polished, professional report ready for decision-makers.

Task 6: Reflect on Your Process

Write 4 to 5 sentences reflecting on how your use of the AI assistant changed from Task 1 to Task 5. Think about how specific or open-ended your requests were at the start versus the end, whether you became more confident in asking follow-up questions, and how you decided when to rely on AI versus your own judgment.

What You Practiced

• You used an AI assistant in Excel to generate a financial forecast based on historical data without manually building formulas.
• You asked the AI to explain its methodology and learned how it approached the forecasting task.
• You created a structured summary table with key financial metrics and compared the AI output with your own understanding of the data.
• You identified trends and anomalies by prompting the AI to analyse patterns and highlight unusual data points.
• You built a complete MIS report combining charts, tables, forecasts, and written insights using AI support throughout the process.
• You reflected on how your interaction with AI tools evolved as you moved from simple tasks to complex, real-world deliverables.