Question Details

Answered: - I am working with Spreadsheet and Databases with my homework and


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

 


 

__________________________________________

 


 

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

 


 

Spring 16 Case 1

 


 

IST 310: Spreadsheet and Database Applications

 


 

Page 1

 


 

IST 310: Spreadsheet and Database Applications

 

Spring 16 Case 1 Instructions

 

35 Points

 


 

__________________________________________

 

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.

 


 

Spring 16 Case 1

 


 

IST 310: Spreadsheet and Database Applications

 


 

Page 2

 


 

IST 310: Spreadsheet and Database Applications

 

Spring 16 Case 1 Instructions

 

35 Points

 


 

__________________________________________

 


 


 


 


 


 


 

Total Price ($): Use a formula to determine the total price of the phone.

 

Customer Type: If the customer uses Sprint or Verizon and has a 128GB 6s, 64GB 6s Plus, or

 

128GB 6s Plus, the customer is considered a ?VIP?. All other Sprint and Verizon customers are

 

considered ?Regular?. All T Mobile and AT&T customers are considered ?Regular?. Write a

 

formula using IF, AND, & OR to return the appropriate value (i.e. either ?VIP? or ?Regular?).

 

Use formulas to complete cells K1:K5.

 

Check Figures:

 

I20 = 5%

 

J16 = ($101.88)

 

M28 = $1,142.63

 

N13 = VIP

 

N24 = Regular

 

K5 = $744.44

 


 

Task 3

 

Steve Jobs, co-founder and former CEO of Apple, spent some time as a fruitarian. Fruitarianism (a

 

subset of veganism) is a diet that consists entirely or primarily of fruits (and possibly nuts and seeds).

 

Some fruitarians will eat only what falls (or would fall) naturally from a plant (i.e. foods that can be

 

harvested without killing or harming the plant). Many fruitarians do not eat grains, believing that it is

 

unnatural to do so. Other fruitarians feel that it is improper for humans to eat seeds as they contain

 

future plants. Some fruitarians? diets include dried fruits, nuts, honey, olive oil, beans, and chocolate.

 

All fruitarians eat absolutely no animal products.

 

Tim Cook, CEO of Apple, has an idea for a new product line that is inspired by the ?clean? eating habits

 

of a fruitarian. Apple Nutrition (with the tag line: Be Healthy. One Bite at a Time.) will consist of a

 

full line of organic snacks and energy bars that contain primarily dried fruits, nuts, and honey.

 

Additionally, Apple Nutrition will provide recipes and information about healthy eating habits though

 

the new Nutrition app which can be accessed on any iOS device. Cook believes that Apple Nutrition fits

 

perfectly with the fitness tracking capabilities of the Apple Watch and the data that is stored in the

 

Health app. As Cook stated in a recent interview: ?Everyone knows that losing weight and staying fit is

 

about both diet and exercise. The addition of the Apple Nutrition product line demonstrates that Apple

 

is fully committed to helping people live healthier lives.?

 

Steve Wozniak, co-founder of Apple, thinks that Apple Nutrition is a crazy idea. Wozniak doesn?t

 

understand why Apple would want to enter the food industry, and he believes that Steve Jobs will ?roll

 

over in his grave? if Apple pursues this new venture. Wozniak tries to persuade Cook to completely

 

trash the Apple Nutrition idea. Cook believes, however, that Apple Nutrition will be a huge success for

 

the company.

 

Spring 16 Case 1

 


 

IST 310: Spreadsheet and Database Applications

 


 

Page 3

 


 

IST 310: Spreadsheet and Database Applications

 

Spring 16 Case 1 Instructions

 

35 Points

 


 

__________________________________________

 

In order to manufacture the organic snacks and energy bars, Apple will have to purchase many new

 

pieces of equipment (Tim Cook wants Apple to actually produce the food rather than outsource the

 

production to a third-party?Steve Wozniak just vomited when he heard that!). One piece of equipment

 

that is needed to produce the Mac Energy Bar, the ABC machine, will cost $940,000 and have a useful

 

life of 5 years with a salvage value of $140,000. The straight line method of depreciation is used. Cook

 

wants to use 18% as the hurdle rate and 35% as the tax rate. All of these input values have already been

 

entered into the ?Task 3? worksheet. The price, COGS, and selling expense per energy bar as well as

 

the expected sales volume for each of the next five years have all been entered into the worksheet as

 

well.

 

Complete the rest of the cash flow analysis and calculate the NPV and IRR (i.e. all cells shaded in green

 

must be completed). Cost of Goods Sold, Selling Expense, Depreciation, and Taxes should be shown as

 

negative values (black with parenthesis around the numbers). Round taxes to zero decimal places.

 

NPV Check Figure = $183,179

 


 

Spring 16 Case 1

 


 

IST 310: Spreadsheet and Database Applications

 


 

Page 4

 


 

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Oct 07, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now