80.2K
Verified Solution
Link Copied!
Use the Markdown Pricing Model spreadsheet model and a two-way data table to find the total revenue if days at full retail vary from 25 to 45
in increments of 5 and the intermediate markdown varies from 10% to 45% in increments of 5%.
\begin{tabular}{|c|c|c|} \hline Selling Season (days) & 50 & \\ \hline Days at full retail & 40 & \\ \hline Intermediate markdown & 30% & \\ \hline Clearance markdown & 70% & \\ \hline \multicolumn{3}{|l|}{ Sales Data } \\ \hline Price(X) & Average Daily Sales (Y) & \\ \hline$70 & 7 & \\ \hline$49 & 32.2 & \\ \hline \multicolumn{3}{|c|}{ Demand function parameters } \\ \hline Intercept & 91 & \\ \hline Slope & -1.2 & \\ \hline \multicolumn{3}{|l|}{ Model } \\ \hline \multicolumn{3}{|l|}{ Full Retail Sales } \\ \hline Retail price & $70.00 & \\ \hline Daily sales & 7 & \\ \hline Days at retail price & 40 & \\ \hline \multirow[t]{2}{*}{ Units sold at retail } & 280 & \\ \hline & Retail revenue & $19,600.00 \\ \hline \multicolumn{3}{|l|}{ Discount Sales } \\ \hline Discount & 30% & \\ \hline Discount price & $49.00 & \\ \hline Daily sales & 32.2 & \\ \hline \multirow{2}{*}{ Unit sold } & 322 & \\ \hline & Discount revenue & $15,778.00 \\ \hline \multicolumn{3}{|l|}{ Clearance Sales } \\ \hline Clearance price & 21 & \\ \hline \multirow[t]{3}{*}{ Units sold at clearance } & 398 & \\ \hline & Clearance revenue & $8,358.00 \\ \hline & Total revenue & $43,736.00 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline & A & B & C \\ \hline 1 & Markdown Pricing Model & & \\ \hline \multicolumn{4}{|c|}{\begin{tabular}{l|l} 2 \\ 2 \end{tabular}} \\ \hline 3 & Data & & \\ \hline 4 & Retail Price & 70.00 & \\ \hline 5 & Inventory & 1000 & \\ \hline 6 & Selling Season (days) & 50 & \\ \hline 7 & Days at full retail & 40 & \\ \hline 8 & Intermediate markdown & 30% & \\ \hline 9 & Clearance markdown & 70% & \\ \hline \multicolumn{4}{|l|}{10} \\ \hline 11 & Sales Data & & \\ \hline 12 & Price (X) & Average Daily Sales (Y) & \\ \hline 13 & 70 & 7.00 & \\ \hline 14 & 49 & 32.20 & \\ \hline 15 & Demand function parameters & & \\ \hline 16 & Intercept = & = INTERCEPT(B13:B14,A13:A14)| & \\ \hline 17 & Slope & = =SLOPE(B13:B14,A13:A14) & \\ \hline \\ \hline 19 & Model & & \\ \hline \multicolumn{4}{|l|}{20} \\ \hline 21 & Full Retail Sales & & \\ \hline 22 & Retail price & =B4 & \\ \hline 23 & Daily sales & =B16+B17B22 & \\ \hline 24 & Days at retail price & =B7 & \\ \hline 25 & Units sold at retail & =B23B24 & \\ \hline 26 & & Retail revenue & =B25B2 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline 27 & Discount Sales & & \\ \hline 28 & Discount & =B8 & \\ \hline 29 & Discount price & =B22(1B28) & \\ \hline 30 & Daily sales & =B16+B17B29 & \\ \hline 31 & Unit sold & =MIN(B30(B6B24),B5B25) & \\ \hline 32 & & Discount revenue & =B31B29 \\ \hline 33 & Clearance Sales & & \\ \hline 34 & Clearance price & =B4(1B9) & \\ \hline 35 & Units sold at clearance & =MAX(0,B5B25B31) & \\ \hline 36 & & Clearance revenue & =B34B35 \\ \hline 37 & & & \\ \hline 38 & & Total revenue & =C26+C32+C36 \\ \hline \end{tabular} Fill in the two-way data table below. (Round to the nearest dollar as needed.) \begin{tabular}{|c|c|c|} \hline Selling Season (days) & 50 & \\ \hline Days at full retail & 40 & \\ \hline Intermediate markdown & 30% & \\ \hline Clearance markdown & 70% & \\ \hline \multicolumn{3}{|l|}{ Sales Data } \\ \hline Price(X) & Average Daily Sales (Y) & \\ \hline$70 & 7 & \\ \hline$49 & 32.2 & \\ \hline \multicolumn{3}{|c|}{ Demand function parameters } \\ \hline Intercept & 91 & \\ \hline Slope & -1.2 & \\ \hline \multicolumn{3}{|l|}{ Model } \\ \hline \multicolumn{3}{|l|}{ Full Retail Sales } \\ \hline Retail price & $70.00 & \\ \hline Daily sales & 7 & \\ \hline Days at retail price & 40 & \\ \hline \multirow[t]{2}{*}{ Units sold at retail } & 280 & \\ \hline & Retail revenue & $19,600.00 \\ \hline \multicolumn{3}{|l|}{ Discount Sales } \\ \hline Discount & 30% & \\ \hline Discount price & $49.00 & \\ \hline Daily sales & 32.2 & \\ \hline \multirow{2}{*}{ Unit sold } & 322 & \\ \hline & Discount revenue & $15,778.00 \\ \hline \multicolumn{3}{|l|}{ Clearance Sales } \\ \hline Clearance price & 21 & \\ \hline \multirow[t]{3}{*}{ Units sold at clearance } & 398 & \\ \hline & Clearance revenue & $8,358.00 \\ \hline & Total revenue & $43,736.00 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline & A & B & C \\ \hline 1 & Markdown Pricing Model & & \\ \hline \multicolumn{4}{|c|}{\begin{tabular}{l|l} 2 \\ 2 \end{tabular}} \\ \hline 3 & Data & & \\ \hline 4 & Retail Price & 70.00 & \\ \hline 5 & Inventory & 1000 & \\ \hline 6 & Selling Season (days) & 50 & \\ \hline 7 & Days at full retail & 40 & \\ \hline 8 & Intermediate markdown & 30% & \\ \hline 9 & Clearance markdown & 70% & \\ \hline \multicolumn{4}{|l|}{10} \\ \hline 11 & Sales Data & & \\ \hline 12 & Price (X) & Average Daily Sales (Y) & \\ \hline 13 & 70 & 7.00 & \\ \hline 14 & 49 & 32.20 & \\ \hline 15 & Demand function parameters & & \\ \hline 16 & Intercept = & = INTERCEPT(B13:B14,A13:A14)| & \\ \hline 17 & Slope & = =SLOPE(B13:B14,A13:A14) & \\ \hline \\ \hline 19 & Model & & \\ \hline \multicolumn{4}{|l|}{20} \\ \hline 21 & Full Retail Sales & & \\ \hline 22 & Retail price & =B4 & \\ \hline 23 & Daily sales & =B16+B17B22 & \\ \hline 24 & Days at retail price & =B7 & \\ \hline 25 & Units sold at retail & =B23B24 & \\ \hline 26 & & Retail revenue & =B25B2 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline 27 & Discount Sales & & \\ \hline 28 & Discount & =B8 & \\ \hline 29 & Discount price & =B22(1B28) & \\ \hline 30 & Daily sales & =B16+B17B29 & \\ \hline 31 & Unit sold & =MIN(B30(B6B24),B5B25) & \\ \hline 32 & & Discount revenue & =B31B29 \\ \hline 33 & Clearance Sales & & \\ \hline 34 & Clearance price & =B4(1B9) & \\ \hline 35 & Units sold at clearance & =MAX(0,B5B25B31) & \\ \hline 36 & & Clearance revenue & =B34B35 \\ \hline 37 & & & \\ \hline 38 & & Total revenue & =C26+C32+C36 \\ \hline \end{tabular} Fill in the two-way data table below. (Round to the nearest dollar as needed.)
Answer & Explanation
Solved by verified expert
