View the step-by-step solution to:

IST 310: Spreadsheet and Database Applications Spring 16 Case 1 Instructions 35 Points __________________________________________ Apple Inc. designs,...

I am working with Spreadsheet and Databases with my homework and cant get my formulas to work. Please Help

IST 310: Spreadsheet and Database Applications Spring 16 Case 1 Instructions 35 Points __________________________________________ Spring 16 Case 1 IST 310: Spreadsheet and Database Applications Page 1 Apple Inc. designs, manufactures, and markets mobile communication and media devices, personal computers, watches, and portable digital music players worldwide. The company also sells related software, services, accessories, networking solutions, and third-party digital content and applications. Apple Inc. was founded in 1977, and it currently employs more than 90,000 people. Case Assignment 1 requires you to analyze the financial performance of Apple Inc. This assignment consists of three tasks, and minimal guidance will be provided throughout these instructions to help you complete the various tasks. With that, it’s time to get to work! Template File: Spring16Case1Template.xlsx Rename To: LastNameFirstNameCase1.xlsx (e.g. BestMarkCase1.xlsx) Task 1 Use google.com/finance to download the historical stock information for Apple (AAPL) from January 2, 2014 to June 30, 2015 into a spreadsheet. Move the Apple stock information worksheet into the Case 1 assignment workbook as the first worksheet and rename the worksheet as “Task 1”. Sort the data from oldest to newest. Create an Open-High-Low-Close chart for the entire month of June 2015 . Additional information: Chart title = Apple Open-High-Low-Close (June 2015) Delete the legend No axis labels Vertical axis minimum = 124; vertical axis maximum = 132 Vertical axis formatted as currency with 0 decimal places Chart resized and displayed in cells H1:R25
Background image of page 1
IST 310: Spreadsheet and Database Applications Spring 16 Case 1 Instructions 35 Points __________________________________________ Spring 16 Case 1 IST 310: Spreadsheet and Database Applications Page 2 Create a Combo Chart that combines a line chart to show the overall trend in the closing price of the stock from January 2, 2014 to June 30, 2015 with a column chart that shows the stock volume for the same time period. Use the secondary axis for the closing price data series. Add a linear trendline based on the stock closing price data series. Additional information: Chart title = Apple Daily Stock Volume and Closing Prices Legend position = bottom No axis labels Primary vertical axis formatted as number with 0 decimal places Secondary vertical axis formatted as currency with 0 decimal places Chart resized and displayed in cells H30:R55 Task 2 The “Task 2” worksheet contains information about 35 different iPhones that were purchased from the Apple Store in Leawood, KS on October 1, 2015. Use formulas and cell references to complete all cells shaded in blue. All dollar amounts should be displayed as “Currency” with two decimal places. Percentages should be displayed with zero decimal places. Additional information: Base Phone Price ($): Use an IF statement and the HLOOKUP function. Two separate HLOOKUP tables have already been created in the “Task 2 Base Price” worksheet. Phone and AppleCare+ ($): The price of AppleCare+ (i.e. Apple’s insurance coverage) is found in cell B1. If the customer purchased AppleCare+ with his/her phone, then the amount in column H should be the base price of the phone plus the price of the insurance coverage. If the customer did not purchase AppleCare+ with his/her phone, then the amount in column H should just be the base price of the phone. You may NOT add any columns to the worksheet to calculate the values in column H. Discount (%): Use the INDEX and MATCH functions. Four separate discount tables (one for each carrier) have already been created for you in the “Task 2 Discount” worksheet. The discount tables in the “Task 2 Discount” worksheet have been named “Discount1”, “Discount2”, “Discount3”, and “Discount4”. Discount ($): Use a formula to calculate the dollar amount of the discount. The discount applies only to the base price of the phone. Store the discount amount as a negative number displayed in black with parenthesis around it. Activation Fee ($): Use the VLOOKUP function to return the proper activation fee. You must create the lookup table in the “Task 2 Activation” worksheet. Sprint and Verizon both charge a $19 activation fee. The activation fee is $25 for T Mobile and $29 for AT&T. Tax ($): Use a formula to determine the dollar amount of the tax. The tax rate is found in cell B2. Tax is applied to the phone (less the discount), AppleCare+, and the activation fee.
Background image of page 2
Show entire document

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question