I used to work as a pricing analyst. Pricing calculations in spreadsheets are known as price models. Price models are data models i.e. calculations that take inputs, perform some mathematical operations, and then output a result. Factors such as revenue, fixed costs(overheads), variable costs, semi-variable costs, units of product, discount margins, price etc can be either inputs or outputs, depending on what you want to model. For example, if you want to know what your profit contribution will be for a given set of costs, sales volume and price, then the inputs will be costs, sales volume and price and the output will be profit contribution. Alternatively, if you know your costs, sales volume and the profit contribution you want, you can work out the price that you would have to charge to achieve this, so inputs are costs, sales volume and profit contribution with price being the output. So data models are simply sets of algebraic equations where you solve for the required variable based on the values of the other variables, which you do know.
In terms of Excel, one of the most useful tools is Goal Seek. This can be used to solve for any output you want without having to go into the model and change the algebraic structure of the model or the inputs. It is basically an equation solver. So if your price model is set up where (1) Revenue = Units Sold x Price (2) Total Costs = Units Sold x Cost Per Unit + Fixed Costs (3) Profit Contribution = Revenue - Total Costs, and you link these equations in your model, Goal Seek will model your price for you based on whatever costs, profit contribution and/or units sold that you enter into the model. You could also, for example, enter price, profit contribution, cost per unit and fixed costs as inputs into the model and use Goal Seek to output the number of units you would need to sell in order to achieve the profit that you entered.
Other Excel functions that are useful include the Scenario tool (Tools>Scenario). This allows you to easily compare the effects of different inputs on your model. These can be used with pivot table reports to display the results of more than one scenario on the same page. Pivot tables are a powerful tool to summarise and display data.
Finally, the VBA programming tool is very useful in price modelling. VBA is embedded (included) in Excel and can be used for complex modelling calculations which would be too complex to perform using in-cell formulas. You can also use it to create user defined functions, which add calculation functionality. For example, Excel aready has a function called SUM. To use it, you would just write +SUM(1,2,3) and Excel will sum whatever is in the parentheses, in this case 1,2 and 3. You could create for example a function called DISCOUNT, which you could program to discount the number in the parenthesis by a given percentage, say 20%. To use it you would then enter for example +DISCOUNT(100) in the cell and the number displayed would be 80 (100*0.8). So user defined functions can be used as shortcuts for calculations that you perform frequently.
Other info on price modelling... to do this job it helps to have a degree in business or other mathematical degree. It is also very helpful to have a background or training in IT, especially in Excel and databases. It is common for accountants to specialise in pricing, although you do not need to be a qualified accountant to do the job, just background knowledge of accounting principles.
In terms of where pricing analysts are positioned within a business, they are part of the finance function, which works closely with the marketing function of businesses to set prices for products and services. The pricing function is internally focussed in that it defines the prices at which a company would like to sell products or services whereas marketing is externally focussed in that it investigates the market for the product/service and feeds back information to finance on the effect that prices will have on the volumes of product/service that the company can sell. For example, if pricing analysts calculate that a product would be profitable if it were to sell 500,000 units at $10 per unit but marketing believes through its market research that the market would only buy the required volume of product at a price of $8, then the company's managment could decide to attempt to cut its costs, or to redevelop or reposition the product, or abandon the product. Conversely, if marketing believes that the company would sell an acceptable quantity of units at $15, then management could decide to charge a price closer to the $10 to achieve more market share or charge a price closer to $15 to increase the product's contribution to company profit. This is where things start to get complicated, as you get into concepts such as price elasticity of demand curves to predict how sales volumes and therefore profitability will change at differing price levels.
Hope this helps - if you need any other info on price modelling let me know!