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.
Self Study: Display the result of the analysis at 2A with a line chart that has
markers (as shown in below picture)
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)
ranging
thousand
are
as
Salaries
of break-
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 .
Self Study: Display the result of the analysis at 3 by a 'Line Chart with Markers'
as shown in below picture.
Two-Variable DataTable (2 input cells, 1 result cell)
1 2
Revenue Revenue
# of Units sold 1200 # of Units sold
Price/unit $ 99 Price/unit $
Total revenue $ 118800 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 $ 27800 Net Income $
1) Find the exact break-even point of the price.
2) Find the exact break-even points of the fixed expenses.
3) Howmany items should be sold inorder to reach a net income of $50000?
3
Revenue
1200 # of Units sold 1200
99 Price/unit $ 99
118800 Total revenue $ 118800
Variable Expenses
30 Cost of Material per Unit $ 30
36000 Total Cost of Material $ 36000
Fixed Expenses
5000 Leasing $ 5000
45000 Salaries $ 45000
5000 Ads $ 5000
55000 Total Fixed Expenses $ 55000
27800 Net Income $ 27800
0?
STM Inc.
Income Statement SCENARIO MANAGER
As of Year End Dec. 31,2021 Name the related cells, define two different scenario s
Sales & Revenue Total $ 303,008.00 and Prediciton ) by following the below instructions:
# Units Sold 4,456 - Initial : Take a back up of the original values of Inco
Unit Price $ 68.00 Statement.
Cost of Goods Sold $ 120,000.00
- Prediction : Apply 7.6% decrease o n both salar ies, 5
decrease on travel + ticket expenses and warehouse re
Gross Profit for the Period $ 183,008.00 increase on transportation expenses. Moreover , includ
9.8% interest expense increase to above pr edic tions.
Expenses $ 79,600 What are the effects of above changes on Net Income
Present the results with :
Export Expenses $ 200.49
Other Selling Expenses $ 110.00 - a suitable (new values of Net Income & EBT) sum
Stores & Handling charges $ 200.00 - two separated clustered bar charts (as shown below
displays the comparison of the initial and predicted N
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
Transportation Expenses $ 218.00
Other Expenses $ 200.00
Trade License Expenses $ 1,359.89
EBIT $ 103,408.48
Interest Expense $ 10,000.00
EBT $ 93,408.48
Taxes $ 9,000.00
Net Income $ 84,408.48
e two different scenario s (Initial
the below instructions:
he original values of Income
rease o n both salar ies, 5000 $
penses and warehouse rent, 400 $
penses. Moreover , include a case of
e to above pr edic tions.
changes on Net Income and EBT?
Net Income & EBT) summary report
ar charts (as shown below) that
e initial and predicted Net Income
