Question about Microsoft Excel for PC

1 Answer

Formulate Sheets for Price Analysis

Can you tell me how to formulate sheets for Price analysis or can you send me an example of Prformatted sheets.

I work as a Materilas buyer and this is a fast way for me to analise costs between different supppliers but the same products .

Firstly I must imput the list value and then calculate the discount price but sometimes like today I asked a supplier to send me a price list which was in Excel but already preformated to the following.

It was a workbook which contained two worksheet.

One the first was a introduction and a cell to enter my company discount whic is 30% of list Price so enter 30% in this Cell.

Then on the second sheet was full list of Materials with List Prices less my 30% which gave Trades Prices as well.

I found this a very good method but would like to know what formula lie's behinh the two sheets to construct my work in this way.

So for example if I entered 25% instead of 30% in the first Cell my Second sheet would calculate the discount in this way.

this is helpful for a number of reasons because the Suppliers discounts change as you Purchase more and would be able to keep track of data more easily.

If you cannoit understand what I mean, I can forward you the link for the Suppplier and maybe we can go from There???

Please help.........This would make my day so much easier!!

Regards

Declan

Posted by on

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that gotĀ 5 achievements.

    Governor:

    An expert whose answer gotĀ voted for 20 times.

    Hot-Shot:

    An expert who has answered 20 questions.

  • Expert
  • 82 Answers

This achieved like this.
In sheet one there is cell where you input discount percentage say for exaple it cell B5. Now this cell needsto formatted to percentage(click location B5 and then right click and select format cell .On number tag select percentage). Also in cell C5 put 75 and it will write 75% as it formated to show percentage.
Let us look at one product and then you can apply the same principle to all products.
Go to sheet2. This will contain full price in one cell and discount price.Let us say full price is in location B10 and you want the discounted price in C10.
Now if the discount is 30 % then obiously you are paying 75% of full price.
To display discount price in sheet2 at C10, you need to enter this formula in C10
C10*sheet2!$B$5
Now as soon as your the discount changes C10 will change to reflect the discount rate.


Posted on Oct 13, 2008

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

2 Answers

What Are The Fundamental Survival Abilities I Need To Operate A Business?


The basic survival skills incorporate a working understanding of fundamental record keeping financial management personnel management market analysis break-even analysis service or product understanding tax understanding legal structures and communication skills.

Apr 15, 2015 | The Business & Productivity Software

2 Answers

How to automatically import data from tally to excel. Want to display auto imported data in sharepoint.


Import TALLY Data / Export TALLY Data
Tally allows users to Import data from other software's as well as Export data from Tally to other software's. Tally allows users to Import and Export in ASCII, SDF and XML formats. XML is now the most widely used format of exporting data in the world. Any transactions can be exported from Tally and imported in other software after suitably altering the current structures to accept Tally data structure. The reverse is also possible by writing a small TDL program to accept the data from other software's. The data that is to be imported into Tally can be in either XML or DBF format. Easy Tally data transfer or Tally data migration from Tally 4.5 & Tally 5.4 to Tally 6.3 , Tally 7.2 , etc.
ODBC COMPLIANCE ALLOWS OTHER PROGRAMS TO USE DATA FROM TALLY DIRECTLY. E.G. : MS-EXCEL :
ODBC - OPEN DATABASE CONNECTIVITY
Tally is now ODBC compliant. Hence, any program like MS-Excel, Oracle etc that is ODBC compliant can use data from Tally. Here, data connectivity is dynamic i.e. any new updation in Tally is reflected in other ODBC Compliant Software immediately. Now, with ODBC facility the user can extract data out of Tally and design his own report formats in other ODBC compliant software's.
Procedure :
Now, Load Company -> specify period -> minimize Tally -> Open a new Excel file -> select data -> select 'Get External Data' -> select New database query -> choose Data Source
Tally connects to data source and displays Tally Wizard query screen -> select the columns you would want to include in the query.
Once all the columns are selected, the user returns to excel and excel displays all the columns selected
Graphical Analysis of Tally Data
Sometimes, pictures can say better than figures. Tally also provides graphical (Bar graph) analysis of data. The user can do a graphical analysis reports like Sales register, Purchase register, Ledgers, Funds flow, Cash flow, Item registers etc. This helps the user in deeper analysis of data. In Tally, a graphical analysis can also show month wise graphs for the year in comparison with any other period. This will help the management in quickly locating the highs and lean periods in a financial year and hence be better prepared for difficult times.
Web Enabled
Tally ees 6.3 is now Web enabled. This will help the user in reducing his paper work further. Now, report in Tally can be e-mailed directly from Tally. It is not just reports, but even invoices and purchase orders can also be e-mailed directly from Tally. Hence, the user does not have to send printed invoice or purchase orders from Tally.
Procedure :
Select any report (e.g. Balance sheet) -> select Alt+M (E-mail) -> specify all details as required by Tally
Ability to Publish Reports and Documents on the Internet
Now, Tally allows users to upload reports on the web site directly from Tally. So companies that would want to publish reports on the web site can do so directly from Tally. Many companies would also like to publish their price list on their websites along with the product they deal with.
Procedure :
Select any report (Profit and Loss A/C) -> select Upload (Alt + O) -> specify all details as required by Tally.
Direct Internet Access
Tally now offers direct Internet access. A Tally user while working on Tally can directly log on to tally website get details of all the facilities offered by Tally. The user can also download the latest releases of Tally 6.3 as and when available. Tally website also offers a facility of Tally Chat. Any user can now chat with a Tally representative and get any information regarding Tally.
Procedure :
Start Tally -> now, from any screen select Web Browser (Alt+W). Tally now takes you to the Tally Web site.
Print Preview
Any user would ideally like to view format of reports / Invoices / Vouchers before printing the same. Tally now offers a facility of print preview. Now any report can be viewed before printing or before it is mailed. Once Tally shows the preview the user can either opt to print or mail the same.

