C6 - Data Set C6-Student Template \begin{tabular}{|l|r|r|r|} \hline \multicolumn{4}{|c|}{ Sales Estimates for Specialty Candles (for Q1 - Q10) } \\ \hline & \multicolumn{1}{|c|}{ Jar } & \multicolumn{1}{|c|}{ Imprinted } & \multicolumn{1}{c|}{ Additive } \\ \hline Sales Price & 6.26 & 4.8 & 5.86 \\ \hline Est. Units Sold & 1,423,000 & 2,022,000 & 2,321,000 \\ \hline Sales Commissions & 3.0% & 2.8% & 3.5% \\ \hline \end{tabular} Josey's best guess is that marketing will choose the Jar candles; her second guess is that they will choose Imprinted candles; her third guess is that they will choose Additive candles. \begin{tabular}{|l|r|r|r|} \hline \multicolumn{3}{|c|}{ Variable Costs for Specialty Candles (for Q1 - Q10) } \\ \hline & \multicolumn{2}{|c|}{ Imprinted } & \multicolumn{2}{c|}{ Additive } \\ \hline Wax & 1.56 & 1.56 & 1.56 \\ \hline Wick & 0.06 & 0.06 & 0.06 \\ \hline Coloring & 0.12 & 0.12 & 0.12 \\ \hline Scent & 0.36 & 0.36 & 0.36 \\ \hline Clip & 0.01 & 0.01 & 0.01 \\ \hline Cellophane & 0.01 & 0.01 & 0.01 \\ \hline Information Stickers & 0.03 & 0.03 & 0.03 \\ \hline Packing Box & 0.02 & 0.02 & 0.02 \\ \hline Jars/Sand/Gems & 0.95 & 0.12 & 0.6 \\ \hline Lids/Glitter/Charms & 0.14 & 0.06 & 0.24 \\ \hline Apprentice DL & 0.15 & 0.15 & 0.15 \\ \hline Candlemaker DL & 0.26 & 0.26 & 0.26 \\ \hline Candle Master DL & 0.24 & 0.24 & 0.24 \\ \hline Variable Overhead & 0.23 & 0.16 & 0.14 \\ \hline \end{tabular} \begin{tabular}{|l|r|} \hline \multicolumn{2}{|c|}{ Allocated Fixed Costs } \\ \hline Indirect Salaries & 168,800 \\ \hline Depreciation & 313,200 \\ \hline Insurance & 17,400 \\ \hline Parts and Repairs & 21,988 \\ \hline Storage Rent & 144,600 \\ \hline Utilities & 88,704 \\ \hline Shipping & 11,490 \\ \hline \end{tabular} C6 - Data Set C6-Student Template Specialty Candle Division Flexible Budget \begin{tabular}{|l|r|r|} \hline & Candle Type \\ \hline & Est. Sales \& Production & \\ \hline & Per Unit & Total \\ \hline Sales Revenue & & \\ \hline Variable Costs & & \\ \hline Direct Materials & \\ \hline Wax & & \\ \hline Wick & & \\ \hline Coloring & & \\ \hline Scent & & \\ \hline Clip & & \\ \hline Cellophane & & \\ \hline Information Stickers & \\ \hline Packing Box & & \\ \hline Jars/Sand/Gems & \\ \hline Lids/Glitter/Charms & \\ \hline Total Direct Materials & \\ \hline Direct Labor & & \\ \hline Apprentice DL & & \\ \hline Candlemaker DL & \\ \hline Candle Master DL & \\ \hline Total Direct labor & \\ \hline \end{tabular} Variable Manufacturing Overhead \& Selling Costs Sales Commissions Variable Manufacturing Overhead Total Variable Manufacturing \& Selling Costs Total Variable Costs Total Contribution Margin Foxed Costs Indirect Salaries Depreciation Insurance Parts and Repairs Storage Rent Utilities Shipping Total Fixed Costs Contribution Margin Income Use the Excel skills learned in this chapter to answer the following questions and problems. When submitting your answers on the Armond Dalton site, please round unit amounts to the nearest penny and other amounts to the nearest dollar or whole unit. When submitting your work as an Excel file, use reasonable formatting. Creating a Flexible Budget To successfully answer these questions about the new production line and those in the next section, you must create a flexible budget for the Specialty Candle Division. As part of that budget, you will need to use V and Hlookup functions to pull the correct variable costs into the budget. Create a data validation box that allows the user to select the style of candle for the budget. Your V and Hlookups will reference this cell and pull the correct amounts into the budget. Josey's table (which is available on the Data spreadsheet for this chapter) provides you with her best guess of the style Marketing will choose to promote first and the sales volume for the next quarter. Answer the questions in this section using this data. Q-1. Using your flexible budget and Josey's best guess as to which new candle type the marketing department is most likely to choose, how much total revenue will Wedgewood earn from the new division? Q-2. How much will the company spend on wax for the new division? Q-3. How much will the company spend on Candle Master wages for the new division? Q-4. How much will the company report as the contribution margin per unit for the new division? Q-5. How much will the company spend on fixed costs for the new division? Q-6. How much will the company report as contribution margin income for the new division? Using a Flexible Budget Q-7. If marketing doubles the estimated units sold, what will be the new total contribution margin? Q-8. If marketing cuts the original estimated units sold in half instead, what will be the new contribution margin income? -9. If marketing decides to sell Josey's second guess instead of her best guess, what will be the new division's total contribution margin under this assumption? -10. If marketing decides to sell Josey's third guess instead of her best guess, what will be the new division's contribution margin income under this assumption? C6 - Data Set C6-Student Template \begin{tabular}{|l|r|r|r|} \hline \multicolumn{4}{|c|}{ Sales Estimates for Specialty Candles (for Q1 - Q10) } \\ \hline & \multicolumn{1}{|c|}{ Jar } & \multicolumn{1}{|c|}{ Imprinted } & \multicolumn{1}{c|}{ Additive } \\ \hline Sales Price & 6.26 & 4.8 & 5.86 \\ \hline Est. Units Sold & 1,423,000 & 2,022,000 & 2,321,000 \\ \hline Sales Commissions & 3.0% & 2.8% & 3.5% \\ \hline \end{tabular} Josey's best guess is that marketing will choose the Jar candles; her second guess is that they will choose Imprinted candles; her third guess is that they will choose Additive candles. \begin{tabular}{|l|r|r|r|} \hline \multicolumn{3}{|c|}{ Variable Costs for Specialty Candles (for Q1 - Q10) } \\ \hline & \multicolumn{2}{|c|}{ Imprinted } & \multicolumn{2}{c|}{ Additive } \\ \hline Wax & 1.56 & 1.56 & 1.56 \\ \hline Wick & 0.06 & 0.06 & 0.06 \\ \hline Coloring & 0.12 & 0.12 & 0.12 \\ \hline Scent & 0.36 & 0.36 & 0.36 \\ \hline Clip & 0.01 & 0.01 & 0.01 \\ \hline Cellophane & 0.01 & 0.01 & 0.01 \\ \hline Information Stickers & 0.03 & 0.03 & 0.03 \\ \hline Packing Box & 0.02 & 0.02 & 0.02 \\ \hline Jars/Sand/Gems & 0.95 & 0.12 & 0.6 \\ \hline Lids/Glitter/Charms & 0.14 & 0.06 & 0.24 \\ \hline Apprentice DL & 0.15 & 0.15 & 0.15 \\ \hline Candlemaker DL & 0.26 & 0.26 & 0.26 \\ \hline Candle Master DL & 0.24 & 0.24 & 0.24 \\ \hline Variable Overhead & 0.23 & 0.16 & 0.14 \\ \hline \end{tabular} \begin{tabular}{|l|r|} \hline \multicolumn{2}{|c|}{ Allocated Fixed Costs } \\ \hline Indirect Salaries & 168,800 \\ \hline Depreciation & 313,200 \\ \hline Insurance & 17,400 \\ \hline Parts and Repairs & 21,988 \\ \hline Storage Rent & 144,600 \\ \hline Utilities & 88,704 \\ \hline Shipping & 11,490 \\ \hline \end{tabular} C6 - Data Set C6-Student Template Specialty Candle Division Flexible Budget \begin{tabular}{|l|r|r|} \hline & Candle Type \\ \hline & Est. Sales \& Production & \\ \hline & Per Unit & Total \\ \hline Sales Revenue & & \\ \hline Variable Costs & & \\ \hline Direct Materials & \\ \hline Wax & & \\ \hline Wick & & \\ \hline Coloring & & \\ \hline Scent & & \\ \hline Clip & & \\ \hline Cellophane & & \\ \hline Information Stickers & \\ \hline Packing Box & & \\ \hline Jars/Sand/Gems & \\ \hline Lids/Glitter/Charms & \\ \hline Total Direct Materials & \\ \hline Direct Labor & & \\ \hline Apprentice DL & & \\ \hline Candlemaker DL & \\ \hline Candle Master DL & \\ \hline Total Direct labor & \\ \hline \end{tabular} Variable Manufacturing Overhead \& Selling Costs Sales Commissions Variable Manufacturing Overhead Total Variable Manufacturing \& Selling Costs Total Variable Costs Total Contribution Margin Foxed Costs Indirect Salaries Depreciation Insurance Parts and Repairs Storage Rent Utilities Shipping Total Fixed Costs Contribution Margin Income Use the Excel skills learned in this chapter to answer the following questions and problems. When submitting your answers on the Armond Dalton site, please round unit amounts to the nearest penny and other amounts to the nearest dollar or whole unit. When submitting your work as an Excel file, use reasonable formatting. Creating a Flexible Budget To successfully answer these questions about the new production line and those in the next section, you must create a flexible budget for the Specialty Candle Division. As part of that budget, you will need to use V and Hlookup functions to pull the correct variable costs into the budget. Create a data validation box that allows the user to select the style of candle for the budget. Your V and Hlookups will reference this cell and pull the correct amounts into the budget. Josey's table (which is available on the Data spreadsheet for this chapter) provides you with her best guess of the style Marketing will choose to promote first and the sales volume for the next quarter. Answer the questions in this section using this data. Q-1. Using your flexible budget and Josey's best guess as to which new candle type the marketing department is most likely to choose, how much total revenue will Wedgewood earn from the new division? Q-2. How much will the company spend on wax for the new division? Q-3. How much will the company spend on Candle Master wages for the new division? Q-4. How much will the company report as the contribution margin per unit for the new division? Q-5. How much will the company spend on fixed costs for the new division? Q-6. How much will the company report as contribution margin income for the new division? Using a Flexible Budget Q-7. If marketing doubles the estimated units sold, what will be the new total contribution margin? Q-8. If marketing cuts the original estimated units sold in half instead, what will be the new contribution margin income? -9. If marketing decides to sell Josey's second guess instead of her best guess, what will be the new division's total contribution margin under this assumption? -10. If marketing decides to sell Josey's third guess instead of her best guess, what will be the new division's contribution margin income under this assumption