I need someone who’s an expert in excel to do this grader project and business analysis

I need someone who’s an expert in excel to do this grader project and 6 question business analysis. N.B You have to do grader project first before you’re able do the business analysis See attachment
yo16xlgradercase5___coffee_10_instructions.docx

campbell_ex05coffee_start.xlsx

Don't use plagiarized sources. Get Your Custom Essay on
I need someone who’s an expert in excel to do this grader project and business analysis
Just from $13/Page
Order Essay

business_case_5___chloe_jants_coffee_shop.docx

Unformatted Attachment Preview

Office 2016 â?? myitlab:grader â?? Instructions
Excel Project
YO16_XL_GRADER_CASE5 – Coffee 1.0
Project Description:
Chloe Jants is fulfilling her long-term dream of opening a coffee shop on a busy corner of her hometown. Her
town is like many others in the southern half of the state, and she has done a substantial amount of research
regarding sales and customer patterns. Chloe has developed a startup plan with costs and expenses and would
like to explore different loan options to see which would work best for her. She would also like to run a statistical
analysis on the sales and customer patterns for which she has collected data.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Points
Possible
Step
Instructions
1
Start Excel. Open the file named ex05Coffee_START. Save the file with the name
ex05Coffee_LastFirst, replacing LastFirst with your name. Add the Data Analysis ToolPak to
the Ribbon.
0.000
2
On the LoanAnalysis worksheet, in cell B9, use the PMT function to calculate the end of the
month payment amount required to pay off the amount borrowed for loan option 1. Use cell
references where appropriate and make sure the result is positive.
3.000
3
In cell B10, use the PMT function to calculate the beginning of the month payment amount
required to pay off the amount borrowed for loan option 1. Use cell references where
appropriate and make sure the result is positive.
3.000
4
In cell B18, use the RATE function to calculate the annual interest rate for loan option 2 if
payments are made at the end of each month. Use cell references for all arguments. Increase
the number of decimal places to 2.
3.000
5
In cell B19, use the RATE function to calculate the annual interest rate for loan option 2 if
payments are made at the beginning of each month. Use cell references for all arguments.
Increase the number of decimal places to 2.
3.000
6
In cell B26, use the NPER function to calculate the total number of payment periods required
to pay off the amount borrowed in loan option 3 if payments are made at the end of each
period. Format as a Number with 2 decimal places.
3.000
7
In cell B27, calculate the number of years it would take to pay off the amount borrowed in
loan option 3, using the total payment periods calculated in cell B26. Format as a number with
2 decimal places.
1.000
8
In cell B39, use the CUMPRINC function to calculate the end of period cumulative principal
payment amounts for each period for loan option 4. Use cell references for all arguments and
make sure the result is positive. Autofill the function down through B78.
5.000
9
In cell C39, use the CUMIPMT function to calculate the end of period cumulative interest
payment amounts for each period for loan option 4. Use cell references for all arguments and
make sure the result is positive. Autofill the function down through C78.
5.000
Updated: 01/11/2017
1
Current_Instruction.docx
Office 2016 â?? myitlab:grader â?? Instructions
Step
Excel Project
Points
Possible
Instructions
10
In cell B79, use the SUM function to calculate the total amount of principal payments
necessary to pay off loan option 4.
2.000
11
In cell C79, use the SUM function to calculate the total amount of interest payments necessary
to pay off loan option 4.
2.000
12
On the NPV worksheet, in cell B21, use the NPV function to calculate the net present value
based on the cash flows provided.
5.000
13
In cell B23, use the IRR function to calculate the internal rate of return based on the cash
flows provided.
4.000
14
15
16
On the Depreciation worksheet, in cell B9, use the SLN function to calculate the straight line
depreciation amount for year 1. AutoFill the function down to B13.
In cell C9, calculate the accumulated depreciation at the end of year 1. In cell C10, calculate
the accumulated depreciation at the end of year 2. AutoFill the calculation down to C13.
In cell D9, calculate the book value at the end of year 1. AutoFill the calculation down to D13.
4.000
2.000
2.000
17
In cell B18, use the DB function to calculate the declining-balance depreciation amount for
year 1. AutoFill the function down to B22.
5.000
18
In cell C18, calculate the accumulated depreciation at the end of year 1. In cell C19, calculate
the accumulated depreciation at the end of year 2. AutoFill the calculation down to C22.
2.000
19
In cell D18, calculate the book value at the end of year 1. AutoFill the calculation down to
D22.
1.000
20
21
On the DailyStats worksheet, in cell C4, use the Data Analysis tools to calculate the moving
average for the number of customers that visit the coffee shop every seven days. Include a
chart output and position the chart so that the top-left corner is in cell G4 and the bottomright corner is in cell L18.
In cell F6, use the AVERAGE function to calculate the mean of the number of customers. In
cell F7, use the MEDIAN function to calculate the median number of customers. In cell F8, use
the MODE.SNGL function to determine the number of customers that appears most often in
the data set.
Updated: 01/11/2017
2
7.000
3.000
Current_Instruction.docx
Office 2016 â?? myitlab:grader â?? Instructions
Excel Project
Points
Possible
Step
Instructions
22
In cell F10, use the MIN function to determine the fewest number of customers to visit the
coffee shop in any given day. In cell F11, use the MAX function to determine the most number
of customers to visit the coffee shop in any given day.
23
In cell F13, calculate the range by subtracting the smallest number of customers to visit the
coffee shop on any given day from the largest number. In cell F14, use the VAR.S function to
calculate the variance of the sample data set. In cell F15, use the STDEV.S function to
calculate the standard deviation of the sample data set.
2.000
3.000
24
Use the Data Analysis tools to run descriptive statistics on the number of customers. Place the
summary statistics output in cell A1 of the DescriptiveStatistics worksheet. Be sure and use
the Number of Customers column label in the output and include labels in the first row.
5.000
25
On the MonthlyStats worksheet, in cell E4, use the AVERAGE function to calculate the median
monthly customers to visit the coffee shop. In cell E5, use the STDEV.S function to calculate
the standard deviation of the number of customers to visit the coffee shop each month.
2.000
26
In cell E7, use the NORMDIST function to determine the probability of meeting or exceeding
7,100 customers in a month using the cumulative probability distribution.
4.000
In the Standard Deviation column, starting in cell D11, enter values ranging from 4 to -4 in
increments of 0.1. These will serve as standard deviations from the mean that will be used to
chart the normal distribution of monthly customers.
27
In cell E11, calculate the number of monthly customers that would be 4 standard deviations
from the mean. AutoFill the calculation down to E91.
7.000
In cell F11, use the NORM.DIST function to calculate the probability of there being exactly the
4 standard deviations from the mean of customers visiting the coffee shop in a month.
Format as Percentage with 2 decimal places.
28
29
30
31
Create a Scatter with Smooth Lines chart using the Quantity and Probability data. Edit the
Chart Title to read, Monthly Customer Distribution. Add a vertical axis title that reads,
Probability, and a horizontal axis label that reads, Total Monthly Customers. Move the
chart so that the top-left corner is in cell H10 and the bottom-right corner is in cell M24.
On the Correlations worksheet, in cell E4, use the COVARIANCE.S function to calculate the
covariance between the age and visits per month variables.
In cell E5, use the CORREL function to calculate the correlation coefficient between the age
and visits per month variables.
Create a Scatter chart using the Age and Visits per month columns, A2:B30. Edit the chart title
to read Age-Visits Correlation. Add a vertical axis title that reads Visits per month. Add a
horizontal axis title that reads Age. Add a linear trend line. Move the chart so that the top-left
corner is in cell D7 and the bottom-right corner is in cell K21.
Updated: 01/11/2017
3
4.000
2.000
2.000
4.000
Current_Instruction.docx
Office 2016 â?? myitlab:grader â?? Instructions
Step
32
Excel Project
Points
Possible
Instructions
Save and close the workbook and then exit Excel. Submit the workbook as directed.
0.000
Total Points
Updated: 01/11/2017
4
100.000
Current_Instruction.docx
Chloe’s Coffee Shop
Loan Analysis – Option 1
Loan Amount
Interest Rate
Term of Loan (years)
Payments per Year
Monthly Payment (end of period)
Monthly Payment (beginning of period)
$
218,000.00
7.20%
10
12
Loan Analysis – Option 2
Loan Amount
Monthly Payment
Term of Loan (years)
Payments per Year
Interest Rate (end of period)
Interest Rate (beginning of period)
$218,000.00
$2,200.00
10
12
Loan Analysis – Option 3
Loan Amount
Interest Rate
Monthly Payment (end of period)
Total Number of Payments (end of period)
Term of loan in years
$218,000.00
7.20%
$2,200.00
Loan Analysis – Option 4
Loan Amount
Interest Rate
Term of Loan (years)
Payments per Year
Quarterly Payments
$218,000.00
7.2%
10
4
$7,692.29
Quarter Principal Payments
1
2
3
4
5
6
Interest Payments
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Totals
Chloe’s Coffee Shop
Discount Rate
Total Initial Investment
3.50%
-$218,000.00
Cash Flows
Year 0 -$218,000.00
Year 1 $10,000.00
Year 2 $12,300.00
Year 3 $15,100.00
Year 4 $18,600.00
Year 5 $22,800.00
Year 6 $28,100.00
Year 7 $34,600.00
Year 8 $42,500.00
Year 9 $52,400.00
Year 10 $64,400.00
Net Present Value
Internal Rate of Return
Chloe’s Coffee Shop
Cost of Asset
Salvage Value
Useful Life
$24,000.00
$9,000.00
5
Straight Line Depreciation
Depreciation
expense for
year
End of Year
Accumulated
depreciation at
end of year
Book value
at end of
year
1
2
3
4
5
Declining-Balance Depreciation
Depreciation
expense for
year
End of Year
1
2
3
4
5
Accumulated
depreciation at
end of year
Book value
at end of
year
Chloe’s Coffee Shop
Day of the Number of Moving
Month Customers Average
1
143
2
155
3
132
4
178
5
166
6
154
7
123
8
187
9
156
10
153
11
153
12
156
13
152
14
152
15
178
16
128
17
146
18
173
19
125
20
173
21
165
22
128
23
192
24
163
25
176
26
187
27
167
28
188
29
165
30
182
31
122
Mean
Median
Mode
Min
Max
Range
Variance
Standard Deviation
Chloe’s Coffee Shop
Month
January
February
March
April
May
June
July
August
September
October
November
December
Total Customers
7,689
7,572
6,589
5,672
4,889
4,324
5,638
6,583
7,203
7,763
7,878
8,112
Average
Standard Deviation
Desired Minimum Quantity
Probability
Standard Deviation
7,100
Quantity
Probablilty
Chloe’s Coffee Shop
Age
25
65
18
32
34
63
25
22
45
47
15
19
16
25
65
72
41
48
36
37
25
26
28
29
33
58
56
Visits per month
29
3
29
7
6
22
25
31
27
15
19
23
2
13
5
8
24
16
28
5
30
22
7
18
14
11
8
Covariance
Correlation
EBTM250 â?? (Team #)
Memo
To:
Recipient Name
From:
List Names of Team Members
Date:
Date
Re:
Chloe Jantsâ?? Coffee Shop
1) What difference does it make whether a loan payment is made at the beginning or end
of the month?
a. Loan Repayment â??
2) Based on your Net Present Value (NPV) and Internal Rate of Return (IRR) calculations,
would this be a good investment for Ms. Jants (e.g., Yes, No or Uncertain)? Provide a
justification for your recommendation.
a. Your Conclusion â??
b. Justification â??
3) Explain why the annual depreciation schedules for Straight-Line Depreciation (SLN) and
Declining Balance Depreciation (DB) are different? What would you recommend for Ms.
Jants and why?
a. Schedule Difference(s) â??
b. Recommendation to Ms. Jantsâ?? â??
c. Justification â??
4) What do the moving average calculations on the number of customers each day tell Ms.
Jants? What do the other statistics tell her? How can this data help her make decisions
about daily operations?
a. Your Response
5) What does the monthly customer distribution tell Ms. Jants? How can she use this for
strategic decision making?
a. Customer Distribution â??
b. Use of Data â??
6) What do the correlation calculations between age and number of visits tell Ms. Jants?
How can this data help her plan her daily operations?
a. Correlation Calculations â??
b. Planning â??
2

Purchase answer to see full
attachment

GradeAcers
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.

Order your essay today and save 15% with the discount code DISCOUNT15