Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

Unlock your academic potential

© 2024 | Zucol Services PVT LTD | All rights reserved.

Added on 2019/10/09

|11

|1983

|217

Report

AI Summary

The provided assignment content is an analysis of a company's profit and loss statement over four years. The company sells calendars with a variable cost that remains constant over the period. The task is to analyze how changes in selling prices and sales volume affect the company's net profit after tax, assuming a 20% tax rate. Additionally, a Monte Carlo simulation model was developed using Excel to estimate the probable demand for calendars over four years. The analysis shows that increasing selling prices and sales volume leads to higher profits, but also highlights the impact of inflation on variable costs and net profit.

Your contribution can guide someone’s learning journey. Share your
documents today.

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

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

Need help grading? Try our AI Grader for instant feedback on your assignments.

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

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

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

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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

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

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

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

Need help grading? Try our AI Grader for instant feedback on your assignments.

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

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

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

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

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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

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

1 out of 11