Financial Analysis Report: Impact of Inflation and Sales

This report analyzes the financial performance of the Kaleidoscope and Orange Company, focusing on the 'Solar Powered Tents' and 'Rainwear' product lines. The analysis investigates the impact of inflation and changes in sales volume on the company's net profit, utilizing Microsoft Excel for data analysis and report generation. The report examines various scenarios, including the effects of rising inflation rates on variable costs and profitability, as well as the impact of increased sales volume and product price adjustments. A Monte Carlo simulation is used to assess demand for calendars, providing insights into potential best and worst-case outcomes. The report concludes with recommendations for managing finances, including planning product prices in consideration of inflation and prioritizing the removal of unfavorable simulation results. The findings highlight the importance of strategic financial planning in mitigating the negative effects of inflation and maximizing profitability.

a) Background
To celebrate the Glastonbury festival, the Kaleidoscope and Orange Company designed
the ‘Solar Powered Tents’ which are also known as the ‘Concept Tent’. These tents are
revolutionary in nature because they have the ability of absorbing the sunlight directly.
b) Purpose/Objective
The objective is to analyze the various scenarios in this report that will highlight the impact on
the profitability of the company due to changes in the rate of inflation and the change in the
sales levels.
c) Methodology
The data has been analysed in Microsoft Excel and reports have been developed.
Accordingly, the analysis has been done
d) Key Findings and Conclusions
It has been found that the net profits are highly impacted by the rate of inflation because
when the inflation rises year by yea, the cost of labor and other variable costs
increases, which cause the net profit to decline as compared to the profit that the
company would have earned if the rate of inflation would have remained stagnant.
e) Lessons Learned
The inflation has impact on the profits of the company and the net profits depend
heavily on the prices of product and the number of units sold.
f) Recommendations
It is recommended that the company should plan the prices of the product by
considering the rate of inflation so that it does not have to give up much share of its
profit and the company should prioritize the remove of bad results from the 1000
simulations
Contents
Introduction
To celebrate the Glastonbury festival, the Kaleidoscope and Orange Company designed the
‘Solar Powered Tents’ which are also known as the ‘Concept Tent’. These tents are
revolutionary in nature because they have the ability of absorbing the sunlight directly. Apart
from this, these tents have various other features like the central wireless control hub installed
in the tents that controls the energy generated and used. The tents give an amazing experience
of campaigning. Now, the company has fixed the prices of the tents as per the costs that it

estimated. But, a financial planning model needs to be developed so that the company can
determine the impact on net profits when this planning is changed in regard to the volume of
sales and the price of the product (Carino and Ziemba, 1998). In line with this, various scenarios
will be discussed in this report that will highlight the impact on the profitability of the company
due to changes in the rate of inflation and the change in the sales levels.
Then the company has introduced new product in the market which is the ‘Rainwear’ that has
venting approach and keeps the person safe from the rain as well as exertion. Since the product
is new in the market, therefore, a risk analysis has been done and a report is prepared for the
spreadsheet simulation model and accordingly the recommendations have been made for the
worst and best results obtained in a simulation of 1000 trials, the mean profit and its
corresponding risk, and the findings have been interpreted.
Methodology
Task 1
Effect on the net profit due to changes in planned sales volume and product price
Year Demand
(in units)
Selling
price(in
euros)
Fixed cost(in
euros)
Variable
costs(in
euros)
Rate of
inflation
Rate
of
Tax
Raw
materi
al
Pack
agin
g
Direct
labour
Distri
butio
n
1 2000 65 10000 8 2 5 3 - 20%
2 2200 67 10000 8 2 5 3 3% 20%
3 2420 69 10000 8 2 5 3 5% 20%
4 2662 71 10000 8 2 5 3 6% 20%
Sale
price(B1
*C1)
Total
cost(D1+
F1)
Profit(Sale
price-cost)
Tax
paid
(20%)
Net
profit(profit-
tax)

130000 10018 119982 23996.
4
95985
.6
147400 10018 137382 27476.
4
10990
5.6
166980 10018 156962 31392.
4
12556
9.6
189002 10018 178984 35796.
8
14318
7.2
Assumption:
1. Variable costs are assumed to be same
over the four years
Analysis
From the above table, it can be seen that the net profit is affected when the planned sales are
changed and there are changes in the prices of the products. In the first year, the net profit is
95985.6 euros when the 2000 units are sold at the price of 65 euros. This net profit has been
calculated after considering the taxes paid by the company and the cost incurred. After this,
when the company increases its sales by 10% every year and it also increases the prices of the
product by 2 euros every year, then the profits of the company have been continuously
increasing. This is because the company earns more when it is able to sell more at the increased
prices. But at the same time, the rate of inflation is also raising so, there will be slight decrease
in the profit but then also, the company will earn some amount of profit.
Scenario 1: Profitability of the new carving knife when inflation is predicted to be 2.5% during
the second year and 3% thereafter:
With the increase in the rate of inflation, the prices level in the market rises because more
money supply is there in the economy; the purchasing power of the people is more. So, they
buy goods even at the increased prices. But at the same time, the cost of labour and the raw
material rises. With the increase in rate of inflation from 2.5%to 3%, the variable costs of the
company will rise and the net profits will decline.

