[go: up one dir, main page]
Scribd
Upload
31 views4 pages

Sample Scenario Analysis Excel

The document outlines various financial analysis tasks including loan repayment options, business profit sensitivity, break-even analysis for a café, and university budget planning. It provides sample data and specific what-if analysis tasks such as goal seek examples, data tables, and scenario managers to evaluate different financial scenarios. The objective is to determine optimal financial strategies under varying conditions.

Uploaded by

SIR GUNZ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
31 views4 pages

Sample Scenario Analysis Excel

The document outlines various financial analysis tasks including loan repayment options, business profit sensitivity, break-even analysis for a café, and university budget planning. It provides sample data and specific what-if analysis tasks such as goal seek examples, data tables, and scenario managers to evaluate different financial scenarios. The objective is to determine optimal financial strategies under varying conditions.

Uploaded by

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

1.

Financial Planning & Loan Analysis


Objective: Determine loan repayment options under different conditions.

Sample Data:

- Loan Amount: $20,000

- Annual Interest Rate: 5% (0.05)

- Loan Term (Years): 5

- Monthly Payment (PMT):


=PMT(Interest Rate/12, Years*12, –Loan Amount)

What-If Analysis Tasks:

A. Goal Seek Example:

- Question: What loan term is needed if the borrower can only pay $300/month?

- Set Up:

- Target Cell: Monthly Payment (PMT formula)

- Target Value: $300

- Changing Cell: Loan Term (Years)

B. One-Variable Data Table (Interest Rate Impact):

Interest Rate & Monthly Payment

Start from 3.5% and increment by 0.5% to 8% interest rate


C. Scenario Manager (Best/Worst Case):

Best Case

Interest Rate: 4%
Years: 5
Monthly Payment ???

Worst Case

Interest Rate: 6%
Years: 4
Monthly Payment ???

---

2. Business Profit Sensitivity (Pricing & Cost) :


Objective: Analyze how price and cost changes affect profit.

Sample Data:

- Units Sold: 1,000

- Selling Price per Unit: $15

- Variable Cost per Unit: $7

- Fixed Costs: $5,000

- Profit Formula: =(Price - Cost) Units - Fixed Costs

What-If Analysis Tasks:

A. Two-Variable Data Table (Price vs. Cost)

B. Scenario Manager:

High Demand
Price: $20

Cost: $5

Profit: ???

Low Demand

Price: $10

Cost: $9

Profit: ???

---

3. Break-Even Analysis for a Café


Objective: Find the number of coffees needed to cover costs.

Sample Data:

- Price per Coffee: $4

- Variable Cost per Coffee: $1.50

- Fixed Costs (Rent, Staff): $2,000/month

- Break-Even Formula: =Fixed Costs / (Price - Cost)

What-If Analysis Tasks:

A. Goal Seek Example:

- Question: What price is needed to break even at 500 sales?

B. Data Table (Price Sensitivity):

Find the break-even unit numbers for different price points such as $3, $4, $5, $6
4. University Budget Planning
Objective: Adjust tuition fees based on enrollment changes.

Sample Data:

- Current Enrollment: 1,000 students

- Tuition per Student: $5,000

- Total Revenue: $5,000,000

Goal Seek Example:

- Question: If enrollment drops by 10%, what tuition increase is needed to maintain revenue?

You might also like