Instructions Formulas Req 1 Ch1 Req 2 Ch1 Chapter 1: Applying Excel Name: G#: Data Sales $12,000 Variable costs: Cost of goods sold $6,000 Variable selling S600 Variable administrative $400 Fixed costs: Fixed selling $2,500 Fixed administrative $1,500 Enter a formula into each of the green cells marked with a ? below. Once complete use the "Copy Formula" button. Exhibit 1-7 Traditional Format Income Statement Sales Cost of goods sold Gross margin Selling and adninin Selling Administrative Net operating income Copy Formulas Contribution Format Income Statement Sales Variable expenses: Cost of goods sold Variable selling Variable administration Contribution margin Fixed expenses Fixed selling Fixed administrative Net operating income Chapter 1: Applying Excel Req. 1: Enter $900 for Operating Inco S4,700 in red be press the copy When correct, $12,000 $6,000 Data Sales Variable costs: Cost of goods sold Variable selling Variable administrative Fixed costs: Fixed selling Fixed administrative $400 How much is the $2,500 $1,500 Exhibit 1-7 Did the Gross ? T Traditional Format Income Statement Sales Cost of goods sold Gross margin Selling and administrative expenses: Selling Administrative Net operating income ? ? Why or why no TI ? Contribution Format Income Statement Sales Variable expenses: Cost of goods sold Variable selling Variable administration Contribution margin Fixed expenses: Fixed selling Fixed administrative Net operating income ? ? ? ? ? ? Req. 1: Enter $900 for the green variable selling cell in the data section. The Net Operating Income should now be $700 and the Contribution Margin should be $4,700 in red bold text. If not, go back to the formulas page, make corrections, press the copy formulas button, and recheck your answers until they are correct. When correct, answer the questions below. How much is the gross margin? Did the Gross Margin change from the previous Formulas page? Why or why not? Chapter 1: Applying Excel Req. 2: Enter th data cell Data Sales Variable costs: Cost of goods sold Variable selling Variable administrative Fixed costs: Fixed selling Fixed administrative What ha $2,500 $1,500 Exhibit 1-7 Did the Traditional Format Income Statement Sales ? ? ? Cost of goods sold Gross margin Selling and administrative expenses: Selling Administrative Net operating income ? ? Did the ? Contribution Format Income Statement Sales Variable expenses: Cost of goods sold Variable selling Variable administration Contribution margin Fixed expenses: Fixed selling Fixed administrative Net operating income ? ? ? ? ? ? ? ? Req. 2: Enter the new data given in requirment 2 on page 48 of the textbook in the green data cells on the left. After the data is entered, answer the questions below. What happened to the variable and fixed costs after the 10% increase in Sales? Did the Contribution Margin change by 10%? Why or why not? Did theNet Operating Income increade by 10%? Why or why not? ACTG131 Chapter 1 Applying Excel Lab Instructions 1. The information and requirements for the Chapter 1 Applying Excet problem can be found on pages 47-48 of the textbook 2. Enter your name and number at the top of the page in the yelow cells on the next page. Formulas Enable macros on the spreadsheet it a warning pops up Click the "Options" button, then in the pop up box thoone Enable the content then click "OK" Security Warning Macros have been disables Option Security Alert-Macro Mbwe We Contes de tres Peter ACTOR www.cat 4. Go to the next page. Formulas, and complete the green cols with a 7 in Exhibit 1-7 using Excel formulas only, not by typing numbers/amounts. For example, the entry for Sale in coll18 should be anno 2.000. Once you hit the enter key92.000 will appear as the sales amount in cell D18, but would actually show any amount from oel CS in the data section of changed The formula for Gross Margin. -D18-019 s also given as an example. Sometimes there are other formulas to comectly calculate 30 answer. In the case of Gross Margin, you could also enter **C5-CT" as the formula for col D20. formulas are not used the reports, the answers for the stor requirements will be incorect when you new data amounts that are given to compare different scenarios as the wrong amount would be used in the calculation 5. Once all of the formulas are entered, use the "Copy Formulas" button to copy the formulas to the other sheets of the workbook you we using any other program ke Numbers on a Mac Google Docs, or the online version of Excel you will have to copy and paste your Formulas to each sheet manual 6. Select the Reg 1 Cht sheet in the workbook and enter the data change in the green colas specified in Requirement 1 7. If your formulas are correct both Net Operating Incomes should now be $700 and the Contribution Margin should be 54.700. These answers are shown in bold red text on the worksheet & If you don't get these answers, find the formula errors in the green cels back on the Formulas sheet and make your comections. Once corrected update the formulas on the other sheets by clicking on the "Copy Formulas" button again worksheet 9. When you have the correct answers, type your explanations for the Requirements questions in the green boxes on the right of the 10. Go to sheet Roq 2 Cht in the workbook and change the data at the top of the sheet to the data given in requirement 2 Answer the questions for requirement 2 in the green answer boxes on the right of the worksheet 11