[go: up one dir, main page]
Scribd
Upload
10 views20 pages

Task9 KEY

The document presents an income statement for a company for two months ending August 31, 2021, detailing revenues, variable and fixed expenses, and net income. It includes calculations for break-even points based on varying units sold and material costs, along with sensitivity analyses on revenue and expenses. Additionally, it explores the impact of changes in salary and advertising costs on profitability and outlines methods for determining the necessary sales to achieve specific net income goals.

Uploaded by

Man With Harmonica
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
10 views20 pages

Task9 KEY

The document presents an income statement for a company for two months ending August 31, 2021, detailing revenues, variable and fixed expenses, and net income. It includes calculations for break-even points based on varying units sold and material costs, along with sensitivity analyses on revenue and expenses. Additionally, it explores the impact of changes in salary and advertising costs on profitability and outlines methods for determining the necessary sales to achieve specific net income goals.

Uploaded by

Man With Harmonica
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 20

Income Statement

For the two Months Ended 31/08/2021


(in $)
Revenues
# of Units sold 4500
Price/unit $ 84
Total revenue $ 378,000.00

Variable Expenses
Cost of Material per Unit $ 50
Total Cost of Material $ 225,000.00

Fixed Expenses
Leasing $ 3000
Salaries $ 65000
Ads $ 5000
Total Fixed Expenses $ 73,000.00

Net Income $ 80,000.00

1.a Find the break-even point for the product based on number of units sold ranging
between 500 and 5000 in increments of 500.
1.b. Find more accurate approximation of break-even point in an interval of
thousand in increments of 50.
1.c. And the final step is: in increments of 10 at the same table!

2.a. Conduct a sensitivity analysis to see how Revenue and Total Expenses are
affected when number of units sold changes between 500 and 5000. Display it with
a line chart that has markers (as shown in picture)
One-Variable DataTable (1 input cell, many results cells)

1A 1B
$ 80,000.00 $ 80,000.00
500 $ -56,000.00 2000 $ -5,000.00
1000 $ -39,000.00 2050 $ -3,300.00
1500 $ -22,000.00 2100 $ -1,600.00
2000 $ -5,000.00 2150 $ 100.00
# units sold

2500 $ 12,000.00 2200 $ 1,800.00


3000 $ 29,000.00 2250 $ 3,500.00
3500 $ 46,000.00 2300 $ 5,200.00
4000 $ 63,000.00 2350 $ 6,900.00
4500 $ 80,000.00 2400 $ 8,600.00
5000 $ 97,000.00 2450 $ 10,300.00
2500 $ 12,000.00

2A
Tot. Revenue Tot. Expenses 2A) Tot. Rev. vs To
$ 378,000.00 $ 298,000.00
$450,000.00
500 $ 42,000.00 $ 98,000.00
$400,000.00
1000 $ 84,000.00 $ 123,000.00
d ranging $350,000.00
1500 $ 126,000.00 $ 148,000.00
$300,000.00
f 2000 $ 168,000.00 $ 173,000.00
# units sold

$250,000.00
2500 $ 210,000.00 $ 198,000.00
$200,000.00
3000 $ 252,000.00 $ 223,000.00
$150,000.00
are 3500 $ 294,000.00 $ 248,000.00
ay it with $100,000.00
4000 $ 336,000.00 $ 273,000.00
$50,000.00
4500 $ 378,000.00 $ 298,000.00
$-
5000 $ 420,000.00 $ 323,000.00 500 1000 1500 2000

Tot. R
1C
$ 80,000.00
2100 $ -1,600.00
2105 $ -1,430.00
2110 $ -1,260.00
2115 $ -1,090.00
2120 $ -920.00
2125 $ -750.00
2130 $ -580.00
2135 $ -410.00
2140 $ -240.00
2145 $ -70.00
2150 $ 100.00

2A) Tot. Rev. vs Tot. Exp. based on # units sold

1000 1500 2000 2500 3000 3500 4000 4500 5000

Tot. Revenue Tot. Expenses


Income Statement
For the two Months Ended 31/08/2021
(in $)
Revenues
# of Units sold 4500
Price/unit $ 84
Total revenue $ 378,000.00

Variable Expenses
Cost of Material per Unit $ 50
Total Cost of Material $ 225,000.00

Fixed Expenses
Leasing $ 3000
Salaries $ 80000
Ads $ 15000
Total Fixed Expenses $ 98,000.00

Net Income $ 55,000.00

2.b. On the same data discuss how Revenue and Total Expenses change if Salaries
increase to $80000 and Ads to $15000 for different units of products sold?

2.c. Based on sensivitiy analysis conducted in Step b, define new location of


break-even point.
One-Variable DataTable (1 input cell, many
results cells)
2C
$ 55,000.00
500 $ -81,000.00
1000 $ -64,000.00
1500 $ -47,000.00
2000 $ -30,000.00
# units sold

