Lorenzo's Poster Shop

lorenzo, the owner of a local poster shop, comes to you for help. While his shop has been breaking even for the past two years, it has not been able to generate a profit. For him to keep the shop open, he needs to earn at least $12,000 in operating income next year.

You agree to help Lorenzo and ask him for some current information about his products' selling price and costs. You tell him you will work through some possible scenarios that might involve chamging his sales price to generate the number of units sold needed to reach his target profit.

Lorenzo shares the following information with you as you ponder different scenarios to help your client.

Selling price $7.50

cost for paper, per unit $0.70

cost for printing, per unit $1.10

cost for film, per unit $0.60

staff salaries $48,000.00

other operating costs $12,120.00

using Lorenzos data and proper excel formulas, fist plan to get an understanding of Lorenzos fincancial situation based on breakeven. then look at the following five pricing scenarios:

1) lower the selling price by 10% to increase sales volume by 5%

2) advertise on dadio and social media for a combined cost of $1,000 to increase volume by 10%

3) use a more affordable paper on which to print the posters (available for $0.60 per unit), in combination with a less-expensive film to coat the surface of the poster (available for $0.40 per unit).

4) instead of paying the sales people a fixed salary, move to a commission-based compensation plan (save $20,000 in salary; incur $1.50 per unit sold commission), which should increase sales volume by 20%.

5) advertise on radio and social media for a combined cost of $1,000 and instead of paying sales people a fixed salary, move to a commission-based compensation plan (save $20,000 in salary; incur $1.50 per unit sold commission), which could increase sales volume by 25%.

a few days later, lorenzo calls back and says he realizes he didnt take into account any tax consequences. he lets you know that he estimates his tax rate to be about 25% and asks if you could olease factor that into the various options. he is open to raising the price of his posters if you think that is a viable option.

he also lets you know that hes been approached by a potential client inquiring about a soecial order and he wants you to advise him on whether to accept the order. the client wants 500 custom posters for $7 per poster. the posters will require a different type of paper that will cost $0.10 more per poster. the order will also require lorenzo to oay an extra $1000 in employee costs.

per lorenzos request you also decide to see what effect a price increase might have on lorenzos position. you have noticed lorenzos is one of the only poster shops in the area and customers may be willing to pay more and a higher price may even signal higher quality. though there are no guarantees that the market will accept the price increase. lorenzo is looking at increasing the sales price to $9.25.

\begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|c|}{LorenzosPosterShopContributionMarginIncomeStatement} \\ \hline Sales & & [Insert formula] \\ \hline \multicolumn{3}{|l|}{ Less Variable Costs: } \\ \hline Paper & [Insert formul & \\ \hline Printing & [Insert formul & \\ \hline Film & [Insert formul & \\ \hline Total Variable Cost: & & [Insert formula] \\ \hline Contribution Margin & & [Insert formula] \\ \hline Less Fixed Costs & & [Insert formula] \\ \hline Operating Income & & [Insert formula] \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline \multicolumn{2}{|c|}{OptionAContributionMarginIncomestatement} \\ \hline [Insert text] & [Insert formula] \\ \hline [Insert text] & [Insert formula] \\ \hline [Insert text] & [Insert formula] \\ \hline [Insert text] & [insert formula] \\ \hline [Insert text] & [Insert formula] \\ \hline [insert text] & [Insert formula] \\ \hline [Insert text] & (Insert formula) \\ \hline \end{tabular} [insert text] [Insert value] [insert value] \begin{tabular}{|c|c|} \hline \multicolumn{2}{|c|}{OptionBContributionMarginIncomeStatement} \\ \hline [Insert text] & [Insert formula] \\ \hline [Insert text] & [Insert formula] \\ \hline [insert text] & [Insert formula] \\ \hline [Insert text] & [Insert formula] \\ \hline [Insert text] & [insert formula] \\ \hline (Insert text] & [Insert formula] \\ \hline [insert text] & [insert formula] \\ \hline \end{tabular} [insert text] [insert text] [insert text] [Insert value] [insert value] [insert value] New Variable Cost per unit \begin{tabular}{|c|c|c|} \hline & OptionEContributionMarginIncome & nent \\ \hline [Insert text] & & [Insert formula] \\ \hline [Insert text] & & [Insert formula] \\ \hline [Insert text] & & [insert formula] \\ \hline [Insert text] & & [Insert formula] \\ \hline [Insert text] & & [Insert formula] \\ \hline [Insert text] & & [Insert formula] \\ \hline [Insert text] & & [Insert formula] \\ \hline \end{tabular} [Inserttext][Inserttext][Inserttext][Inserttext] [Insert value] [Insert value] [Insert value] [Insert value] Look the following net income after tax figures \begin{tabular}{|c|c|c|} \hline OriginalOptionsA & Alone[Insertformula] & withSpecialOrder[Insertformula] \\ \hline B & [Insert formula] & [Insert formula] \\ \hline C & [Insert formula] & [Insert formula] \\ \hline D & [Insert formula] & [Insert formula] \\ \hlineE & [Insert formula] & [Insert formula] \\ \hline \multicolumn{3}{|l|}{ With Price increase } \\ \hline A & [Insert value] & [Insert formula] \\ \hline B & [Insert value] & [Insert formula] \\ \hline C & [Insert value] & [Insert formula] \\ \hline D & [Insert value] & [Insert formula] \\ \hline E & [Insert value] & [Insert formula] \\ \hline \end{tabular} this is for the combined special order plus scenario option chosen for Lorenzo \begin{tabular}{|c|c|c|} \hline Contrib & Shopmestatement & \\ \hline Incremental sales & [insert value] & Per unit \\ \hline Less: Incremental costs & [Insert value] & Per unit \\ \hline New contribution margin & [insert value] & Per unit \\ \hlinex Volume & [insert value] & units \\ \hline Totalnewcontributionmargin & [insert value] & \\ \hline Less: Additional fixed costs & [insert value] & \\ \hline New additional income & [insert value] & \\ \hline Income Tax (25\%) & [Insert value] & \\ \hline New net income after tax & [insert value] & \\ \hline \end{tabular} \begin{tabular}{|cc|} \hline Options & After-Tax Profit \\ A & [Insert formula] \\ B & [Insert formula] \\ C & [Insert formula] \\ D & [Insert formula] \\ E & [Insert formula] \\ \hline \end{tabular} A B C D E F H \begin{tabular}{|c|c|} \hline Contributio & tatement \\ \hline Sales & [Insert forumla] \\ \hline Less Variable Costs: & [insert formula] \\ \hline Contribution Margin & [insert formula] \\ \hline Less Fixed Costs & [tnsent formula] \\ \hline Operating income & [insert formula] \\ \hline \end{tabular} \begin{tabular}{|ll|} \hline \multicolumn{2}{|c|}{ Option B } \\ \hline \multicolumn{2}{|c|}{ Contribution Margin Income Statement } \\ \hline Sales & [Insert formula] \\ Less Variable Costs: & [insert formula] \\ Contribution Margin & [Insert formula] \\ Coss Fixed Costs & [insert formula] \\ Operating income & [Insert formula] \\ \hline \end{tabular} New Selling Price [insert value] New Fixed costs New Sales Volume Units [insert value] [insert value] [insert value]