Oregon State University
Oregon State University Home Page

Up Practice Solution
 

BA131 Practice Final Exam
Updated:  03/10/2007 02:25:18 PM
  • You have 1 hour and 50 minutes to complete this exam.
  • Pace your work ... if you get stuck, move on.
  • You are free to reference class notes and all on-line course materials.
  • Please let me know immediately if your computer hangs up or other problems occur. Good luck!
  1. Create a PracticeFinal folder within your BA131 folder in your personal classwork area.
  2. Use Outlook to notify you when the class session is almost over.
    1. Within Outlook, go to the calendar function and set an "appointment" for today at the end of your class period.  For example, if you are in the 12:00 class, set an appointment for 12:50.
    2. Use the reminder option to provide a warning 15 minutes prior to the appointment.
    3. Capture a Gif screen image showing your appointment.
      1. Set the View option on the calendar to day, as in .
      2. Your image should look generally like this:

      3. Save the image as Appointment.gif within your PracticeFinal folder. Thus, it should be stored as P:/classwork/ba131/practicefinal/appointment.gif.
         
  3. Search \\cob-storage\StudentWebData\coajr149 for a file containing the text Practice Income Statement.  Save this file in your PracticeFinal folder as ProfitProjection.xls. (You should end up with a file named P:/classwork/ba131/practicefinal/profitprojection.xls.)
    1. Rename Sheet2 to Projected Profit (include the space between the two words).
    2. Complete the projected profit worksheet.  Use relative and absolute addressing such that the formulas in columns D through G are copied from column C. The picture below is for formatting use only--don't try to match the values...

       wpe23C.jpg (65585 bytes)

      1. The quantity sold for the month of July comes directly from the Assumptions worksheet.   The quantity sold in each subsequent month should be adjusted by the appropriate percentage change. 
      2. You should be able to copy your formula for Cell B3 into cells B4, B6 and B7. 
      3. You should be able to copy your formula for Cell C3 into cells C4, C6 and C7. Hint: You will need to use a mixed reference to the cell in the Assumption sheet; that is, the row number should change when you copy this reference but the column should be locked (=Assumptions!$xn - where x is a column and n is a row).
      4. Projected Sales is the quantity sold times the unit price. 
      5. Projected Profit is projected sales times gross profit margin on the Assumptions worksheet. Write a formula in B12 that can be copied to B13, and then copy those formulas to columns C through G.
      6. The total is the sum of Projected Profit for the Name and Premium brands.  Use Autosum, but be careful to check the cell addresses.
    3. Format the worksheet to match the image above. Use the Autoformat -- Colorful 2 as a starting point, and customize from there.
    4. Create a 3-D Line Chart which shows the projected profit for the Name and Premium brands.  Save this chart on a sheet named Profit Chart (include a space between the words).
      1. Add titles and labels
      2. Change the values for the Projected Profit axis to range from $10,000 to $16,000. Format these values to display currency without decimals.


     

  4. Save the following files into your PracticeFinal folder.
    1. PFFormat.doc
    2. PFPrice.txt
    3. PFSales.txt
    4. PFCustList.txt
  5. Create a new Excel workbook named CustomerSales.xls within your PracticeFinal folder. If you complete this step correctly, you should be able to use this link to open the CustomerSales.xls file.
    1. You should have three worksheets in the workbook – name them Customers, Sales and Prices
    2. Put the information in PFCustList.txt into the Customers worksheet starting with cell A1.
    3. Put the information in PFSales.txt into the Sales worksheet starting with cell A1.
    4. Put the information in PFPrice.txt into the Prices worksheet starting with cell A1.
    5. On the Sales worksheet
      1. Add a heading in cell F1 for Unit Price and use a lookup function to retrieve the unit price for each coffee mix variety from the Price worksheet.
      2. Add a heading in cell G1 for Total Sales and compute the total sales as the quantity sold times the unit price.
      3. Adjust the Sales worksheet to look like the following image. This will require you to change row 1 to be 25 points tall, make the text in row 1 bold, and adjust column widths and number formats.:


         
    6. On the Customer worksheet:
      1. Add column headings in cells G1 and H1 named Total Sales and Discount.
      2. Use a Sumif function to find the Total Sales for each Customer Name from the Sales worksheet.
      3. Compute the discount as the total sales divided by 20,000.  This is the only place you will have an actual value in a computation.
      4. In cell G28 and H28, add labels for  Total Sales and Discount
      5. In cells F29 to F31, add the labels "Average", "Minimum" and "Maximum" and compute the average, minimum and maximum values for columns G and H.
      6. Apply appropriate formats for the values displayed.
      7. Change the format of the heading row (row 1) to set the wrap text option.
      8. Format the table using the autoformat "List3" option.
      9. Results for the first five customers are shown below.


       

  6. Open the PFFormat word document and save it as CustomerDiscount.doc in your PracticeFinal folder.  If you complete this step correctly, you should be able to use this link to open the CustomerDiscount.doc file. Set the document up as a mail-merge using the CustomerSales.xls workbook as the data source.   This business letter should be formatted so that one of the finished letters would look like this:

     

    1. The document should have a 1-inch top and bottom margins and 1.5-inch left and right margins.  REMEMBER that there are not supposed to be ANY empty paragraphs in this document.
    2. Throughout the document, set the font to be12-point Times New Roman.
    3. The first paragraph (line) should contain the date -- right-justified formatted as shown below.
    4. The next three lines should contain merge fields for the name, address and city-state-zip of the customer.
    5. The letter salutation should be addressed to the customer contact.
    6. Put 12-points of spacing before paragraphs as necessary to match the vertical spacing shown above.
    7. Insert the total sales merge field into the appropriate space. Give it a picture switch like this:
    8. Copy cells F28 to H31 from the Customer worksheet and insert them as a table in the report.  The table in the Word document should be dynamically linked (OLE-linked) to the data in the spreadsheet.  Align the table with the right margin of the document and use square wrapping.

     

  7. Perform the merge, and save the form letters resulting from the merge as MergedLetters.doc in your PracticeFinal folder.
     
  8. CONGRATULATIONS!  You are done with the practice Final exam.  It is a good idea to now check your work against the  solution files to see where you need to improve and practice.