2500 $ -13,000.00
3000 $ 4,000.00
3500 $ 21,000.00
4000 $ 38,000.00
4500 $ 55,000.00
5000 $ 72,000.00

2B
Tot. Revenue Tot. Expenses
$ 378,000.00 $ 323,000.00
Salaries 500 $ 42,000.00 $ 123,000.00
? 1000 $ 84,000.00 $ 148,000.00
1500 $ 126,000.00 $ 173,000.00
of
2000 $ 168,000.00 $ 198,000.00
# units sold

2500 $ 210,000.00 $ 223,000.00


3000 $ 252,000.00 $ 248,000.00
3500 $ 294,000.00 $ 273,000.00
4000 $ 336,000.00 $ 298,000.00
4500 $ 378,000.00 $ 323,000.00
5000 $ 420,000.00 $ 348,000.00
Income Statement
For the two Months Ended 31/08/2021
(in $)
Revenues
# of Units sold 4500
Price/unit $ 84
Total revenue $ 378,000.00

Variable Expenses
Cost of Material per Unit $ 50
Total Cost of Material $ 225,000.00

Fixed Expenses
Leasing $ 3000
Salaries $ 65000
Ads $ 5000
Total Fixed Expenses $ 73,000.00

Net Income $ 80,000.00

1. Find how Net Income changes if Number of Units changes between 1000 and
15000 and Cost of Material per Unit is between 45 and 75 in increment of 10.

2. Define break-even points for each category of Cost of Material per Unit.

3. How many products should be sold to make a profit if Price per Unit increases
to $120 when Cost of Material per Unit is between 45 and 75 in increment of 10.
Analyze the case in an interval of 100 - 1500 and display the results with a 'Line
Chart with Markers' as shown in picture.
Two-Variable DataTable (2 input cells, 1 result cell)
1&2
Cost of Material per Unit
80,000.00 $ 45.00 $ 55.00 $ 65.00 $ 75.00
1000 $ -34,000 $ -44,000 $ -54,000 $ -64,000
2000 $ 5,000 $ -15,000 $ -35,000 $ -55,000
3000 $ 44,000 $ 14,000 $ -16,000 $ -46,000
4000 $ 83,000 $ 43,000 $ 3,000 $ -37,000
5000 $ 122,000 $ 72,000 $ 22,000 $ -28,000
6000 $ 161,000 $ 101,000 $ 41,000 $ -19,000
# units sold

7000 $ 200,000 $ 130,000 $ 60,000 $ -10,000


8000 $ 239,000 $ 159,000 $ 79,000 $ -1,000
9000 $ 278,000 $ 188,000 $ 98,000 $ 8,000
10000 $ 317,000 $ 217,000 $ 117,000 $ 17,000
11000 $ 356,000 $ 246,000 $ 136,000 $ 26,000
12000 $ 395,000 $ 275,000 $ 155,000 $ 35,000
13000 $ 434,000 $ 304,000 $ 174,000 $ 44,000
14000 $ 473,000 $ 333,000 $ 193,000 $ 53,000
15000 $ 512,000 $ 362,000 $ 212,000 $ 62,000
Income Statement
For the two Months Ended 31/08/2021
(in $)
Revenues
# of Units sold 4500
Price/unit $ 120
Total revenue $ 540,000.00

Variable Expenses
Cost of Material per Unit $ 50
Total Cost of Material $ 225,000.00

Fixed Expenses
Leasing $ 3000
Salaries $ 65000
Ads $ 5000
Total Fixed Expenses $ 73,000.00

Net Income $ 242,000.00

3. How many products should be sold to make a profit if Price per Unit increases
to $120 when Cost of Material per Unit is between 45 and 75 in increment of 10.
Analyze the case in an interval of 100 - 1500 and display the results with a 'Line
Chart with Markers' as shown in picture.
Two-Variable DataTable (2 input cells, 1 result cell)
3
Cost of Material per Unit
242,000.00 $ 45.00 $ 55.00 $ 65.00 $ 75.00
100 $ -65,500 $ -66,500 $ -67,500 $ -68,500
200 $ -58,000 $ -60,000 $ -62,000 $ -64,000
300 $ -50,500 $ -53,500 $ -56,500 $ -59,500
400 $ -43,000 $ -47,000 $ -51,000 $ -55,000
500 $ -35,500 $ -40,500 $ -45,500 $ -50,500
600 $ -28,000 $ -34,000 $ -40,000 $ -46,000
# units sold

700 $ -20,500 $ -27,500 $ -34,500 $ -41,500


800 $ -13,000 $ -21,000 $ -29,000 $ -37,000
900 $ -5,500 $ -14,500 $ -23,500 $ -32,500
1000 $ 2,000 $ -8,000 $ -18,000 $ -28,000
1100 $ 9,500 $ -1,500 $ -12,500 $ -23,500
1200 $ 17,000 $ 5,000 $ -7,000 $ -19,000
1300 $ 24,500 $ 11,500 $ -1,500 $ -14,500
1400 $ 32,000 $ 18,000 $ 4,000 $ -10,000
1500 $ 39,500 $ 24,500 $ 9,500 $ -5,500

