Assignment CB203 Cost / Volume / Profit Analysis Assignment Instructions For this assignment you will pick a local business that is close to you or on your route of travel. You can choose any company, but it should be different from anyone else that you know from class. Take a photo of the outside of the business and include it in your final submission. The Assignment You will attempt to estimate, using reasonable assumptions, what a simplified contribution margin format income statement would look like for the business you choose. For example, Step 1: Estimate how many customers per hour or day they have and guess what the average transaction size might be. It doesn't matter if you are correct, just be reasonable. Use these estimates to forecast annual revenue and annual customer or transaction counts. Step 2: Imagine their expenses and classify them into variable and fixed. List three or four of each. For example, a car repair shop might have the following costs: . Variable: 4 mechanics at $30 per hour including benefits Parts cost at 20% of revenue Utilities to run the power tools $3 per transaction . Fixed: . Employee to answer phone, and deal with customers $40,000 per year including benefits Advertising expense on Google $2,000 per month Monthly rent on the building, $5,000 per month Business taxes paid to city $12,000 per year Step 3: Use this information to build a contribution margin format income statement in Excel that is based on formulas so that you can make changes to volume or margins. Step 4: Adjust your estimates to so profit looks reasonable. If your numbers show them losing a million dollars on one location, you might want to adjust some of your assumptions. Step 5: Using the numbers, calculate the break-even sales $ and units values. Step 6: Design a "what if" scenario (eg. Increase marketing by $1,000 per month and get 10 new transactions per month) and use a copy your contribution margin income statement to estimate the change in profit. Based on the result, state your recommendation on implementing the change. Marks: . Compiling the income statement with correct format and calculations (5 marks) Reasonableness of assumptions used (1 mark) Correctness of the break-even calculations, given the assumptions. (1 mark) Execution of the scenario and recommendation correctly (2 marks) Overall appearance, clarity, organization, spelling, and grammar (1 mark) . . Assignment Cost / Volume / Profit Analysis Assignment Instructions CB203 Submission: Submit your photograph and excel file for grading. Make sure each part of your file is well-labelled and your recommendation is displayed prominently. Late assignments will not be accepted, so don't wait until the last day to submit your work. You never know if something will come up preventing you from submitting so early submission ensures your grade. I M4 A B C D Input 1 Details 2 Student Name 3 Student ID # 4 Input 5 Details 6 Selected Business Name 7 Description of Business Activity 8 9 Display Business Picture Below: 10 11 12 13 14 4 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Calibri A = ce Ee Copy 60 L00 .00 .0 Paste - A $ % ilil lila Merge & Center B I U Format Painter Number 2 Font Alignment L2 clipboard C7 x fx A B D E F G H 1 Step 1 - Revenue 2 3 Estimate how many customers per hour or day they have and guess what the average transaction size might be. It doesn't matter if you are corr 4. just be reasonable. Use these estimates to forecast annual revenue and annual customer or transaction counts. 5 6 Description Input Stated Assumptions 7 # of Customers 8 Average Transaction 9 # of Annual transactions 10 Annual Estimated Revenue 11 12 13 14 E General Cut Wrap Text Calibri 11 A A E Copy 60 .00 $ % CE Style Conditional Format as Formatting Table .0 Merge & Center Paste A BIU Format Painter Number Styles Alignment Font Clipboard E13 v: X V fx G H F E A B D Step 2 - Variable/Fixed Expenses 1 2 Imagine your business' expenses and classify them into variable and fixed. List three or four of each. Amount Stated Assumptions Variable Expense Description Amount Stated Assumptions Fixed Expense Description 10 11 Nooooooow 12 13 14 15 16 17 18 19 20 21 Step 3 - Contribution Margin Income Statement A1 A WN A BIC DE F G H I J K L 1 Step 3 - Contribution Margin Income Statement 2 3 Build a contribution margin format income statement in Excel that is based on formulas so that you can make changes to volume or margins. 5 6 7 Income Statement For the Year Ended December 2021 8 o od 9 Total Per Unit 10 11 XXXXXXXXXX 12 13 Less: XXXXXX 14 15 16 17 18 XXXXXXXXXX 19 20 Less: XXXXXX 21 22 23 24 25 XXXXXXXXXX 26 27 28 Cover Page Step 1 Step 2 Stephs Step 4 Step 5 Step 6 Grading Rubric Xf F8 E FRI F G A D Step 4 - Review Sense Check 1 2 3 Adjust your estimates to so profit looks reasonable. If your numbers show them losing a 4 million dollars on one location, you might want to adjust some of your assumptions. 5 6 Does your profit look reasonable? 7 Yes 8 No 9 10 If your business' profit does not look reasonable, go back to Step 3 and adjust it accordingly. 11 12 13 14 15 16 17 1 18 19 1 E D F G A B Step 5 - Break-Even Calculations 2 3 Using your company's financials, calculate the break-even sales $ and units values. Clearly label which formulas are 4 being used for the calculations. In the Amount cells, show your work. 5 6 Formula Used Amount ($) Description Break-Even Sales 7 CO Formula Used Amount (units) 9 Description 10 Break-Even Sales 11 12 13 14 15 16 17 18. 19 20 21 22 23 24 E Copy +0 Merge & Center bol A Paste Table Styles B I U Formatting Format Painter Number Styles Font Alignment Clipboard N8 VX fx G 1 J H K E F D B. Step 6 - "What if" Scenario 2 3 u N Design a "what if" scenario (eg. Increase marketing by $1,000 per month and get 10 new transactions per month) and use a copy your contribution margin income statement to estimate the change in profit. Based on the result, state your recommendation on implementing the change. 4 6 7 Income Statement For the Year Ended December 2021 9 Step 3 Scenario Total Per Unit Step 6 Scenario Per Unit Difference Total Per Unit 10 Total 0 11 Revenues 12 13 Less: Variable Expenses 14 15 16 17 18 Contribution Margin 19 20 Less: Fixed Expenses 21 22 23 24 25 Net Income 26 27 28 Recommendation: Below, provide support for your recommendation, substantiated by your analysis. Cover Page Step 1 Step 2 Step 3 Step 4 Step 5 Grading Rubric Step 6 Assignment CB203 Cost / Volume / Profit Analysis Assignment Instructions For this assignment you will pick a local business that is close to you or on your route of travel. You can choose any company, but it should be different from anyone else that you know from class. Take a photo of the outside of the business and include it in your final submission. The Assignment You will attempt to estimate, using reasonable assumptions, what a simplified contribution margin format income statement would look like for the business you choose. For example, Step 1: Estimate how many customers per hour or day they have and guess what the average transaction size might be. It doesn't matter if you are correct, just be reasonable. Use these estimates to forecast annual revenue and annual customer or transaction counts. Step 2: Imagine their expenses and classify them into variable and fixed. List three or four of each. For example, a car repair shop might have the following costs: . Variable: 4 mechanics at $30 per hour including benefits Parts cost at 20% of revenue Utilities to run the power tools $3 per transaction . Fixed: . Employee to answer phone, and deal with customers $40,000 per year including benefits Advertising expense on Google $2,000 per month Monthly rent on the building, $5,000 per month Business taxes paid to city $12,000 per year Step 3: Use this information to build a contribution margin format income statement in Excel that is based on formulas so that you can make changes to volume or margins. Step 4: Adjust your estimates to so profit looks reasonable. If your numbers show them losing a million dollars on one location, you might want to adjust some of your assumptions. Step 5: Using the numbers, calculate the break-even sales $ and units values. Step 6: Design a "what if" scenario (eg. Increase marketing by $1,000 per month and get 10 new transactions per month) and use a copy your contribution margin income statement to estimate the change in profit. Based on the result, state your recommendation on implementing the change. Marks: . Compiling the income statement with correct format and calculations (5 marks) Reasonableness of assumptions used (1 mark) Correctness of the break-even calculations, given the assumptions. (1 mark) Execution of the scenario and recommendation correctly (2 marks) Overall appearance, clarity, organization, spelling, and grammar (1 mark) . . Assignment Cost / Volume / Profit Analysis Assignment Instructions CB203 Submission: Submit your photograph and excel file for grading. Make sure each part of your file is well-labelled and your recommendation is displayed prominently. Late assignments will not be accepted, so don't wait until the last day to submit your work. You never know if something will come up preventing you from submitting so early submission ensures your grade. I M4 A B C D Input 1 Details 2 Student Name 3 Student ID # 4 Input 5 Details 6 Selected Business Name 7 Description of Business Activity 8 9 Display Business Picture Below: 10 11 12 13 14 4 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Calibri A = ce Ee Copy 60 L00 .00 .0 Paste - A $ % ilil lila Merge & Center B I U Format Painter Number 2 Font Alignment L2 clipboard C7 x fx A B D E F G H 1 Step 1 - Revenue 2 3 Estimate how many customers per hour or day they have and guess what the average transaction size might be. It doesn't matter if you are corr 4. just be reasonable. Use these estimates to forecast annual revenue and annual customer or transaction counts. 5 6 Description Input Stated Assumptions 7 # of Customers 8 Average Transaction 9 # of Annual transactions 10 Annual Estimated Revenue 11 12 13 14 E General Cut Wrap Text Calibri 11 A A E Copy 60 .00 $ % CE Style Conditional Format as Formatting Table .0 Merge & Center Paste A BIU Format Painter Number Styles Alignment Font Clipboard E13 v: X V fx G H F E A B D Step 2 - Variable/Fixed Expenses 1 2 Imagine your business' expenses and classify them into variable and fixed. List three or four of each. Amount Stated Assumptions Variable Expense Description Amount Stated Assumptions Fixed Expense Description 10 11 Nooooooow 12 13 14 15 16 17 18 19 20 21 Step 3 - Contribution Margin Income Statement A1 A WN A BIC DE F G H I J K L 1 Step 3 - Contribution Margin Income Statement 2 3 Build a contribution margin format income statement in Excel that is based on formulas so that you can make changes to volume or margins. 5 6 7 Income Statement For the Year Ended December 2021 8 o od 9 Total Per Unit 10 11 XXXXXXXXXX 12 13 Less: XXXXXX 14 15 16 17 18 XXXXXXXXXX 19 20 Less: XXXXXX 21 22 23 24 25 XXXXXXXXXX 26 27 28 Cover Page Step 1 Step 2 Stephs Step 4 Step 5 Step 6 Grading Rubric Xf F8 E FRI F G A D Step 4 - Review Sense Check 1 2 3 Adjust your estimates to so profit looks reasonable. If your numbers show them losing a 4 million dollars on one location, you might want to adjust some of your assumptions. 5 6 Does your profit look reasonable? 7 Yes 8 No 9 10 If your business' profit does not look reasonable, go back to Step 3 and adjust it accordingly. 11 12 13 14 15 16 17 1 18 19 1 E D F G A B Step 5 - Break-Even Calculations 2 3 Using your company's financials, calculate the break-even sales $ and units values. Clearly label which formulas are 4 being used for the calculations. In the Amount cells, show your work. 5 6 Formula Used Amount ($) Description Break-Even Sales 7 CO Formula Used Amount (units) 9 Description 10 Break-Even Sales 11 12 13 14 15 16 17 18. 19 20 21 22 23 24 E Copy +0 Merge & Center bol A Paste Table Styles B I U Formatting Format Painter Number Styles Font Alignment Clipboard N8 VX fx G 1 J H K E F D B. Step 6 - "What if" Scenario 2 3 u N Design a "what if" scenario (eg. Increase marketing by $1,000 per month and get 10 new transactions per month) and use a copy your contribution margin income statement to estimate the change in profit. Based on the result, state your recommendation on implementing the change. 4 6 7 Income Statement For the Year Ended December 2021 9 Step 3 Scenario Total Per Unit Step 6 Scenario Per Unit Difference Total Per Unit 10 Total 0 11 Revenues 12 13 Less: Variable Expenses 14 15 16 17 18 Contribution Margin 19 20 Less: Fixed Expenses 21 22 23 24 25 Net Income 26 27 28 Recommendation: Below, provide support for your recommendation, substantiated by your analysis. Cover Page Step 1 Step 2 Step 3 Step 4 Step 5 Grading Rubric Step 6