please provide excel calculations

Sales Revenues, Costs and Pricing The company's sales revenue is approximately 6.5Million per annum and it employs 90 people. About 60 per cent of the firm's employees work in the Northamptonshire factory with the remainder engaged at the retail outlets in London and New York. Until 2005, when it recorded a loss of 400k, the company's turnover had been hovering around the break - even point for a number of years. John Philips felt strongly that part of the reason for the lack of profitability lay with the establishment of the new retailing outlet in London. In anticipation of a substantial increase in sales revenue, the decision had been taken to invest in a high profile location in London incurring additional start - up and staffing costs, including the costs of product training carried out at the factory in Northampton. However, the expected sales revenues from the London outlet did not materialise, leaving the company with unrecovered costs and surplus manufacturing capacity. The medium term strategic objective of the French parent company was to develop the brand and increase distribution worldwide. However, the losses in 2005 had focussed the management's attention on the firm's cost structure and the resulting pricing formula that had been strictly applied across the product range for many years. Historically, pricing policy involved a mark - up to full manufacturing cost in order to arrive at a wholesale price containing a 40 per cent profit margin (Exhibit 1). This mark - up price was the price charged to retailers who then added a 100 per cent mark - up to give a 50 per cent profit margin in the price charged to consumers. The wholesale price is the price charged to Oliver's own retail outlets and to other retailers within the French group of companies. Thus, the price to the consumer includes a very high gross profit margin in relation to the actual production costs. Exhibit 2: A representative product cost profile 55 40 18 113 75 188 Materials Direct Labour Factory Overhead Total Manufacturing Cost Wholesale Margin at 40% Wholesale Price Retailers mark - up 100% (giving the retailer a 50% profit on the final selling price ex VAT) Retail Price ex VAT VAT at 17.5% Final Selling Price 188 376 66 442 Exhibit 3: Budgeted Costs and Revenues (2006) EM Direct Materials Cost 1.1 Direct Labour Cost 0.8 Factory Overhead 0.5 Total Manufacturing Cost 2.4 Despatch Cost 0.1 Cost of Sales 2.5 % of Total Cost 16.92 12.31 7.69 36.92 1.54 38.46 Fixed Administration and Cost's of Retail Outlets 3 46.15 Variable selling costs (commissions, royalties, delivery and advertising 1 15.38 Total Cost per annum 6.5 100.00 Budgeted Total Revenue 6.5 Budgeted Profit/(loss) 0 The Origins of the Cost - Based Pricing Policy The firm's full cost plus pricing approach can be traced to the historical custom prevalent among many traditional businesses. According to John Philips, it is still the basis for pricing decisions in the shoe manufacturing industry in the UK. The estimated cost figure used for pricing is based on the historic average costs by labour process - for example, cutting, blocking and punching and sewing. From the production job sheets/order tickets, the numbers of pairs of each type of shoe produced is determined. The operations performed for each particular shoe is ascertained with reference to the product design specification. From these two sources the number of times a particular operation is performed (blocking, punching, cutting, sewing) is calculated and the total labour cost of a particular operation is identified from the payroll. By dividing the latter by the former, the historical average cost per operation is computed. To this cost is added the estimated materials cost (also based on historical actual cost obtained from job sheets/production order tickets) to arrive at the direct cost of a product When it comes to allocating manufacturing overheads, a units' basis is used. In this respect all indirect factory costs are estimated for the entire year ahead and this total figure is divided by the 'normal' (that is, long - run average) output volume. The 'normal' output volume is 24,000 pairs of shoes per annum. This figure divided into the total indirect factory costs gives the manufacturing overhead cost per pair of shoes. The company's current/expected volume is significantly below the 'normal' (historic long - run average) volume and consequently the cost per pair of shoes is actually understated in the firms manufacturing costs. The shortfall in actual volume in relation to expected volume is resulting in the non-recovery of manufacturing Sales Revenues, Costs and Pricing The company's sales revenue is approximately 6.5Million per annum and it employs 90 people. About 60 per cent of the firm's employees work in the Northamptonshire factory with the remainder engaged at the retail outlets in London and New York. Until 2005, when it recorded a loss of 400k, the company's turnover had been hovering around the break - even point for a number of years. John Philips felt strongly that part of the reason for the lack of profitability lay with the establishment of the new retailing outlet in London. In anticipation of a substantial increase in sales revenue, the decision had been taken to invest in a high profile location in London incurring additional start - up and staffing costs, including the costs of product training carried out at the factory in Northampton. However, the expected sales revenues from the London outlet did not materialise, leaving the company with unrecovered costs and surplus manufacturing capacity. The medium term strategic objective of the French parent company was to develop the brand and increase distribution worldwide. However, the losses in 2005 had focussed the management's attention on the firm's cost structure and the resulting pricing formula that had been strictly applied across the product range for many years. Historically, pricing policy involved a mark - up to full manufacturing cost in order to arrive at a wholesale price containing a 40 per cent profit margin (Exhibit 1). This mark - up price was the price charged to retailers who then added a 100 per cent mark - up to give a 50 per cent profit margin in the price charged to consumers. The wholesale price is the price charged to Oliver's own retail outlets and to other retailers within the French group of companies. Thus, the price to the consumer includes a very high gross profit margin in relation to the actual production costs. Exhibit 2: A representative product cost profile 55 40 18 113 75 188 Materials Direct Labour Factory Overhead Total Manufacturing Cost Wholesale Margin at 40% Wholesale Price Retailers mark - up 100% (giving the retailer a 50% profit on the final selling price ex VAT) Retail Price ex VAT VAT at 17.5% Final Selling Price 188 376 66 442 Exhibit 3: Budgeted Costs and Revenues (2006) EM Direct Materials Cost 1.1 Direct Labour Cost 0.8 Factory Overhead 0.5 Total Manufacturing Cost 2.4 Despatch Cost 0.1 Cost of Sales 2.5 % of Total Cost 16.92 12.31 7.69 36.92 1.54 38.46 Fixed Administration and Cost's of Retail Outlets 3 46.15 Variable selling costs (commissions, royalties, delivery and advertising 1 15.38 Total Cost per annum 6.5 100.00 Budgeted Total Revenue 6.5 Budgeted Profit/(loss) 0 The Origins of the Cost - Based Pricing Policy The firm's full cost plus pricing approach can be traced to the historical custom prevalent among many traditional businesses. According to John Philips, it is still the basis for pricing decisions in the shoe manufacturing industry in the UK. The estimated cost figure used for pricing is based on the historic average costs by labour process - for example, cutting, blocking and punching and sewing. From the production job sheets/order tickets, the numbers of pairs of each type of shoe produced is determined. The operations performed for each particular shoe is ascertained with reference to the product design specification. From these two sources the number of times a particular operation is performed (blocking, punching, cutting, sewing) is calculated and the total labour cost of a particular operation is identified from the payroll. By dividing the latter by the former, the historical average cost per operation is computed. To this cost is added the estimated materials cost (also based on historical actual cost obtained from job sheets/production order tickets) to arrive at the direct cost of a product When it comes to allocating manufacturing overheads, a units' basis is used. In this respect all indirect factory costs are estimated for the entire year ahead and this total figure is divided by the 'normal' (that is, long - run average) output volume. The 'normal' output volume is 24,000 pairs of shoes per annum. This figure divided into the total indirect factory costs gives the manufacturing overhead cost per pair of shoes. The company's current/expected volume is significantly below the 'normal' (historic long - run average) volume and consequently the cost per pair of shoes is actually understated in the firms manufacturing costs. The shortfall in actual volume in relation to expected volume is resulting in the non-recovery of manufacturing