Impact of Inflation and Sales on Solar Tent Profitability Analysis

This report analyzes the profitability of the Kaleidoscope and Orange Company's 'Solar Powered Tents,' also known as the 'Concept Tent,' designed for the Glastonbury festival. The objective is to evaluate how changes in inflation rates and sales levels affect the company's profitability. The methodology involves analysis using Microsoft Excel. Key findings reveal that net profits are significantly impacted by inflation, as rising inflation increases labor and variable costs, thereby reducing net profits. The report explores scenarios with varying inflation rates and sales volumes, utilizing a spreadsheet simulation model based on Monte Carlo Simulation to estimate probable outcomes. The company should plan product prices considering inflation to maintain profits and prioritize removing bad results from the 1000 simulations.

Executive summary
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
1

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
2

Contents
Introduction.................................................................................................................................................4
About the company and product.............................................................................................................4
Issue........................................................................................................................................................4
Purpose...................................................................................................................................................4
Methodology...............................................................................................................................................5
Task 1.......................................................................................................................................................5
Effect on the net profit due to changes in planned sales volume and product price...........................5
Analysis................................................................................................................................................6
Scenario 1: Profitability of the new carving knife when inflation is predicted to be 2.5% during the
second year and 3% thereafter:...............................................................................................................7
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:....................................................................................................................................................7
Analysis................................................................................................................................................9
Task 2: Spread sheet Simulation Model.......................................................................................................9
Conclusion.................................................................................................................................................11
Recommendations.....................................................................................................................................11
References.................................................................................................................................................12
3

Introduction
About the company and product
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.
Issue
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).
Purpose
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.
4

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
pound)
Fixed cost(in
pound)
Variable
costs(in
pound)
Rate of
inflatio
n
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
5

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 pound when the 2000 units are sold at the price of 65 pound. 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 pound 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.
6

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 2pound and the 20 units are sold. So selling price is 40
pound. The cost incurred by the company is 20 pound. So, profit is 30 pound. 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(i
n units)
Selling
price(in
pound)
Fixed cost(in
pound)
Variable costs(in
pound)
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%
7

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
8

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: Spread sheet Simulation Model
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.
9

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.
Cutoff
s
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.
10

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.
11