Net Income when # units sold: 100 - 1500 & Cost of material per unit
$60,000

$40,000

$20,000

$-
0 200 400 600 800 1000 1200
$-20,000

$-40,000

$-60,000

$-80,000

$45.00 $55.00 $65.00 $75.00


ost of material per unit: $45 - $75

1000 1200 1400 1600

$65.00 $75.00
1 2
Revenue Revenue
# of Units sold 1200 # of Units sold
Price/unit $ 75.833333 Price/unit $
Total revenue $ 91000 Total revenue $

Variable Expenses Variable Expenses


Cost of Material per Unit $ 30 Cost of Material per Unit $
Total Cost of Material $ 36000 Total Cost of Material $

Fixed Expenses Fixed Expenses


Leasing $ 5000 Leasing $
Salaries $ 45000 Salaries $
Ads $ 5000 Ads $
Total Fixed Expenses $ 55000 Total Fixed Expenses $

Net Income $ 0 Net Income $

1) Find the exact break-even point of the price.


2) Find the exact break-even point of the total fixed expenses.
3) Howmany items should be sold inorder to reach a net income of $50000?
3
Revenue
1200 # of Units sold 1521.7391
99 Price/unit $ 99
118800 Total revenue $ 150652

Variable Expenses
30 Cost of Material per Unit $ 30
36000 Total Cost of Material $ 45652.174

Fixed Expenses
5000 Leasing $ 5000
45000 Salaries $ 45000
5000 Ads $ 5000
82800 Total Fixed Expenses $ 55000

0 Net Income $ 50000


STM Inc.
SCENARIO MANAGER
Income Statement
As of Year End Dec. 31,2021 Name the related cells, define two different scenario s
and Prediciton ) by following the below instructions:
Sales & Revenue Total $ 303,008.00
# Units Sold 4,456 - Initial : Take a back up of the original values of Inco
Statement.
Unit Price $ 68.00
Cost of Goods Sold $ 120,000.00 - Prediction : Apply 7.6% decrease o n both salar ies, 50
decrease on travel + ticket expenses and warehouse ren
increase on transportation expenses. Moreover , include
Gross Profit for the Period $ 183,008.00 9.8% interest expense increase to above pr edic tions.

What are the effects of above changes on Net Income a


Expenses $ 79,600 Present the results with :
Export Expenses $ 200.49
- a suitable (new values of Net Income & EBT) summ
Other Selling Expenses $ 110.00
- two separated clustered bar charts (as shown below
Stores & Handling charges $ 200.00 displays the comparison of the initial and predicted Ne
Management Salaries $ 29,000.00 and EBT values.
Staff Salaries $ 11,300.00
Warehouse Rent $ 15,205.00
Telephone & Fax $ 2,142.52
Travel & Ticket $ 18,455.00
Hospitality & Entertainment $ 256.00
Depreciation Expenses $ 952.62 INITIAL
Transportation Expenses $ 218.00
Other Expenses $ 200.00
Trade License Expenses $ 1,359.89 Net Income $84,408.48
EBIT $ 103,408.48
Interest Expense $ 10,000.00
EBT $ 93,408.48
Taxes $ 9,000.00
Net Income $ 84,408.48 EBT

$75,000.00 $80,000.00 $85,000.00 $90,


two different scenario s (Initial
he below instructions:

e original values of Income

ease o n both salar ies, 5000 $


enses and warehouse rent, 400 $
enses. Moreover , include a case of
to above pr edic tions.

changes on Net Income and EBT?

Net Income & EBT) summary report


r charts (as shown below) that
initial and predicted Net Income

INITIAL

$84,408.48

$93,408.48

$85,000.00 $90,000.00 $95,000.00


Scenario Summary
Initial Prediction
Changing Cells:
UnitsSold 4,456 4,456
UnitPrice $ 68.00 $ 68.00
ExportExpenses $ 200.49 $ 200.49
OtherSellingExpenses $ 110.00 $ 110.00
S_H_Expenses $ 200.00 $ 200.00
Mng_Sal $ 29,000.00 $ 26,796.00
Stf_Sal $ 11,300.00 $ 10,441.20
Wrh_Rent $ 15,205.00 $ 10,205.00
Tel_Fax $ 2,142.52 $ 2,142.52
Trv_Tck_Exp $ 18,455.00 $ 13,455.00
Hosp_Ent $ 256.00 $ 256.00
Dep_Exp $ 952.62 $ 952.62
Trp_Exp $ 218.00 $ 618.00
Other_Exp $ 200.00 $ 200.00
Trd_Lic_Exp $ 1,359.89 $ 1,359.89
Int_Exp $ 10,000.00 $ 10,980.00
Taxes $ 9,000.00 $ 9,000.00
Result Cells:
Net_Income $ 84,408.48 $ 96,091.28
EBT $ 93,408.48 $ 105,091.28
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.

You might also like