39 A B C D E F G J 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 0 For the Year 2020 1 12 Sales $3,600,000 13 Less variable costs 14 Direct materials 540,000 15 Direct labor 480,000 16 Variable overhead 660,000 17 Total variable costs 1,680,000 18 Contribution Margin $ 1,920,000 19 Less fixed costs: 500,000 20 Income before taxes $ 1,420,000 21 22 23 You are the top managerial accountant at Candlemania, and are scheduled to meet with the company's 24 CEO next week to discuss the financial outlook for 2021. In particular, the CEO will want to know 25 what steps the company might take to improve its 2020 income performance. You expect to be asked 26 questions about the impact that various changes in sales price, sales volume, and costs would have 27 on the company's contribution margin, breakeven point, margin of safety, and income. In addition, 28 the CEO is likely to ask this question: if both sales price and costs increase in 2020, how many 29 candles would we have to sell to make a certain amount of income in 2021? 30 31 You obviously won't be able to do these calculations by hand during the meeting, so you have decided 32 to construct an Excel program to do them for you. Page 2 is an example of how the output of your 33 program should look. Please note that the only area of the program that may contain hard-coded 34 numbers is Section A, the only fields that you may change when doing "what ifs" are the shaded ones 35 in Section B, and all cells containing a "?" on the attached example (including those in Section C) 36 must contain only formulas. No hard-coded numbers are allowed in any cell containing a "? 37 38 You will be expected to turn in the following 39 1. Send your Excel file to wkwak@unomaha.edu within 3 weeks. (Due July 51) You may use Answerkey since actual parts are done 40 41 42 43 44 45 46 47 48 49 50 51 2 With 2020 results accurately portrayed, and a "2021 what-it?" 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% Instructions Format Answerkey Check Figure Ready