Td$1$: Forecasting and Budget Estimation in a Risky Environment

AERO is an industrial subcontractor that manufactures various families of components for the aerospace sector.

For the past four years, this company has been producing a new product, the ZULTRA component. Despite a strong start during this period, the commercial and industrial directors are questioning the growth potential of this activity and the strategic choices available to them. Despite numerous contacts with clients, they struggle to accurately assess the market potential.

Part $1$: Forecasting

You will need to prepare several forecast documents for the upcoming fifth year. The first step is to establish sales volume forecasts for the coming year.

For this, you have quarterly sales volume statistics $($number of units sold$)$ for the past four years, as well as marketing expenses $($in K$)$ allocated to support sales against competition. The company follows a management rule where marketing efforts are predefined and represent approximately $5\%$ of estimated revenue $($based on an average selling price of $2,000$ per unit sold$).$ On average, marketing expenses are distributed as follows:

$20\%$ per quarter for the first three quarters $40\%$ in the last quarter

Ann$$es $($years$)$ Trimestres $($quarters$)$ D$$penses marketing en K$($euros$)($cost$)$ Nb d'unit$$s vendues $($sales N$)$ Y$1$ trimestre $128300$ trimestre $228378$ trimestre $328354$ trimestre $456750$ Y$2$ trimestre $150450$ trimestre $250418$ trimestre $350460$ trimestre $41001100$ Y$3$ trimestre $142556$ trimestre $242426$ trimestre $342394$ trimestre $484850$ Y$4$ trimestre $160438$ trimestre $260224$ trimestre $360500$ trimestre $4120900$

To analyze the situation, make a diagnosis, and establish a forecast, you must perform the following calculations:

$1$ Sales show high variability. First, calculate the seasonal coefficient $(\%)$ for each year,

indicating the share of each quarter in the total annual sales.

$2$ Deseasonalize sales by calculating a simple moving average over the entire period Year

$1$ to Year $4.$

$3$ Plot on the same graph both the actual quarterly sales and the moving average curve.

Analyze and comment on the graph.

In Year $5,$ the company plans to allocate a budget of $400$K$,$ distributed according to the

usual rule:

$680$K per quarter for the first three quarters

$160$K in the last quarter

$4$ Perform a linear regression between quarterly marketing expenses and quarterly

sales over the past four yearsand determine the trendline equation. Evaluate the reliability

of this adjustment by calculating the linear correlation coefficient between the two series.

This will allow you to establish a forecast for Year $5,$ taking into account seasonal variations

in sales $($using the most recent data, as they are considered more reliable$).$

$5$ Calculate the index evolution $($Base $100,$ Q$1$ Year $1)$ for:

Marketing budget evolution

Sales evolution

Trendlineevolution

$1$ Plot all three indices on the same graph.

$7$ Provide an analysis of the results and recommendations for the company.

Final Objective:

Use the data analysis to provide insights on market trends and propose the best strategic

decisions for the company's future growth.

Part $2$: Building and Estimating Budget Scenarios

The commercial and industrial departments of the company have decided to study two

distinct scenarios for forecasting operating results, break$-$even estimates, and cash flow

projections.

two scenarios $($for simplification, all calculations will be considered excluding taxes$).$

Available Data:

Quarterly sales will be analyzed using seasonal coefficients $($as a percentage of

annual sales forecasts$)$:

Q$1$: $20\%$ of annual sales

Q$2$: $20\%$ of aquual sales

Q$3$: $20\%$ of annual sales

Q$4$: $40\%$ of annual sales

The unit selling price of the ZULTRA component is $2,000($excluding taxes$).$

The estimated cost of raw materials and energy is $340$ per unit produced.

This cost is expected to increase by $40\%$ at the beginning of Q$3.$

The company faces a technical bottleneck:

If production exceeds $400$ units per quarter, the company will rely

on subcontracting for additional production.

The subcontractor charges $2,000$ per unit delivered.

In Q$3,$ this subcontracting cost will increase to $2,200$ per unit due to rising

costs.

The subcontractor's reliability may become an issue in the long term.

Marketing expenses are fixed and estimated at the beginning of the year based on

forecasts.

Personnel costs are fixed and paid at the end of each month, amounting

to $300,000$ per quarter.

Capital expenditures $($CAPEX$)$ amount to G$400,000$ per quarter, mainly industrial

asset depreciation.

These are non$-$cash expenses and are considered in profit calculations.

All other operating expenses are variable costs.

Payment terms with clients and suppliers:

$40\%$ of revenues are collected in the same quarter as the sale, while

$60\%$ of revenues are collected in the following quarter.

For purchases, the situation is reversed:

$60\%$ of purchases are paid in the current quarter

$40\%$ in the following quarter.

The company's cash flow position at the beginning of Year N$5$ is positive, with an

opening balance of