Only the excel sheet needs to be filled out
Cost-Based Pricing Exercise Viki's Flower Shoppe Congratulations! You have been hired as the new pricing manager for the very successful line of florists, Viki's Flower Shoppe. Viki's sells the usual assortment of flowers, plus small gifts and candy boxes. Your first task is to analyze the pricing structure for a new flower arrangement that Viki's hopes will be a best seller. The flowers cost the store $9 for each arrangement. The vase costs $7. The florists are paid $20 an hour, and they can make about 4 of arrangements per hour. Rent for the store and overhead expenses for utilities and salaries for sales people and executives total about $60000 per year. Based on market conditions, Viki's expects to sell 3000 arrangements in one year. They would like to achieve a return on sales of 12%. 1. Calculate the unit cost for each arrangement based on this projected sales figure. 2. Calculate the minimal cost-based price for each arrangement. 3. Estimate the Break-even point (in units and $) for this price Based on your analysis, Viki's decides to price the arrangement at $49.99. 4. At this price, what is the contribution margin for each arrangement? 5. Estimate the Break-even point (in units and $) for this price 6. How many dollars' worth of arrangements they must sell to realize $9000 of profit? At the end of the year, Viki's have sold only 2800 arrangements. 7. How did that affect their profits? 8. What can be said about the price elasticity of demand (% change in demand per % change in price (?) For the next year, they are planning to decrease the price to $47.99 9. How much arrangements they could expect to sell next year given the demand elasticity? 10. What profit they can expect? oo Don 5 = Home Insert Page Layout B20 fx Cost-based Pricing Exercise View Formulas Data Review F 1 Pricing Exercise Estimate Plan Year 1 Fact Year 1 Plan Year 2 Labor per unit = Labor per hr / Productivity per hr O 0 0 Sales Volume 5 Fixed Costs 6 Labor per unit 7 Materials 8 Variable costs, $ per unit 9 Unit Costs, $ 10 Total Costs 11 Mark-Up (ROS), % Cont.Margin, $ 13 Price, $ per unit 14 Revenue #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0 0 0 Unit costs (UC) = VC + FC / Sales # TC = UC * Sales # ROS = CM /Price Contribution margin (CM) = Price - UC Markup Price = UC/(1-Mark-Up %) Revenue = Price * Volume #DIV/0! #DIV/0! #DIV/0! #DIV/0! ' #DIV/0! ' #DIV/0! ' ' #DIV/0! #DIV/0! #DIV/0! ' ' #DIV/0! #DIV/0! #DIV/0! 15 Breakeven = FC/(Price - VC) 16 Breakeven, units 17 Breakeven, $ 18 Profit #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Profit = Revenue - TC = Cont.Margin * Sales 19 20 Price Elasticity of demand Elasticity = ((Price 2 - Price 1) / Price 1)/((Sales 2 - Sales 1) / Sales 1)) 21