Apr 15, 2010 | Microsoft Office Excel 2007

1 Answer

Can I get a free download of the Analysis Tool Pack for Excel 2007? Thanks.


Here's a link to help you with Analysis ToolPak for Excel 2007.

http://office.microsoft.com/en-us/excel/HP100215691033.aspx

Hope this helps you solve your problem.

Jul 10, 2009 | Microsoft Office Excel 2007

2 Answers

Constants/missing correlations in SPSS


If all values for a specific "dummy" variable are identical, then you should delete the variable, because it adds NO information to your analysis.

If all values for one "dummy" variable MATCH the values for another "dummy" variable, then the two variables are 100% correlated -- remove one of them from your analysis.

Apr 06, 2009 | SPSS 13.0 (30960UU)

1 Answer

Need to use the VLOOKUP formula for more then one column


You probably need to organise your pricelist sheet so that you only have 2 columns, product and price. If this doesn't fix you, please provide the syntax of your vlookup code.

Dec 10, 2008 | Microsoft Excel for PC

1 Answer

Explain brief about Goak Seek Command, Solver, Sceanerios


Goal Seek Solver and Sceanerios are a Part of What if Data Analysis Tools.
Goal Seek find the Expected Change in terms of value to a calculated Items.
For Example if you have a sales data for the year 2007 where Profit is 350000 and you want to make it 500000 for the year 2008 so what changes you will do in Product cost or Product Price this you will find with Goal Seek. It is also known as Back calculation. It has some restrictions which is overcome by Solver. But before doing this type of calculation use Sceanerios to store different Calculated values using Goal Seek and Solver. You dont have to add these values in different sheets all the values stored as a sceanerios. so You can show different Predections to the customers without moving around the Sheets.

Nov 17, 2008 | Microsoft Business & Productivity Software

2 Answers

Help I dont know mutch of excel


If I am doing price analysis and have List Prices but want to formulate the whole sheet that it will calculate 30% of prices to give me trade prices

How can I do this

Sep 02, 2008 | Microsoft Excel for PC

1 Answer

Work day


Hi Chinnu,

For this you will have to install an Add-in called Analysis Tool Pack.

Kindly follow the below instructions:

1. Open Excel Options (Menu-Excel Options)
2. Click on Add-Ins on the side pane of the Excel Options Window
3. Check to see if Analysis ToolPak is in Inactive application add-in list.
4. If is not, proceed to Step 7
5. If it is in the inactive application add in list, Select Excel Add-ins against Manage and click on "Go"
6. In the add-ins menu check Analysis ToolPak and Analysis ToolPak VBA and click OK
7. Use this formula to find the number of work days excluding Saturday & Sunday in a month

=NETWORKDAYS(start_date,end_date, [Holidays])

In your case give the start date as 1st July and end date as 10th July and Holidays as 0 or 1 (doesn't matter). The answer it will return will be 8 days.

Trust the above helped.

Jul 23, 2008 | Microsoft Office Professional 2007 Full...

1 Answer

How to formulate log function with excel


Here try this link

http://support.microsoft.com/kb/214116

Nov 12, 2007 | Microsoft Excel for PC

Not finding what you are looking for?
Microsoft Excel for PC Logo

350 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...