Answer using Excel spreadsheet and excel formulas.

The Assignment You will attempt to estimate, using reasonable assumptions, what a simplified contribution margin format income statement would look like for one of the locations of Dino Pie Limited. You must use the excel spreadsheet template provided. What you need to do: Step 1: Dino Pie Limited has one bakery location where it sells fresh made apple pies and nothing else. Pick what the selling price for that high quality apple pie is. Fill that in on your Income Statement. Step 2. Estimate how many customers per hour or day they have and estimate how many pies they sell per month. It doesn't matter if you are correct, just be reasonable. Fill that in on your spreadsheet and use these estimates to forecast monthly revenue. Step 3: Imagine their expenses and classify them into variable and fixed. List three or four of each. Come up with your own but make them reasonable. For example, you might have the following costs: . Variable: Pie ingredients (crust and filling) Amount of time it takes each baker to make a pie Utilities to run the bakery Fixed: . Store Manager's salary per month Advertising expense per month Monthly rent on the location per month Depreciation on the equipment per month Please note that I don't expect you to include all the possible detailed costs but rather just the obvious ones and I expect your estimates to be reasonable. Add lines to your spreadsheet if necessary. Step 4: Use this information to build a contribution margin format Income Statement in your Excel spreadsheet that is based on formulas so that you can make changes to volume or margins. Step 5: Adjust your estimates so profit looks reasonable. If your numbers show them losing a hundred thousand dollars at their location, you might want to adjust some of your assumptions. Step 6: Once those numbers look reasonable, using those numbers, calculate both the break- even sales $ and units in the cells immediately below that Income Statement. Indicate how they were calculated. Use formulas in cells wherever possible. Step 7: To the right of your first CM Income Statement, you will see that you are asked to create a "what if" scenario (eg, Increase marketing by $1,000 per month and sell 20 more pies per month). Do not use my example. Come up with your own. Use your knowledge from your marketing and math courses. Fill this in at the top of this copy - tell me what the "what if" scenario is. Come up with your own "what if". Step 8: Copy your contribution margin income statement and the formulas over so you can quickly and easily estimate the change in profit in your "what if" scenario. Change the input numbers to match your "what if" scenario. Step 9: Based on the result, state your recommendation on whether or not you should implement the change in the space provided below that statement. Just a sentence or two in the cell right under that Income Statement is sufficient. Marks: m . Compiling the Income Statement with correct format and formula calculations. Referencing other cells or numbers wherever possible. (10 marks) Reasonableness of the assumptions used (2 marks) Correctness of both the break-even calculations, given the assumptions. (2 marks) Reasonableness and execution of the "what if scenario (4 marks) Overall appearance, clarity, organization, spelling, and grammar (2 marks) . Review View File Data Home Insert Draw Formulas Help Page Layout General 1 Conditional Fc V X ab Calibri ~ 14 ' ' TA Format as Tab Paste % 9 0 BI U A Cell Styles Styles Number LZ Alignment Font Undo Clipboard F2 x fx Dino Pie Limited Income Statement For the Month ended June 20XX Dino Pie Limited Income Statement For the Month ended June 20XX 7 Quantity Sold: 8 Quantity Sold: 9 Total Perunit Total Per unit 10 11 Sales Revenue Less Variable Costs: Sales Revenue Less Variable Costs: 12 13 14 15 18 contribution Margin Contribution Margin 11 16 Less Fixed Costs: Less Fixed Costs: 19 20 21 22 20 Net Operating Income 24 Net Operating Income Recommendation: 25 26 Break Even in units: 27 20 Break Even in Sales $ 29 30 CVP + The Assignment You will attempt to estimate, using reasonable assumptions, what a simplified contribution margin format income statement would look like for one of the locations of Dino Pie Limited. You must use the excel spreadsheet template provided. What you need to do: Step 1: Dino Pie Limited has one bakery location where it sells fresh made apple pies and nothing else. Pick what the selling price for that high quality apple pie is. Fill that in on your Income Statement. Step 2. Estimate how many customers per hour or day they have and estimate how many pies they sell per month. It doesn't matter if you are correct, just be reasonable. Fill that in on your spreadsheet and use these estimates to forecast monthly revenue. Step 3: Imagine their expenses and classify them into variable and fixed. List three or four of each. Come up with your own but make them reasonable. For example, you might have the following costs: . Variable: Pie ingredients (crust and filling) Amount of time it takes each baker to make a pie Utilities to run the bakery Fixed: . Store Manager's salary per month Advertising expense per month Monthly rent on the location per month Depreciation on the equipment per month Please note that I don't expect you to include all the possible detailed costs but rather just the obvious ones and I expect your estimates to be reasonable. Add lines to your spreadsheet if necessary. Step 4: Use this information to build a contribution margin format Income Statement in your Excel spreadsheet that is based on formulas so that you can make changes to volume or margins. Step 5: Adjust your estimates so profit looks reasonable. If your numbers show them losing a hundred thousand dollars at their location, you might want to adjust some of your assumptions. Step 6: Once those numbers look reasonable, using those numbers, calculate both the break- even sales $ and units in the cells immediately below that Income Statement. Indicate how they were calculated. Use formulas in cells wherever possible. Step 7: To the right of your first CM Income Statement, you will see that you are asked to create a "what if" scenario (eg, Increase marketing by $1,000 per month and sell 20 more pies per month). Do not use my example. Come up with your own. Use your knowledge from your marketing and math courses. Fill this in at the top of this copy - tell me what the "what if" scenario is. Come up with your own "what if". Step 8: Copy your contribution margin income statement and the formulas over so you can quickly and easily estimate the change in profit in your "what if" scenario. Change the input numbers to match your "what if" scenario. Step 9: Based on the result, state your recommendation on whether or not you should implement the change in the space provided below that statement. Just a sentence or two in the cell right under that Income Statement is sufficient. Marks: m . Compiling the Income Statement with correct format and formula calculations. Referencing other cells or numbers wherever possible. (10 marks) Reasonableness of the assumptions used (2 marks) Correctness of both the break-even calculations, given the assumptions. (2 marks) Reasonableness and execution of the "what if scenario (4 marks) Overall appearance, clarity, organization, spelling, and grammar (2 marks) . Review View File Data Home Insert Draw Formulas Help Page Layout General 1 Conditional Fc V X ab Calibri ~ 14 ' ' TA Format as Tab Paste % 9 0 BI U A Cell Styles Styles Number LZ Alignment Font Undo Clipboard F2 x fx Dino Pie Limited Income Statement For the Month ended June 20XX Dino Pie Limited Income Statement For the Month ended June 20XX 7 Quantity Sold: 8 Quantity Sold: 9 Total Perunit Total Per unit 10 11 Sales Revenue Less Variable Costs: Sales Revenue Less Variable Costs: 12 13 14 15 18 contribution Margin Contribution Margin 11 16 Less Fixed Costs: Less Fixed Costs: 19 20 21 22 20 Net Operating Income 24 Net Operating Income Recommendation: 25 26 Break Even in units: 27 20 Break Even in Sales $ 29 30 CVP +