Transcript Create table Company (Company_id number(10,0) NOT NULL

Project Prizm: Database and
Analysis of Prizm Software
Services
Table of Contents
Executive Summary of the Project
E-R Diagram
Schema Script
Implementation Documentation
Discussion of Learning and Conceptual
Difficulties Encountered during Project
Executive Summary
Our mission in SalesScope, Inc is to analyze multibillion dollar business contracts that
have been won or lost by its clients. SalesScope’s clients submit a request for proposal from
another firm and were one of many companies to bid for the project. After responding to the
proposal and then giving a number of orals, the client either won or lost the business deal. Over
time, companies start to develop trends, losing or winning a number of consecutive deals.
Our job begins when clients such as yourself hire us to investigate behind the scenes of
each contract. As an impartial third party, SalesScope interviews employees from its clients, the
company who submitted the Request for Proposal (RFP) and the competition. We use this
information to analyze strategies and communication, and to provide you with positive and
negative feedback. After only a few weeks, SalesScope’s clients receive a detailed report of
interview results, a complete analysis of the deal, and suggestions of what can be done in the
future to win or continue winning business. This process is known as win/loss analysis and
serves a very specific niche in business to business sales.
Project Statement: The purpose of Project Prizm was to research several recent deals that Prizm
Software Services was involved in and to analyze why these deals had the results that they did.
The database that we have created incorporates the information that resulted from our research
and was used as a to analyze the project. We have also created several tables and reports that
will help demonstrate the processes of our analysis.
E-R Diagram
Schema Script
Implementation Documentation
The database for Prizm Software Services
was created from five tables; company,
RFP, Client, Proposal, and Outcome. The
following five slides are a documentation
of the SQL language used to create the
tables and insert the information that was
generated during research.
Implementation Documentation
1. Company Table
Create table Company
(Company_id number(10,0) NOT NULL,
Company_name varchar (20) NOT NULL,
Industry varchar (20),
Yearly_revenue varchar (20),
constraint Company_pk Primary key(Company_id));
The following line is the information for the first company inserted into
the database. (The information for the other 17 company’s can be
found in notepad, which has a link that can be found on our final
project website.)
Insert into Company (Company_id, Company_name, Industry,
Yearly_revenue) values (1, 'EM Inc', 'Energy', '35 billion');
In Access, this is part of the form that was created for the
Company table. These are the names of all the companies
that have requested project proposals from Prizm.
Implementation Documentation
2. RFP Table
Create table RFP
(RFP_id number (10,0) NOT NULL,
Project_name varchar (50) NOT NULL,
Company_id number (10,0) NOT NULL,
Timeline varchar (25),
Weeks_to_orals varchar (20),
Business_objective varchar (100),
Number_competitors number (2,0),
value_contract varchar (25),
constraint RFP_pk primary key (RFP_id),
constraint RFP_FK foreign key (Company_id) references
Company(Company_id));
The following line is the RFP information for the first company inserted into the
database. (The information for the other 17 RFP’s can be found in notepad,
which has a link that can be found on our final project website.)
Insert into RFP(RFP_ID, Project_name, Company_ID, Timeline, Weeks_to_orals,
Business_objective,
Number_competitors, value_contract )values (122,'Global HR Systems using
SAP', 1 , '4 weeks', '1 week', 'Deploy HR SAP in US using similar tools as being
used internationaly, Data warehouse', 7, '25 million');
This is an exerp from the RFP table, it displays all of the information
about the Requests for Proposals that have been submitted to Prizm
from other companies
Implementation Documentation
3. Client Table
Create table Client
(Client_id number (10,0) NOT NULL,
Client_name varchar (20) NOT NULL,
Industry varchar (25),
constraint Client_PK primary key (Client_id));
The following line is the information for Prizm Software Inc. inserted into the
database.
Insert into client (Client_id, Client_name, Industry) values (1, 'Cust 1', 'Sales');
This is the form created from the client information. Because of
confidentiality restrictions, the client names are not included. This form
is used by SalesScope to keep track of all the different clients they are
doing win/loss reviews for.
Implementation Documentation
4. Proposal Table
Create table Proposal
(Proposal_id number (10,0) NOT NULL,
RFP_id number (10,0) NOT NULL,
Client_id number (10,0) NOT NULL,
Access_to_selection_team varchar (20),
Access_to_execs varchar (20),
Position_start_RFP number (2,0),
Position_end_RFP number (2,0),
Primary_competition varchar(25),
constraint Proposal_PK primary key (Proposal_id),
constraint Proposal_FK foreign key (RFP_id) references RFP(RFP_id),
constraint Proposal_FK2 foreign key (Client_id) references Client(Client_id));
The following line is the for the first proposal by Prizm inserted into the
database. (The information for the other 21 proposals can be found in notepad,
which has a link that can be found on our final project website.)
Insert into Proposal(Proposal_id, RFP_id, Client_id, Access_to_selection_team,
Access_to_execs, Primary_competition ) values (200, 122, 1, 'low', 'low', 'CGE and Y'
);
This is part of the Proposal form. This form shows all of the gathered
information about the proposals that SalesScope’s clients have
submitted to the requesting companies. Most importantly it shows how
clients, like Prizm, ranked at different points in the selection process, as
well as their major competitors.
Implementation Documentation
5. Outcome Table
Create table Outcome
(Outcome_ID number (10,0) NOT NULL,
Proposal_id number (10,0) NOT NULL,
outcome varchar (25),
Winner_strat varchar (20),
Winner_strat_description varchar (100),
Loser_strat varchar (20),
Loser_strat_description varchar (100),
Decision_maker_real varchar (50),
Decision_maker_official varchar (50),
Winner_percent_price_diff varchar (30),
Importance_price varchar (50),
constraint Outcome_PK primary key (Outcome_id),
constraint Outcome_FK foreign key (Proposal_id) references Proposal(Proposal_id));
The following line is the for the outcome for the first proposal inserted into the database. (The
information for the other 21 outcomes can be found in notepad, which has a link that can be
found on our final project website.)
Insert into Outcome (outcome_id, proposal_id, outcome, winner_strat, winner_strat_description,
loser_strat, loser_strat_description, decision_maker_real, decision_maker_official,
winner_percent_price_diff,importance_price) values (300, 200, 'loss', 'flanking', 'they brought an
intact team from another global energy company that implemented SAP', 'frontal', 'frontal', 'CIO',
'CIO', -5, 'not in top 3 factors');
Since Outcome is such a large table, this is an example of some of the
fields in the form. This form organizes all of the information about the
outcome of the bid, such as whether or not Prizm won the deal, what
strategies were used and also the importance of price in the final
decision
Implementation Documentation:
Queries
After creating the database and inserting
all of client information we were able run a
number of queries. The results of these
queries show us the relationships between
different proposals under a number of
different circumstances.
Loss Query and Report
This query was designed to show our client’s project name, primary
competitor, outcome, the winner’s strategy used and the loser’s
strategy used. It shows the information only in the instances that Prizm
lost the bid. The results are ordered by the names of the competitors.
This allows Prizm to see how often they lost to a particular client.
This is the result of the Losses Query in Report form. It can
be used to see if there are patterns when Prizm competes
against certain companies. They can use this in the future
to adapt their strategy depending on who they are
competing against.
Decision Makers Query and Report
This is the query designed to select all of the projects, where the
person who ACTUALLY chose which company won the bid is the same
person who was supposed to OFFICIALLY make the decision.
Decision Maker report
This is the resulting report from the previous query. The report shows
how important it is to know who is making the final decisions when a
company is bidding for a project and how it relates to the outcome.
Timeline and Outcome Query and Report
This query selects all of the projects, Prizm’s starting and ending RFP
positions, and outcomes when Prizm only had two weeks to respond to
an RFP. The results are grouped by wins and losses.
The resulting report from this query shows how a short
preparation time affected the proposing companies rank of
Prizm after the proposals were submitted.
Price Factor Query and Report
This query displays the project name, outcome , value of
contract and how the hiring company rated the importance
of price for all of the projects that Prizm won, price was an
important factor and their price was lower than the
competition.
This report shows how the price that Prizm chose was
important in the outcome of the bid. In all three cases the
price was lower than the competition and price was an
important factor to the hiring companies.
Strategy Query and Report
This SQL language query searches the data base for all for
the records where the strategy of the winning client is the
same as the strategy of the losing client. It shows the
importance of price and the price difference between Prizm
and the competing clients.
These were the only projects were both the winner and
loser used the same strategy
Access to Selection Team Query and Report
This query checks the database for all the projects where
Prizm had a great deal of access to the selection team,
whether it was through interviews or phone conversations.
Although the results of this report may appear simple, it is
important for Prizm to know how the amount of interaction
they have with the contracting companies affects the
outcome of the bid. In these cases, even though the
selection team was highly accessible, it did not help Prizm
win the bid.
Learning and Difficulties
We did most of our learning during this project through trial and error, and
eventually accomplished everything we set out to do. Even though we
started out initially with good data, understanding how it all related caused
some problems as we got started. Once we had designed a working
schema, we were able to developed a script in SQL that created and
inserted data correctly into tables. Later, we formulated queries that we
thought would produce the best reports, however, getting the query
language to select the right information took was tedious.
The only other significant difficulty we encountered was getting the computers
set up to link into oracle through access. Thankfully, we found one
computer that was configured do it, and monopolized it for a few days.
Overall, we learned a great deal from this project. It was interesting to see the
progression from starting out with raw data, determining relationships and
them using software to manipulate the data. In particular, we realized how
crucial it is for companies to be able to use databases to solve problems,
highlight trends, relate different sets of data and organize information. They
can constantly use this information to make better business decisions.