Purpose: - Computer Science and Engineering



CS&E 1111/1112 pre Lab 6: Boolean logic & charting Purpose: The purpose of this lab is to apply decision making skills to our worksheets. You will be working with Relational Operators and Boolean Logical functions including AND, OR and NOT. The in-lab portion will also expand your knowledge and use of the Excel Charting tools. Lab 6 Pre-Lab Assignment –complete before coming to lab:Part 1 – Lookup Function – Golf OrdersYou are a sales rep for a sporting equipment vendor. To assist you in pricing your golf ball product have setup a worksheet and will now complete it. Golf balls are sold by the 3 can canisters ($/each). Canister pricing varies based on the total number ordered. The shipping costs vary based on the method of shipment as listed here.88265063500000The Golf workbook containing lookup tables and an order form is in a worksheet named Golf Balls as seen here. In these steps, you need to complete this worksheet using the appropriate lookup functions to calculate the total prices, total shipping charges, and grand totals. Complete the following:Open the workbook named Golf.xlsx from Carmen, then save it as Golf Orders1.xlsx.In the Golf Balls worksheet, complete the Unit Pricing lookup table to include the units and corresponding prices that your company charges for golf balls based on the pricing info given in the table in the problem plete the Shipping Costs table to list the appropriate unit shipping charges.In cell E14, write a formula using a LOOKUP function to calculate the unit price for this order based on the quantity ordered. Write the formula so that it can be copied down the column, and then copy the formula into cells E15:E19.In cell F14, calculate the total price of this order (excluding shipping). Write the formula so that it can be copied down the column, and then copy the formula into cells F15:F19.In cell G14, calculate the unit shipping charge based on the shipping method. Write the formula so that it can be copied down the column, and then copy the formula into cells G15:G19. Correct data entry errors, as necessary.In cell H14, calculate the total shipping cost. Write the formula so that it can be copied down the column, and then copy the formula into cells H15:H19. Ignore any inconsistent formula errors.In cell I14, calculate the shipping discount, using Shipping Discount 2, based on the total price (column F). The discounted value will be the corresponding percentage times the total shipping cost previously calculated. Write the formula so that it can be copied down the column, and then copy the formula into cells I15:I19.In cell J14, calculate the grand total for this item. Write the formula so that it can be copied down the column, and then copy the formula into cells J15:J19.Save and close the Golf Orders1.xlsx workbook.Part 2 – Financial Functions – Loan AnalysisTo promote your company’s line of new athletic shoes, the marketing group has decided to purchase advertising in selected print media, including leading health and fitness magazines and brochures to upscale sport outlets. Although the cost of this advertising has been worked into the selling expense, this money will actually be needed now, in year 0, rather than in years 1 and 2, so that the advertising agencies can begin designing a promotion and arranging for publication. The finance group will discuss financing options directly with the advertising agencies, which are willing to accept a variety of different payment terms. Your task in these steps is to set up a worksheet to analyze each of the advertising agency plete the following:-3111555499000Open a new workbook and save it as Advertising.xlsx. Create a worksheet with the following column headings: 3. Include the title Advertising Options on your worksheet, merged and centered over the data.4. Fill in the appropriate data inputs and calculations for each option (across the row) so that all information is listed. For all options, assume that the payment period duration will be used as the compounding period and that payments are made at the end of each period.? Option 1—AD Executives Inc. has proposed a campaign costing $45,000. This agency will accept full payment over the next two years in equal monthly installments of $2,100. For this option, you need to calculate the annual interest rate.? Option 2—Bradshaw & Hicks has designed a campaign for $45,000 and indicated that it will charge a 6.25% annual rate of interest on this amount, with fixed quarterly payments paid out over the next 18 months. For this option, you need to calculate periodic payments.? Option 3—AdWest Inc. has proposed the most modestly priced campaign, costing $30,000. This agency is willing to accept monthly payments of $1,400 until the Evaluating the Financial Impact of Loans and Investments Chapter 6 383 6Level 1 campaign is completely paid off. AdWest Inc. will charge a 6.5% annual interest rate. For this option, you need to calculate the duration in years that will be required to pay off this debt.? Option 4—Johnson, Bellview & Associates has shown the Marketing team an excellent campaign that will cost $1,500 a month for the next two years. This agency’s payment terms are based on an annual interest rate of 5%. For this option, you need to calculate the initial value of this advertising campaign.5. In an adjacent column, calculate the total yearly payments required for each option.6. Format your worksheet so that it is easy to read. Be certain that dollars and percentages are included where appropriate and that columns display consistent numbers of decimal places. Wrap text, as necessary, to format the column headings within reasonable column widths. Highlight cells with the data outputs.7. In a row below your data, select an option to recommend if you were trying to minimize the yearly outlay for this campaign. Highlight your recommendation in pink.8. Save and close the Advertising.xlsx pleted Tasks: Golf Orders1.xlsx Advertising.xlsx ................
................

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

Google Online Preview   Download