Accounting 2020 - Introduction to Managerial Accounting Summer 2020 Excel Project - Cost/Volume/Profit Analysis Candlemania, Inc. is a maker of scented candles in decorative containers. During 2020, the company sold 30,000 candles at a sales price of $120 each. Following are the 2020 financial results: Candlemania, Inc. Contribution Format Income Statement For the Year 2020 $3,600,000 Sales Less variable costs: Direct materials Direct labor Variable overhead Total variable costs Contribution Margin Less fixed costs: Income before taxes 540,000 480,000 660,000 1,680,000 $ 1,920,000 500,000 $ 1,420,000 You are the top managerial accountant at Candlemania, and are scheduled to meet with the company's CEO next week to discuss the financial outlook for 2021. In particular, the CEO will want to know what steps the company might take to improve its 2020 income performance. You expect to be asked questions about the impact that various changes in sales price, sales volume, and costs would have on the company's contribution margin, breakeven point, margin of safety, and income. In addition, the CEO is likely to ask this question: if both sales price and costs increase in 2020, how many candles would we have to sell to make a certain amount of income in 2021? You are the top managerial accountant at Candlemania, and are scheduled to meet with the company's CEO next week to discuss the financial outlook for 2021. In particular, the CEO will want to know what steps the company might take to improve its 2020 income performance. You expect to be asked questions about the impact that various changes in sales price, sales volume, and costs would have on the company's contribution margin, breakeven point, margin of safety, and income. In addition, the CEO is likely to ask this question: if both sales price and costs increase in 2020, how many candles would we have to sell to make a certain amount of income in 2021? You obviously won't be able to do these calculations by hand during the meeting, so you have decided to construct an Excel program to do them for you. Page 2 is an example of how the output of your program should look. Please note that the only area of the program that may contain hard-coded numbers is Section A; the only fields that you may change when doing "what ifs" are the shaded ones in Section B; and all cells containing a "?" on the attached example (including those in Section C) must contain only formulas. No hard-coded numbers are allowed in any cell containing a "?"! You will be expected to turn in the following: 1. Send your Excel file to wkwak@unomaha.edu within 3 weeks. You may use Answerkey since actual parts are done. (Due July 31) 2 With 2020 results accurately portrayed, and a "2021 what-if?" scenario based on these change factors: Sales in units +8% Sales price per unit +3% Direct materials cost per unit +2% Direct labor cost per unit +5% Variable overhead cost per unit -1% Fixed costs -7% Target income +6% Accounting 2020 - Introduction to Managerial Accounting Summer 2020 Excel Project - Cost/Volume/Profit Analysis Candlemania, Inc. is a maker of scented candles in decorative containers. During 2020, the company sold 30,000 candles at a sales price of $120 each. Following are the 2020 financial results: Candlemania, Inc. Contribution Format Income Statement For the Year 2020 $3,600,000 Sales Less variable costs: Direct materials Direct labor Variable overhead Total variable costs Contribution Margin Less fixed costs: Income before taxes 540,000 480,000 660,000 1,680,000 $ 1,920,000 500,000 $ 1,420,000 You are the top managerial accountant at Candlemania, and are scheduled to meet with the company's CEO next week to discuss the financial outlook for 2021. In particular, the CEO will want to know what steps the company might take to improve its 2020 income performance. You expect to be asked questions about the impact that various changes in sales price, sales volume, and costs would have on the company's contribution margin, breakeven point, margin of safety, and income. In addition, the CEO is likely to ask this question: if both sales price and costs increase in 2020, how many candles would we have to sell to make a certain amount of income in 2021? You are the top managerial accountant at Candlemania, and are scheduled to meet with the company's CEO next week to discuss the financial outlook for 2021. In particular, the CEO will want to know what steps the company might take to improve its 2020 income performance. You expect to be asked questions about the impact that various changes in sales price, sales volume, and costs would have on the company's contribution margin, breakeven point, margin of safety, and income. In addition, the CEO is likely to ask this question: if both sales price and costs increase in 2020, how many candles would we have to sell to make a certain amount of income in 2021? You obviously won't be able to do these calculations by hand during the meeting, so you have decided to construct an Excel program to do them for you. Page 2 is an example of how the output of your program should look. Please note that the only area of the program that may contain hard-coded numbers is Section A; the only fields that you may change when doing "what ifs" are the shaded ones in Section B; and all cells containing a "?" on the attached example (including those in Section C) must contain only formulas. No hard-coded numbers are allowed in any cell containing a "?"! You will be expected to turn in the following: 1. Send your Excel file to wkwak@unomaha.edu within 3 weeks. You may use Answerkey since actual parts are done. (Due July 31) 2 With 2020 results accurately portrayed, and a "2021 what-if?" scenario based on these change factors: Sales in units +8% Sales price per unit +3% Direct materials cost per unit +2% Direct labor cost per unit +5% Variable overhead cost per unit -1% Fixed costs -7% Target income +6%