Homework answers / question archive / BA 250 Analytical Techniques in BUS Review Assignments ch1 there are no Data Files needed for the Review Assignments

BA 250 Analytical Techniques in BUS Review Assignments ch1 there are no Data Files needed for the Review Assignments

MS Excel

Share With

---

BA 250 Analytical Techniques in BUS

Review Assignments ch1

there are no Data Files needed for the Review Assignments.

Carmen needs to estimate the total costs for supplying computers, audio/video equipment, and Internet access to the participants at the Boston conference. She has some documentation on the cost of different expense items. Carmen asks you to enter that information into a workbook and calculate the total cost of items in this expense category. Complete the following:  

1.  Create a new, blank workbook, and then save it as NP_eX1_equipment in the location  specified by your instructor.  

2.  Rename the Sheet1 worksheet using Documentation as the new name.  

3.  Enter the data shown in Figure 1–42 in the specified cells.Figure 1–42 Documentation sheet dataFigure 1–43 conference summary data  

4.  Set the font size of the title text in cell A1 to 24 points.  

5.  Set the width of column B to 32 characters, and then wrap the contents of cell B5 within the cell.  

6.  Add borders around all of the cells in the range A3:B5.  

7.  Add a new worksheet after the Documentation sheet, and then rename the worksheet using equipment expenses as the new name.  

8.  In cell A1, enter Medit conference equipment expenses as the worksheet title. Set the font size of the title text in cell A1 to 20 points.  

9.  Enter the data summarizing the conference in the specified cells as shown in Figure 1–43. Make sure that the postal code value is treated as text rather than a number.

Cell Text Cell Text A4Equipment CategoriesA12PhoneA5Total ExpensesB7Conference ConnectionsA7VendorB8480 Technology laneA8Street AddressB9BostonA9CityB10MassachusettsA10StateB1102155A11Postal CodeB12(617) 555-7814

10.  Enter the column titles and expenses for various equipment that will be used at the conference in the range A14:E20 as shown in Figure 1–44.

Figure 1–44 equipment expensesExpense Category    Subcategory Description Units Cost per Unit E25010computer workstation rental25$105.00 E25020audio/video rental10$85.00 E25030screen projector rentals10$75.00 E25040high-speed Internet access1$450.00 E25050onsite wiring1$500.00 E25056web hosting1$700.00

11.  In cell F14, enter total as the label. In the range F15:F20, calculate the total cost of each equip-ment item by entering formulas that return the value of the number of units ordered multiplied by the cost per unit.

12.  In cell E22, enter subtotal as the label, and then in cell F22, use the SUM function to calculate the sum of the values in the range F15:F20.

13.  In cell E13, enter tax Rate as the label, and then in cell F13, enter 3% as the value.  

14.  In cell E23, enter est. tax as the label, and then in cell F23, calculate the estimated tax by enter-ing a formula that multiplies the subtotal value in cell F22 by the tax rate in cell F13.

15.  In cell E24, enter tOtAL as the label, and then in cell F24, use the SUM function to calculate the sum of the subtotal value in cell F22 and the estimated tax in cell F23.

16.  Insert new cells in the range A13:A24, shifting the other cells to the right.  

17.  In cell A14, enter Account iD as the label. In cell A15, enter e2-5010 as the first ID. In cell A16, enter e2-5020 as the second ID, and allow Flash Fill to enter the remaining IDs.

18.  Add borders around all of the cells in the range F13:G13,A14:G20,F22:G24.

19.  Set the width of columns A and B to 22 characters. Set the width of columns C, E, F, and G to  13 characters. Set the width of column D to 24 characters. Set the height of row 13 to 30 points.

20.  Wrap the text in the range D15:D20 so all of the content is visible.

21.  In cell B4, use the COUNT function to count the number of numeric values in the range E15:E20. In cell B5, display the value of the total expenses that was calculated in cell G24.

22.  Carmen wants to keep the equipment budget under $6,000. If the total cost of the equipment is less than $6,000, enter within budget in cell B3, otherwise enter over budget in the cell.

23.  Change the page orientation of the Equipment Expenses worksheet to landscape orientation, and then scale the width and height of the Equipment Expenses worksheet to print on a single page.

24.  Save the workbook. If you are instructed to print, then print the entire workbook.

25.  Display the formulas in the Equipment Expenses worksheet. If you are instructed to print, then print the worksheet. Remove the worksheet from formula view.

26.  Save the workbook, and then close it.