Page 373 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

EXERCISE 65 : Perform Goal Seek, Solver & Scenarios on

Data

Objectives

At the end of this exercise you shall be able to
• perform Goal seek on data sheet to achieve your target
• use excel solver for data optimization & complex problem solving
• use scenario for various data sheet analysis.

Requirements

Tools/Materials
• PC/Laptop with Windows OS
• MS Excel 2013 or Higher

Procedure

a Goal Seek :

Goal Seek in Excel is a feature that allows you to find the value of a particular cell that achieves a desired result
in another cell by adjusting the value of one input cell. It’s often used in financial modeling, engineering, and other
fields where you need to determine the input necessary to achieve a specific outcome.
TASK 1: Imagine you are managing the financial data for a company. The NET SALES are determined by
the formula NET SALES = GROSS SALES * (PROFIT/100), and the ANNUAL PROFIT is the sum
of the net sales from four quarters. Your goal is to set a target ANNUAL PROFIT (23, 00,000) and
use the Goal Seek function to find the required PROFIT percentage for each quarter to achieve
this target
1 Create an Excel table with the following columns:

Sales Details
Quarter Gross Sales Profit Net Sale
Q1 ₹ 2,850,000 20
Q2 ₹ 3,155,000 20
Q3 ₹ 2,940,318 20

Q4 ₹ 0 20

Annual Profit

Net Profit ₹ 2,300,000

2 Formulas
a In cell C3 (Profit (%) for Q1), enter the initial profit percentage (e.g., 20%).
b In cell D9(Net Profit), enter the target annual profit value (e.g., ₹ 2,300,000).

358