90.2K

Verified Solution

Link Copied!

I am struggling with this, could you please help me?

I have provided the necessary information, I just need help with breakeven, target profit, since those are incorrect, and also the income statement at breakeven, income statement at target profit.

\begin{tabular}{|l|l|lr|} \hline MILK SHAKE SELUNG PRICE & Small & $ & 7.00 \\ \hline & Large & $ & 10.00 \\ \hline \end{tabular} MILK SHAKE SALES MIX MILK SHAKE RECIPE \begin{tabular}{|l|c|c|} \hline & Small & Large \\ \hline Whole Milk & 8.00oz & 12.00oz \\ \hline Cream & 2.00oz & 3.00oz \\ \hline Sugar & 2.00oz & 3.00oz \\ \hline Premium Vanilla Ice Cream & 0.50cup & 0.75cup \\ \hline Flavorings & 6.00oz & 9.00oz \\ \hline Flavored specialty straws & 2.50tsp & 4.00tsp \\ \hline 8 oz cups & 1 & 1 \\ \hline 12 oz cups & 1 & 1 \\ \hline \end{tabular} MILK SHAKE MATERIAL COSTS \begin{tabular}{|l|l|l|l|} \hline Whole Milk & $20.80 & per & 5 gallon container \\ \hline \end{tabular} EMPLOYEES 61 Number of Employees 62 Employee Pay DEPRECIATION INFORMATION Useful Life Salvage Value Industrial Milk Shake Maker \begin{tabular}{l|l|} Sign & $100.00 \\ \hline \end{tabular} EMPLOYEES Number of Employees Employee Pay DEPRECIATION INFORMATION Industrial Milk Shake Maker Industrial Refrigerator/freezer Countertops Tables and benches for customers: Sign \begin{tabular}{|c|r|} \hline Useful Life & Salvage Value \\ \hline 5 years & $0.00 \\ \hline 10 years & $0.00 \\ \hline 10 years & $0.00 \\ \hline 5 years & $0.00 \\ \hline 5 years & $0.00 \\ \hline \end{tabular} FUNDING INFORMATION NOTE: Do not use EXCEL Time Value of Money Functions in funding calculations. Amount of borrowing To match capital expenditures interest rate 5.00% annual Monthly interest payments with principal due in 2 years TARGET PROFIT $50,000.00 per month Capital Expenditures \begin{tabular}{|l|l|l|} \hline 1 & Funding & \multicolumn{1}{|c|}{} \\ \hline 2 & & \\ \hline 3 & Amount Borrowed & \\ \hline 4 & Monthly Payments & \\ \hline 5 & & \\ \hline \end{tabular} 6 7 Depreciation Conversion of Units Purchased to Units Used* Variable Product Costs Total Variable Costs: \begin{tabular}{|l|l|l|} \cline { 2 - 4 } & Cost per Small Shake & Cost per Large Shake \\ \hline Total & & \\ \hline \end{tabular} Information for Chart Labels Variable Cost for Large Milkshake A B Fixed Costs \begin{tabular}{lllll} \hline 1 & See discussion beginning on page 219 in your text for an example of a Multi-product Breakeven. & B & D \end{tabular} Put your well-labelled breakeven analysis below. Remember that all of the calculations must be completed in ExCEL formulas and no 1 See discussion beginning on page 219 in your text for an example of a Mult-product Breakeven which can be applied to Multi-product Target Profit. 2 Tarket Profit 3 Putyour weilabelled breakeven arialysis below. Remember that all of the cakculations must be completed in Excet fomulas and no new 4 information is entered on this worksheet; You may add more rows to this section if needed. 21 Number of Small Shakes 22 Number of tarse Shakes Income Statement at Breakeven Sales Variable Costs \begin{tabular}{|l|l|} \hline & Product Costs \\ \cline { 2 - 2 } & Selling Costs \\ \hline \end{tabular} Total Variable Costs Contribution Margin Fixed Costs Income Statement at Target Profit Sales Variable Costs \begin{tabular}{|l|l|} \hline & Product Costs \\ \cline { 2 - 2 } & Selling Costs \\ \hline \end{tabular} Total Variable Costs Contribution Margin Fixed Costs Total Fixed Costs Net Operating Income 8 NOTE: There are no entries in grayed cells Information for Chart MILK SHAKE SELLING PRICE \begin{tabular}{|l|lr|} \hline Small & $ & 7.00 \\ \hline Large & $ & 10.00 \\ \hline \end{tabular} MILK SHAKE SALES MIX MILK SHAKE RECIPE MILK SHAKE MATERIAL COSTS Whole Milk $20.80 per 5 gallon container A Capital Expenditures \begin{tabular}{|l|r|} \hline Asset & \multicolumn{1}{|c|}{ Amount } \\ \hline Industrial Milk Shake Maker & $660.00 \\ \hline Industrial Refrigerator/freezer & $720.00 \\ \hline Countertops & $1,200.00 \\ \hline Tables and benches for customers & $1,280.00 \\ \hline Sign & $100.00 \\ \hline & \\ \hline & \\ \hline Total & $3,960.00 \\ \hline \end{tabular} Funding Amount Borrowed $3,960.00 Monthly Payments $16.50 Depreciation Variable Product Costs Variable Sellir Total Variable Costs Fixed Costs \begin{tabular}{|l|r|} \cline { 2 - 3 } & \multicolumn{1}{|c|}{ Monthly Costs } \\ \hline Shack Rental & $527.00 \\ \hline Insurance & $50.00 \\ \hline Employee Pay & $1,600.00 \\ \hline Cleaning and other Supplies & $90.00 \\ \hline Depreciation & $50.00 \\ \hline Funding & $16.50 \\ \hline & \\ \hline & \\ \hline & $2,333.50 \\ \hline Total & \\ \hline \end{tabular} Put your well-labelled breakeven analysis below. Remember that all of the calculations must be completed in EXCFL formulas and no new data is entered on this worksheet; You may add more rows to this section if needed. 1 See discussion beginning on page 219 in your text for an example of a Multi-product Breakeven which can be applied to Multi-product Target Profit. Target Profit Put your well-abelied breakeven analvas below. Remember that all of the calculations must be completed in Exceil formulas and no 4 new information is entered on this worksheet; You may add more rows to this section if needed. Monthly interest payments witb principal due in 2 years Target profit 550.000.00 Results \begin{tabular}{|l|r|} \hline Number of Smali Shakes & 13872 \\ \hline Number of Large Shakes & 9248 \\ \hline \end{tabular} Income Statement at Target Profit Sales Variable Costs \begin{tabular}{|l|} \hline Product Costs \\ \hline Selling Costs \\ \hline \end{tabular} Total Variable Costs Contribution Margin Fixed Costs Total Fixed Costs Net Operating Income \begin{tabular}{|l|l|lr|} \hline MILK SHAKE SELUNG PRICE & Small & $ & 7.00 \\ \hline & Large & $ & 10.00 \\ \hline \end{tabular} MILK SHAKE SALES MIX MILK SHAKE RECIPE \begin{tabular}{|l|c|c|} \hline & Small & Large \\ \hline Whole Milk & 8.00oz & 12.00oz \\ \hline Cream & 2.00oz & 3.00oz \\ \hline Sugar & 2.00oz & 3.00oz \\ \hline Premium Vanilla Ice Cream & 0.50cup & 0.75cup \\ \hline Flavorings & 6.00oz & 9.00oz \\ \hline Flavored specialty straws & 2.50tsp & 4.00tsp \\ \hline 8 oz cups & 1 & 1 \\ \hline 12 oz cups & 1 & 1 \\ \hline \end{tabular} MILK SHAKE MATERIAL COSTS \begin{tabular}{|l|l|l|l|} \hline Whole Milk & $20.80 & per & 5 gallon container \\ \hline \end{tabular} EMPLOYEES 61 Number of Employees 62 Employee Pay DEPRECIATION INFORMATION Useful Life Salvage Value Industrial Milk Shake Maker \begin{tabular}{l|l|} Sign & $100.00 \\ \hline \end{tabular} EMPLOYEES Number of Employees Employee Pay DEPRECIATION INFORMATION Industrial Milk Shake Maker Industrial Refrigerator/freezer Countertops Tables and benches for customers: Sign \begin{tabular}{|c|r|} \hline Useful Life & Salvage Value \\ \hline 5 years & $0.00 \\ \hline 10 years & $0.00 \\ \hline 10 years & $0.00 \\ \hline 5 years & $0.00 \\ \hline 5 years & $0.00 \\ \hline \end{tabular} FUNDING INFORMATION NOTE: Do not use EXCEL Time Value of Money Functions in funding calculations. Amount of borrowing To match capital expenditures interest rate 5.00% annual Monthly interest payments with principal due in 2 years TARGET PROFIT $50,000.00 per month Capital Expenditures \begin{tabular}{|l|l|l|} \hline 1 & Funding & \multicolumn{1}{|c|}{} \\ \hline 2 & & \\ \hline 3 & Amount Borrowed & \\ \hline 4 & Monthly Payments & \\ \hline 5 & & \\ \hline \end{tabular} 6 7 Depreciation Conversion of Units Purchased to Units Used* Variable Product Costs Total Variable Costs: \begin{tabular}{|l|l|l|} \cline { 2 - 4 } & Cost per Small Shake & Cost per Large Shake \\ \hline Total & & \\ \hline \end{tabular} Information for Chart Labels Variable Cost for Large Milkshake A B Fixed Costs \begin{tabular}{lllll} \hline 1 & See discussion beginning on page 219 in your text for an example of a Multi-product Breakeven. & B & D \end{tabular} Put your well-labelled breakeven analysis below. Remember that all of the calculations must be completed in ExCEL formulas and no 1 See discussion beginning on page 219 in your text for an example of a Mult-product Breakeven which can be applied to Multi-product Target Profit. 2 Tarket Profit 3 Putyour weilabelled breakeven arialysis below. Remember that all of the cakculations must be completed in Excet fomulas and no new 4 information is entered on this worksheet; You may add more rows to this section if needed. 21 Number of Small Shakes 22 Number of tarse Shakes Income Statement at Breakeven Sales Variable Costs \begin{tabular}{|l|l|} \hline & Product Costs \\ \cline { 2 - 2 } & Selling Costs \\ \hline \end{tabular} Total Variable Costs Contribution Margin Fixed Costs Income Statement at Target Profit Sales Variable Costs \begin{tabular}{|l|l|} \hline & Product Costs \\ \cline { 2 - 2 } & Selling Costs \\ \hline \end{tabular} Total Variable Costs Contribution Margin Fixed Costs Total Fixed Costs Net Operating Income 8 NOTE: There are no entries in grayed cells Information for Chart MILK SHAKE SELLING PRICE \begin{tabular}{|l|lr|} \hline Small & $ & 7.00 \\ \hline Large & $ & 10.00 \\ \hline \end{tabular} MILK SHAKE SALES MIX MILK SHAKE RECIPE MILK SHAKE MATERIAL COSTS Whole Milk $20.80 per 5 gallon container A Capital Expenditures \begin{tabular}{|l|r|} \hline Asset & \multicolumn{1}{|c|}{ Amount } \\ \hline Industrial Milk Shake Maker & $660.00 \\ \hline Industrial Refrigerator/freezer & $720.00 \\ \hline Countertops & $1,200.00 \\ \hline Tables and benches for customers & $1,280.00 \\ \hline Sign & $100.00 \\ \hline & \\ \hline & \\ \hline Total & $3,960.00 \\ \hline \end{tabular} Funding Amount Borrowed $3,960.00 Monthly Payments $16.50 Depreciation Variable Product Costs Variable Sellir Total Variable Costs Fixed Costs \begin{tabular}{|l|r|} \cline { 2 - 3 } & \multicolumn{1}{|c|}{ Monthly Costs } \\ \hline Shack Rental & $527.00 \\ \hline Insurance & $50.00 \\ \hline Employee Pay & $1,600.00 \\ \hline Cleaning and other Supplies & $90.00 \\ \hline Depreciation & $50.00 \\ \hline Funding & $16.50 \\ \hline & \\ \hline & \\ \hline & $2,333.50 \\ \hline Total & \\ \hline \end{tabular} Put your well-labelled breakeven analysis below. Remember that all of the calculations must be completed in EXCFL formulas and no new data is entered on this worksheet; You may add more rows to this section if needed. 1 See discussion beginning on page 219 in your text for an example of a Multi-product Breakeven which can be applied to Multi-product Target Profit. Target Profit Put your well-abelied breakeven analvas below. Remember that all of the calculations must be completed in Exceil formulas and no 4 new information is entered on this worksheet; You may add more rows to this section if needed. Monthly interest payments witb principal due in 2 years Target profit 550.000.00 Results \begin{tabular}{|l|r|} \hline Number of Smali Shakes & 13872 \\ \hline Number of Large Shakes & 9248 \\ \hline \end{tabular} Income Statement at Target Profit Sales Variable Costs \begin{tabular}{|l|} \hline Product Costs \\ \hline Selling Costs \\ \hline \end{tabular} Total Variable Costs Contribution Margin Fixed Costs Total Fixed Costs Net Operating Income

Answer & Explanation Solved by verified expert

See Answer