Alamance-Burlington School System



Project 8-1: Create Formulas to Calculate Income and ExpensesAn employee at Tailspin Toys has entered second quarter income and expense data into a worksheet. You will enter formulas to calculate monthly and quarterly totals.GET READY. LAUNCH Excel if it is not already running.1. OPEN Tailspin Toys from the data fi les for this lesson.2. Select E4 and key _B4_C4_D4 and press Enter.3. Select B6. On the Formulas tab, in the Function Library group, click Insert Function. 4. In the Insert Function dialog box, select SUM and click OK.5. In the Function Arguments dialog box, click the Collapse Dialog button and click B4. Key - and click B5.6. Click the Expand Dialog button and click OK to close the dialog box.7. Select B6 and use the fi ll handle to copy the formula to C6:D6.8. Click B11 and click AutoSum in the Function Library group. Press Enter to accept B8:B10 as the cells to total.9. Select B11 and use the fi ll handle to copy the formula to C11:D11.10. Select B13 and click Insert Function in the Function Library group. On the Insert Function dialog box, SUM will be the default. Click OK.11. Click the Collapse Dialog button for Number1 and click B6. Key - and click B11. Press Enter and click OK to close the dialog box.12. Select B13 and use the fi ll handle to copy the formula to C13:D13.13. Select E4. Click AutoSum in the Function Library group. Press Enter to accept the range as B4:D4. Copy the formula to E5:E14. Then delete the data in cells E7 and E12.14. Select B15, key _B13-B14, and press Enter. Copy the formula to C15:E15.15. SAVE the workbook as Tailspin Toys 8-1 and then CLOSE the fi le.LEAVE Excel open to use in the next project.Project 8-2: Use AutoSum to Total Sales; Calculate Percentage of IncreaseBlue Yonder Airlines has created a workbook to analyze sales for its fi rst four years of operation. Enter formulas to determine the total sales for each division and the percentage increase/decrease each year.GET READY. LAUNCH Excel if it is not already running.1. OPEN the Blue Yonder data fi le for this lesson.2. Select F4 and click AutoSum in the Function Library group on the Formulas tab.3. Press Enter to accept B4:E4 as the range to add.4. Use the fi ll handle to copy the formula in F4 to F5:F8.5. Select B12 and key _(C4-B4)/C4. Press Enter. This formula calculates the percentage increase in sales from 2005 to 2006. The numbers in parentheses yield the amount of the increase. The increase is then divided by the 2006 sales.6. Select B12. Use the fi ll handle to copy the formula to B13:B15.7. With the cell range B12:B15 still selected, use the fi ll handle to copy the formulas in the selected range to C12:D15.8. Select F12. Key _(E4-B4)/E4 and press Enter. This enters a formula to calculate the percentage increase from the fi rst year (2005) to the most recent (2008).9. Copy the formula in F12 to F13:F15.10. SAVE the workbook as Blue Yonder 8-2 and then CLOSE the fi le. LEAVE Excel open for the next project.Project 8-3: Calculate Totals and PercentagesIn the previous project, you calculated total sales for Blue Yonder’s fi rst four years of operation. You also calculated the percentage of increase or decrease in sales for each year. In this project, you will calculate expense totals and percentage increase or decrease.GET READY. LAUNCH Excel if it is not already running.1. OPEN the Blue Yonder Expenses data fi le for this lesson. Expense History worksheet should be the active worksheet.2. Select B8 and click AutoSum to total the 2005 expenses.3. Copy the formula in B8 to C8:F8.4. Select F4 and click AutoSum to total Corporate Contracts expenses for the four-year period.5. Copy the formula in F4 to F5:F7.6. In B12, create a formula to calculate the percentage increase in Corporate Contracts expenses from 2005 to 2006. Begin with 2006 expenses minus 2005 expenses, divided by 2006. Use parentheses to instruct Excel which function to perform fi rst.7. Copy the formula from B12 to B13:B15 and to C12:D15.8. In F12, create a formula to calculate the percentage increase in expenses from 2005 to 2008. Remember to construct the formula to subtract and then divide.9. Click Percentage Style (%) in the Number group. If necessary, click Increase Decimal to display one position after the decimal point.10. Copy the formula in F12 to F13:F15.11. SAVE the workbook as Blue Yonder Expenses 8-3 and then CLOSE the file. LEAVE Excel open for the next project.Project 8-4: Create Formulas in a Template WorksheetTailspin Toys wants to project income and expenses for the third quarter based on its performance in the second quarter. A template has been created for the projections. In this project, you will create formulas for the calculations that affect only this worksheet. In the next exercise, you will create formulas that refer to data in another worksheet in this workbook. You are creating a template, so the values returned by your formulas will be $0 until you use the template in the next exercise.GET READY. LAUNCH Excel if it is not already running.1. OPEN the Tailspin Projections data fi le for this lesson.2. In the Third Qtr worksheet, key 0 (zero) as a placeholder in B4 and in B5.3. Select B6 and enter a formula to subtract the cost of goods sold from sales. The value returned will be $0.4. Key 0 as a placeholder in B8:B10.5. Select B11 and click AutoSum to calculate total expenses.6. Select B13 and enter a formula to subtract total expenses from the gross margin.7. Federal taxes are estimated to be 34% net income. Select B14 and enter a formula to multiply net income before taxes by 34%.8. In B15, enter a formula to calculate net income after taxes.9. SAVE the workbook as Tailspin Projections 8-4. LEAVE the workbook open for the next project.Mastery AssessmentProject 8-5: Refer to Data in Another WorksheetTailspin Toys wants to set goals for the third quarter based on its performance in the second quarter. Its goal is to increase sales by 10% while keeping costs and expenses to 5%. You will create formulas to calculate the projections.USE the workbook you saved in Project 8-4.1. Make Third Qtr the active sheet and display the Formulas. The formula to establish the sales goal for third quarter will be second quarter total sales _ (second quarter total sales *10%).2. Select B4, click Recently Used in the Function Library group, and click SUM in the Select a function box. Select Second Qtr E4 as the Number1 function argument.3. In the Number2 argument box, key _( and click Second Qtr. Select E4.4. Continuing in the Number2 argument box, key *10%) and press Enter. Your completed formula should read _SUM(‘SecondQtr’!E4,_(‘Second Qtr’!E4*10%)).5. On the Third Quarter worksheet, select B5. Click Recently Used in the Function Library group and click SUM. Select Second Qtr E5 as the Number1 function argument.6. In the Number2 argument box, key _( and click Second Qtr. Select E5.7. Continuing in the Number2 argument box, key *5%) and press Enter.8. Copy the formula in B5 to B8:B10.9. SAVE the workbook as Tailspin Projections 8-5 and then CLOSE the file. LEAVE Excel open for the next project.Project 8-6: Name a Range and Use the Range in a FormulaBlue Yonder Airlines wants to analyze the sales and expense data from its four-year history.GET READY. LAUNCH Excel if it is not already running.1. OPEN the Income Analysis data fi le for this lesson.2. Select B4:E4 and click Defi ne Name on the Formulas tab. Accept the defaults in the dialog box and click OK.3. Repeat Step 2 and name the other three income sources.4. On the Analysis worksheet, select B5 and create a formula to calculate the four-year average for corporate contract sales. Use the Corporate Contracts named range in the formula.5. Create a formula using the appropriately named range in B6, B7, and B8.6. In column C, create a formula to calculate the maximum sales for each division.7. Show the formulas on the screen. Adjust column width, if necessary, to display the entire formulas.8. Print the Analysis worksheet in landscape orientation with gridlines and column headings included.9. SAVE the workbook as Income Analysis 8-6 and then CLOSE the file. LEAVE Excel open for the next project. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download