Loan Amount ₹ 3,000,000.
00
Payment period 10
Interest Rate 12.121%
Payment Amount ₹ 5,000,000.00
Loan Schedule Loan Balance Interest Principal Total Payment
Y1 ₹ 3,000,000.00 ₹ 363,636.36 ₹ 300,000.00 ₹ 663,636.36
Y2 ₹ 2,700,000.00 ₹ 327,272.73 ₹ 300,000.00 ₹ 627,272.73
Y3 ₹ 2,400,000.00 ₹ 290,909.09 ₹ 300,000.00 ₹ 590,909.09
Y4 ₹ 2,100,000.00 ₹ 254,545.45 ₹ 300,000.00 ₹ 554,545.45
Y5 ₹ 1,800,000.00 ₹ 218,181.82 ₹ 300,000.00 ₹ 518,181.82
Y6 ₹ 1,500,000.00 ₹ 181,818.18 ₹ 300,000.00 ₹ 481,818.18
Y7 ₹ 1,200,000.00 ₹ 145,454.55 ₹ 300,000.00 ₹ 445,454.55
Y8 ₹ 900,000.00 ₹ 109,090.91 ₹ 300,000.00 ₹ 409,090.91
Y9 ₹ 600,000.00 ₹ 72,727.27 ₹ 300,000.00 ₹ 372,727.27
Y10 ₹ 300,000.00 ₹ 36,363.64 ₹ 300,000.00 ₹ 336,363.64
₹ 5,000,000.00
Full Day Training
Cost Item Amount Amount Gross Profit
Hotel Conference Room ₹ 50,000.00
Feeding per participant ₹ 1,200.00
Training Material ₹ 3,000.00
Certificate ₹ 2,000.00
Prize for best participant ₹ 50,000.00
Analysis based on estimates Figure
Number of participants 40
Course Fee ₹ 20,000.00
Total Feeding cost ₹ 48,000.00
Conference Room cost ₹ 50,000.00
Training Materials cost ₹ 120,000.00
Certificate Costs ₹ 80,000.00
Prize Award cost ₹ 50,000.00
Total Revenue ₹ 800,000.00
Total Cost ₹ 348,000.00
TaxFee (5%) ₹ 40,000.00
Contigencies (7%) ₹ 56,000.00
Gross Profit ₹ 356,000.00
Full Day Training
Cost Item Amount Gross Profit
Hotel Conference Room ₹ 50,000.00
Feeding per participant ₹ 1,200.00
Training Material ₹ 3,000.00
Certificate ₹ 2,000.00
Prize for best participant ₹ 50,000.00
Analysis based on estimates
Number of participants 40
Course Fee ₹ 20,000.00
Total Feeding cost ₹ 48,000.00
Conference Room cost ₹ 50,000.00
Training Materials cost ₹ 120,000.00
Certificate Costs ₹ 80,000.00
Prize Award cost ₹ 50,000.00
Total Revenue ₹ 800,000.00
Total Cost ₹ 348,000.00
TaxFee (5%) ₹ 40,000.00
Contigencies (7%) ₹ 56,000.00
Gross Profit ₹ 356,000.00
1500000
Scenario manager in excel is a part of three what-if-analysis tools .
We can use it to see the impact of changing input values without
changing the actual data.
Worst case Most likely Best likely
2% 1% 6%
3% 1% 6%
5% 6% 1%
Qtr1 Qtr2 Qtr3 Qtr4 Annual
Sales 2,490,000 2,639,400 2,797,764 2,965,630 10,892,794
COGS 1,700,000 1,802,000 1,910,120 2,024,727 7,436,847
GP 790,000 837,400 887,644 940,903 3,455,947
Expenses 500,000 505,000 510,050 515,151 2,030,201
Profit 290,000 332,400 377,594 425,752 1,425,746
what-if-analysis tools .
input values without
.
Q to Q growth
6%
6%
1%
Book Store
Total Number Of Books
100
Highest Price
Lower Price
60%
70%
80%
90%
100%
% Sold For The Highest Price
80%
Number of Books UnitProfit
Total Profit 0
Book Store
Total Number Of Book% Sold For The Highest Price
100 70%
Number ofBooks
HighestPrice
Lower Price
Total Profit
50 60
60%
70%
80%
90%
100%
ighest Price
UnitProfit
70
Data tables are one of the existing parts of What-If analysis tools, which allow you to observe
of variables and to compare the outcomes stored by th
Loan Analysis
1500000 2000000
Interest Rate: 8%
Months of loan 180
Loan Amount: 3,000,000.00
Payments Due: 0
Monthly Payment:
Total Payment:
Total Interest:
hich allow you to observe your result by experimenting it with different values
the outcomes stored by the data table.
Loan Amount
2500000 3000000 3500000 4000000 4500000 5000000
Data tables are one of the existing parts of What-If analysis tools, which allow you to observe
of variables and to compare the outcomes stored by th
Loan Analysis
Interest Rate: 8% 7.50%
Months of loan 180 7.60%
Loan Amount: 3,000,000.00 7.70%
Payments Due: 0 7.80%
Monthly Payment: 7.90%
Total Payment: 8.00%
Rate of Interest
Total Interest: 8.10%
8.20%
8.30%
8.40%
8.50%
8.60%
8.70%
8.80%
8.90%
9.00%
hich allow you to observe your result by experimenting it with different values
the outcomes stored by the data table.
Data tables are one of the existing parts of What-If analysis tools, which allow you to observe
of variables and to compare the outcomes stored by th
Loan Analysis
₹ 0.00 1500000 2000000
Interest Rate: 8% 7.50%
Months of loan 180 7.60%
Loan Amount: 3,000,000.00 7.70%
Payments Due: 0 7.80%
Monthly Payment: 7.90%
Total Payment: 8.00%
Rate of Interest
Total Interest: 8.10%
8.20%
8.30%
8.40%
8.50%
8.60%
8.70%
8.80%
8.90%
9.00%
hich allow you to observe your result by experimenting it with different values
the outcomes stored by the data table.
Loan Amount
2500000 3000000 3500000 4000000 4500000 5000000
Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — ca
objective cell — subject to constraints, or limits, on the values of other formula cells on a wo
Problem: You want to minimize the cost of shipping goods from 2 different warehouses to 4 differe
customers. Each warehouse has a limited supply and each customer has a certain demand.
Goal: Minimize the total shipping cost, not exceeding the quantity available at each warehouse, an
meeting the demand of each customer.
Cost of Shipping Per Product
Customer 1 Customer 2 Customer 3 Customer 4
WareHouse1 50 65 50 90
WareHouse2 55 70 80 75
Number of Product Shipped
Customer 1 Customer 2 Customer 3 Customer 4
WareHouse1 0 0 0 0
WareHouse2 0 0 0 0
Total Received 0 0 0 0
Ordered 35000 21000 18000 33000
Total Shipping Cost -
rmula in one cell — called the
r formula cells on a worksheet.
nt warehouses to 4 different
has a certain demand.
ble at each warehouse, and
Total Shipped Available in Warehouse
0 50000
0 60000
January Report
Average of Monthly Gross Salary Mumbai Delhi Pune
Accounting 44000 63015 96110
Business Development 71581 53302 76769
Engineering 68229 71992 46775
Human Resources 99555 38930 125835
Legal 61827 46426 64245
Marketing 123014 43641 48339
Product Management 75382 105891 70184
Research and Development 90332 76744 53212
Sales 0 72078 53234
Services 90042 87655 48094
Support 142241 67467 53214
Training 157682 44432 97266
Grand Total 1023885 771572 833276
Nagpur Chennai Banglore
55829 53278 53274
68317 64912 101739
57442 89982 111181
68349 68550 65911
75780 51528 40834
53284 69791 152671
78184 76776 81244
91280 53269 45260
116769 38790 71033
128191 61102 53292
118828 121036 54331
88315 42113 56046
1000567 791127 886815
February Report
Average of Monthly Gross Salary Mumbai Delhi Pune
Sales 48968 72078 53234
Accounting 53247 63015 96110
Legal 61827 46426 64245
Engineering 68229 71992 46775
Business Development 71581 53302 76769
Product Management 75382 105891 70184
Services 90042 87655 48094
Research and Development 90332 76744 53212
Human Resources 99555 38930 125835
Marketing 123014 43641 48339
Support 142241 67467 53214
Training 157682 44432 97266
Grand Total 1082100 771572 833276
Nagpur Chennai Kota
116769 38790 71033
55829 53278 53274
75780 51528 40834
57442 89982 111181
68317 64912 101739
78184 76776 81244
128191 61102 53292
91280 53269 45260
68349 68550 65911
53284 69791 152671
118828 121036 54331
88315 42113 56046
1000567 791127 886815
March Report
Average of Monthly Gross Salary Hydrabad Delhi Pune Nagpur
Marketing 123014 43641 48339 53284
Accounting 53247 63015 96110 55829
Engineering 68229 71992 46775 57442
Business Development 71581 53302 76769 68317
Human Resources 99555 38930 125835 68349
Legal 61827 46426 64245 75780
Product Management 75382 105891 70184 78184
Training 157682 44432 97266 88315
Research and Development 90332 76744 53212 91280
Sales 48968 72078 53234 116769
Services 90042 87655 48094 128191
Grand Total 939859 704105 780063 881739
Chennai Banglore
69791 152671
53278 53274
89982 111181
64912 101739
68550 65911
51528 40834
76776 81244
42113 56046
53269 45260
38790 71033
61102 53292
670091 832485
Qrt1 Report
Average of Monthly Gro
Mumbai Hydrabad Delhi Pune Nagpur Chennai
Sales 97936 48968 216233 159703 350307 116369
Accounting 106494 53247 189044 288330 167486 159834
Legal 123654 61827 139279 192736 227339 154583
Engineering 136458 68229 215977 140324 172325 269947
Business Development 143162 71581 159905 230306 204950 194737
Product Management 150764 75382 317673 210551 234553 230327
Services 180084 90042 262966 144283 384573 183306
Research and Develop 180663 90332 230233 159636 273841 159806
Human Resources 199110 99555 116790 377506 205046 205649
Marketing 246029 123014 130922 145018 159853 209374
Support 284483 134934 106427 237656 242072
Training 315364 157682 133295 291797 264946 126340
Grand Total 2164200 939859 2247250 2446615 2882874 2252344
Kota Banglore
71033 142067
53274 106549
40834 81668
111181 222362
101739 203477
81244 162488
53292 106583
45260 90521
65911 131823
152671 305341
54331 54331
56046 112092
886815 1719300
Ex.1 Allow any whole number between 0 to 100
Ex.2 Allow any value between 0 to 100 with decimals
Ex.3 Allow any value between 0 to 100 with input message as "Please Enter any value between 0 to 100"
Ex.4 Error Alert
Enter any whole number between 0 to 100
Ex.5 Create a list from range E28:E29
Student Attendance
Gitesh
Mahesh
Melvin
Neelesh
Kishor
Ashish
Ex.6Create a validation list so that user can only enter dates between 1st Apr 2019 and 31st Mar.2020
Ex.7 Validate cell show that the duplicate values are not allowed
Ex.8 Validate cells so that first three characters are "EMP"
Ex.9 Validate cells so that only phone numbers are allowed in the below cells
Ex.10 Validate cells so that only date below todays date is allowed in the below ce
Ex.11
Vegetables Fruits Meats
Arugula Apple Beef
Asparagus Cherry Chicken
Beet Lemon Lamb
Broccoli Orange Pork
Carrot Peach
Cauliflower Pear
Celery Raspberry
Corn Strawberry
Green Bean Kiwi
Grade table data validation
Name of Employee Grade Salary
Ghate Akash Ramesh Lieutenant 20000
Budhwani Meehir Suresh Major 20000
Beldare Apoorva Vijay Captain
Ghoman Bhagyashri Ramesh LT Colonel
Adhav Ajay Waman Colonel
Bharate Farhin Vasantha Brigadier
Godhane Debolina Ramchandra Major General
Chaudhari Neha Sunil Lieutenant
Chaudhary Nikita Sunil Major
Kumar Yash Adesh Captain
Shekhar Vaibhavi Ajay LT Colonel
Dhananjay Sukanya Ajay Colonel
Ritesh Shivani Ajit Brigadier
Garud RIitesh Ashish Major General
Deshpande Priyanka Ashok Major
s
ny value between 0 to 100"
ate is allowed in the below cells
Select Header dependant list
Fruits Chicken
Grade Minimum Maximum
Lieutenant 15600 39100
Major 17000 39100
Captain 20000 45000
LT Colonel 25000 67000
Colonel 30000 70000
Brigadier 37000 85000
Major General 38500 90000
