Please help answer these practice questions

0 10 Cellar Door is about to open its orange doors to the public! You are doing some last minute number-crunching as your original estimates about operating costs need to be updated due to changes in consumer spending (they're spending less 900). You also have a target profit in mind -$25,000 each month to put towards the Cellar Door future fund for expansion and investment. You need to know whether the changing financial climate (economic downturn) will affect the future fund as well. You revisit the monthly sources of income to Cellar Door which includes the sales of a case of wine at $145 per case. From research you have assumed each customer will purchase one case. You have further assumed each customer will spend $55 at the Cellar Door. While the Cellar Door offers standard (free) and premium $5 wine tastings to each customer, you now adjust your estimate and assume only half the total monthly customers will pay for a premium wine tasting. Finally, you assume each customer will purchase one item of merchandise. All merchandise is priced at $25 and includes wine glass sets and complementary products such as olive oil and chocolate made to pair with Orange Wine's vintage. You revisit your costs spreadsheet, some of which are replicated in Table 3. These costs are monthly Cost Description Cost Wages 31,400 1,855 1,500 2,000 4,200 Cleaning Service Maintenance Cellar Supplies Utilities & Supplies Landscaping Website Insurance Table 3: Costs associated with the Cellar Door 42 420.89 261.78 Other monthly costs are for items sold to customers. Wine is sold by the case at a cost to Orange Wines of $96 per case per customer. Fresh, locally sourced organic produce for the restaurant is cost at $21 per customer, a premium wine tasting costs Orange Wines $2.30 per customer while merchandise costs per customer are set at half the amount an item of merchandise sells for. 1) Follow the Excel instructions below to produce a doughnut chart of the sources of income to the Cellar Door per customer. 2) You need to calculate break-even and other relevant information, however you want to produce two estimates: one based on the original income per customer (best-case scenario) and a more conservative income (worst-case scenario). The worst-case scenario is a 15% reduction of the income you calculated in part 1). Business often produce a sensitivity analysis by calculating several estimates to allow for best and worst case scenarios, which is what you're doing here, even though we all know Cellar Door will be a raging success 3) What is the impact on the contribution margin when making the income stream conservative? Explain the effect of this change on the break-even number in part 2) including showing the calculation of the two contribution margins. Do not show any other calculations. 4) In Excel, produce a single break-even graph of the two incomes estimates (original and conservative) and include it here - you will also include a copy in the infographic where requested. fx Revenue Split Income 1 2 3 4 Income Sources A Income Sources Average Wines Sales Restaurant Wine Tasting Gift Packs Total Income - 8 10 17 Number of Customers Number of Customers ! Income Conservative Income Cost Fixed cost Variable cost customer Revenue per customer Conservative Revenue per customer UN BEBE 0 10 Cellar Door is about to open its orange doors to the public! You are doing some last minute number-crunching as your original estimates about operating costs need to be updated due to changes in consumer spending (they're spending less 900). You also have a target profit in mind -$25,000 each month to put towards the Cellar Door future fund for expansion and investment. You need to know whether the changing financial climate (economic downturn) will affect the future fund as well. You revisit the monthly sources of income to Cellar Door which includes the sales of a case of wine at $145 per case. From research you have assumed each customer will purchase one case. You have further assumed each customer will spend $55 at the Cellar Door. While the Cellar Door offers standard (free) and premium $5 wine tastings to each customer, you now adjust your estimate and assume only half the total monthly customers will pay for a premium wine tasting. Finally, you assume each customer will purchase one item of merchandise. All merchandise is priced at $25 and includes wine glass sets and complementary products such as olive oil and chocolate made to pair with Orange Wine's vintage. You revisit your costs spreadsheet, some of which are replicated in Table 3. These costs are monthly Cost Description Cost Wages 31,400 1,855 1,500 2,000 4,200 Cleaning Service Maintenance Cellar Supplies Utilities & Supplies Landscaping Website Insurance Table 3: Costs associated with the Cellar Door 42 420.89 261.78 Other monthly costs are for items sold to customers. Wine is sold by the case at a cost to Orange Wines of $96 per case per customer. Fresh, locally sourced organic produce for the restaurant is cost at $21 per customer, a premium wine tasting costs Orange Wines $2.30 per customer while merchandise costs per customer are set at half the amount an item of merchandise sells for. 1) Follow the Excel instructions below to produce a doughnut chart of the sources of income to the Cellar Door per customer. 2) You need to calculate break-even and other relevant information, however you want to produce two estimates: one based on the original income per customer (best-case scenario) and a more conservative income (worst-case scenario). The worst-case scenario is a 15% reduction of the income you calculated in part 1). Business often produce a sensitivity analysis by calculating several estimates to allow for best and worst case scenarios, which is what you're doing here, even though we all know Cellar Door will be a raging success 3) What is the impact on the contribution margin when making the income stream conservative? Explain the effect of this change on the break-even number in part 2) including showing the calculation of the two contribution margins. Do not show any other calculations. 4) In Excel, produce a single break-even graph of the two incomes estimates (original and conservative) and include it here - you will also include a copy in the infographic where requested. fx Revenue Split Income 1 2 3 4 Income Sources A Income Sources Average Wines Sales Restaurant Wine Tasting Gift Packs Total Income - 8 10 17 Number of Customers Number of Customers ! Income Conservative Income Cost Fixed cost Variable cost customer Revenue per customer Conservative Revenue per customer UN BEBE