Suppose the price of carving knife is 2euros and the 20 units are sold. So selling price is 40
euros. The cost incurred by the company is 20 euros. So, profit is 30 euros. But suppose the
rate of inflation rises from 2.5%to 3%. So the price of carving knife will rise to 2.06 and selling
price will become 41.2. Also, the costs will rise to 20.6. So, the profit will become 20.6. Though
the company is still earning profit, but it is lesser than the profit that was there before the rate
of inflation increased.
Scenario 2: When the Company decides to sell 3% more units per year as well as increase the
current selling price by 10% per year, then the effect on the ‘net profit after tax’ for each year
will be as follows:
Year Demand(
in units)
Selling
price(in
euros)
Fixed cost(in
euros)
Variable costs(in
euros)
Rate of
inflation
Rate
of Tax
Raw
materi
al
Pack
aging
Direct
labour
Distributio
n
1 2000 65 10000 8 2 5 3 - 20%
2 2260 71.5 10000 8 2 5 3 3% 20%
3 2553.8 78.65 10000 8 2 5 3 5% 20%
4 2885.79 86.515 10000 8 2 5 3 6% 20%
Sale
price(B1*
C1)
Total
cost(D1+
F1)
Profit(Sale
price-cost)
Tax
paid
(20%)
Net profit(profit-tax)
130000 10018 119982 23996.4 95985.
6
161590 10018 151572 30314.4 12125
7.6
200856.4 10018 190838.4 38167.6
7
15267
0.7

249664.1 10018 239646.1 47929.2
2
19171
6.9
Assumption:
1. Variable costs are assumed to be same over the four years
Analysis
From the above table it can be analyzed that When the Company decides to sell 3% more units
per year as well as increase the current selling price by 10% per year, then the effect on the ‘net
profit after tax’ for each year will rise than before. This is because as the prices of goods will be
increased by the company along with the units sold, then automatically, the company will earn
more, even if it has to pay a tax of 20%.
Task 2
The simulation that has been considered here is based on Monte Carlo Simulation (Mooney,
1997). The spreadsheet simulation helps almost accurately estimate the probable happening of
events (Mahadevan, 1997). The development of simulation has been done using the excel
sheet. Mentioned below is the demand that has been considered for a calendar:
Deman
d
Probability
10,000 0.10
20,000 0.35
40,000 0.3
60,000 0.25
Now, the use of excel on this data will be done to simulate this demand for calendars several
times. The use of RAND function will be done by associating each probable value with probable
demand.
Deman
d
Random number assigned
10,000 Less than 0.10
20,000 Greater than or equal to 0.10, and less than 0.45

40,000 Greater than or equal to 0.45, and less than 0.75
60,000 Greater than or equal to 0.75
The simulation has been run on Excel sheet “Task 4” which shows the 1000 iterations.
Cutoffs Deman
d
0 10000
0.1 20000
0.45 40000
0.75 60000
Fraction of time
10000 0.106
20000 0.334
40000 0.301
60000 0.258
The best result obtained is 0334 with 20,000 and the worst is of 0.106 with 10000. Here, it can
be stated that the company can expect the demand of 20000.
Conclusion
The data has been analyzed in the Excel and the table above shows that company will earn
more profits when it increases its sales volume and raises the prices of its products. The net
profits are highly impacted by the rate of inflation because when the inflation rises year by yea,
the cost of labor and other variable costs increases, which cause the net profit to decline as
compared to the profit that the company would have earned if the rate of inflation would have
remained stagnant.
Recommendations
The company should do the following to improve its business situation and manage the
finances:
 The company should plan the prices of the product by considering the
rate of inflation so that it does not have to give up much share of its
profit.
 The company should prioritise the removal of bad results through the
1000 simulations.

References
Mahadevan, S. (1997). Monte carlo simulation. MECHANICAL ENGINEERING-NEW YORK AND
BASEL-MARCEL DEKKER-, 123-146.
Mooney, C. Z. (1997). Monte carlo simulation (Vol. 116). Sage Publications.
Carino, D. R., & Ziemba, W. T. (1998). Formulation of the Russell-Yasuda Kasai financial planning
model. Operations Research, 46(4), 433-449.