SOLUTION: Cuyamaca College Computer Loan Calculation Excel Worksheet

3/4/2021
Excel Exam 1
110 Home
Assignments
Connect
Lectures
Simnet
Study Guides
Tests
Exams
Schedule
Syllabus
Lab
FAQ
Messenger
Videos
Submitting
Excel Exam 1
Grading – This exam is worth 40 points.
Assistance – NO – You cannot use assistance in completing this assignment including tutors
or lab assistants or any other person. Contact your instructor if you have questions.
Resubmit – YES – You may correct this assignment 1 time to improve your score if a
completed version is uploaded by the date listed in the schedule
Information – You will create a quick analysis of the Southwest Mini-Market #211.
The analysis includes income from sales, payroll, loan and other expenses.
In addition a chart of the expenses is also needed.
The goal of this exam is to apply basic formatting and enter worksheet formulas to calculate the net
income.
It requires you to demonstrate the application of concepts covered in the Excel lab book.
The concepts covered in this exam come from the Let Me Try exercises you completed.
If you did not complete all the training in chapters assigned before this exam then you will find parts
of this assignment difficult.
Instructions – This is a test so it does not contain detailed instructions since you are being
assessed on how well you can apply the concepts from the text.
Unless specific requirements are listed you are responsible for all decisions regarding formatting the
worksheets including but not limited to cell width, number format, labels, and formulas.
Poorly formatted worksheets or those which do not appear “business like” or professional will not
earn full credit.
See the examples on the Worksheet Design page. Exception: If you do not see the exact colors
listed for the steps in the exam then you can substitute a color which is the closest match.
I recommend that you print the instructions and check off each step as you complete the exam
to be sure that all steps are completed.
This exam covers the material from Excel chapters 1 to 3 so if you are not sure about
something you may want to review those chapters.
Upload the completed spreadsheet file to the drop box using the Exam 1 link.
Read the information below and click to view the assignment details.
THIS IS A TEST
I have Excel 2016 or Excel 2019 to complete this assignment.
I will upload the completed spreadsheet to the class drop box.
I understand that this is an individual exam to be completed without assistance.
click the checkbox to agree and continue to the instructions
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
1/9
3/4/2021
Excel Exam 1
Part 1 – Create a new Workbook for the exam
1. Start Excel and create a new blank workbook. Save your new workbook as
dylan.audo_Excel_Exam1 (including the underscore characters)
2. Add additional worksheets to your workbook so you have a total of 3 worksheets and name the
worksheets from left to right as follows: Overview, Loan, Payroll
3. Using the standard Office theme change the tab colors as follows:
Overview
Blue-Gray Text 2, Lighter 60%
Loan
Orange Accent 2, Lighter 40%
Payroll
Blue Accent 1, Lighter 40%
Office Theme Colors
Office Theme ——->
! Some systems may have variations in their Office theme.
If yours do not match then use the closest color to those listed in the assignment.
4. Review – Your worksheets tabs should look like this
5. Add 3 Document Properties via the Document Properties panel.
Author: dylan.audo
Title: Excel Exam 1
Comments: location where you completed the exam.
Examples
Home: My Home PC
Cuyamaca College room E206 System 32: E206 System 32
Part 2 – Overview Worksheet – Enter and Format cells
6. Make the Overview worksheet the active worksheet.
Insert the header and footer elements in the header / footers areas as shown below.
Type your user name in the right side of the header where it says Your Name in the example.
Use the Excel built-in elements (highlighted in red) for the remaining header / footer areas.
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
2/9
3/4/2021
Excel Exam 1
An example of the easy way to add headers and footers is available on the Exam Videos page.
Example header / footer
7. In cell A1 enter Southwest Mini-Market #211
8. Merge and center the text in cell A1 across columns A to E
9. In cell A1 change the font size and background color to an appropriate title combination.
Similar to what you have done in your Simnet projects.
10. In the Overview worksheet cell A3 enter the following shown below.
Income
Interest
Sales
Total Income
Expenses
Mortgage
Payroll
Taxes
Insurance
Phone
Internet
Utilities
Advertising
Total Expenses
11. Increase the font size for Income and Expenses then indent the other entries except Totals.
12. Format the worksheet to make it look business like and professional.
You will complete this worksheet after you finish the Loan and Payroll worksheets.
Part 3 – Loan Worksheet – Calculate Payment
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
3/9
3/4/2021
Excel Exam 1
To add the Mortgage expense for the store we need to calculate the mortgage payment on
the Loan Worksheet and then add a reference to the payment amount on the Overview
worksheet.
A loan payment calculation was covered as a topic in Excel chapter 2.
Since this worksheet will have an area for user input and also an area where the calculated
results will be displayed you need to design the worksheet appropriately and may want to
refer to the Worksheet Design page to view example layouts.
13. In cell A1 of the Loan worksheet enter the text Loan Calculation.
14. Merge and center the text in cell A1 across columns A to E
15. In cell A1 change the font size and background color to an appropriate title combination.
16. Input area – Starting in cell A3 create the following. Use the following for your input area text
and values.
Store Cost – 2100 Cuyamaca St.
Down Payment
Annual Percentage Rate
721,100.00
32,100.00
3.125%
Loan Term – Years
30
17. Output area – select an appropriate area in the worksheet to enter formulas to calculate
the following for your output area calculations.
Loan Amount is the difference between the Store Cost and the Down Payment
Monthly Payment – using a function to calculate the payment. Payments are at the end of
the month and displayed as a positive value.
Total Cost of Loan – the total of all payments
Total Interest – the difference between the Loan Amount and Total Cost of Loan
18. Loan Amount
Monthly Payment
Total Interest
Total Cost of Loan
19. Create the workbook range name Loan_Payment using the monthly payment amount cell.
20. Format the worksheet with a business and professional look.
Self check. Change the Loan Term to 15 years. You should see the Monthly Payment, Total
Interest and Total Cost of Loan change. If any of those cells do not change then you have a
problem.
When finished checking change the Loan Term back to 30 years.
Part 4 – Monthly Payroll Worksheet – Add Employees and Calculations
You will calculate the monthly pay for your employees.
Since you have weekly hours you need to multiply this by 4 to get the monthly pay.
This assignment is a very simplified payroll example.
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
4/9
3/4/2021
Excel Exam 1
If you are interested you can download a full California example here however it is much more
complex than this assignment.
Here is the unformatted example
21. In cell A1 enter Monthly Payroll, then merge and center the text across columns A to J
22. In cell A1 change the font size and background color to an appropriate title combination.
23. Add the following columns for each employee starting in row 2 column A: Last Name, First
Name, Rate, Hours, Gross Pay, SS Tax, Fed Tax, State Tax, Insurance, and Net Pay
24. Add 12 employees starting in row 3.
for the first employee use your name last name in column A and your first name in column B
Add 11 names to complete the employee list (use any names you want).
25. Create pay rate data for each employee
In column C, enter the hourly pay in the Rate, using values between 15 to 50
26. Create hours data for each employee
In column D, enter values for weekly Hours using any value from 20 – 40 hours
27. In cell A15 enter Total to use as a total row below the employees.
28. Create a formula to Calculate the monthly Gross Pay
In column E, monthly Gross Pay is 4 times the weekly hours times the rate.
29. Add the following tables to the worksheet starting below your payroll data and calculations
Insurance Table
Health Insurance Premium
520.75
Hours for Health Insurance
30
Tax Rate Table
Employee Social Security
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
7.65%
5/9
3/4/2021
Excel Exam 1
Fed Income
15.00%
State Income
4.00%
Employer Social Security
7.85%
Calculations Table
Total Employee Insurance
Total Employer Social Security Tax
Total Monthly Payroll
30. Columns containing the SS Tax, Fed Tax, State Tax formulas use references to values in the
Insurance and Tax Rate Tables.
The calculated value is the Gross Pay times the specific tax listed in the Tax Rate Table. For
example the SS Tax is the Gross Pay times the Employee Social Security value from the
Tax Rate Table.
31. Use a Function to calculate the totals for the SS Tax, Fed Tax, and State Tax columns in the
Total row 15.
32. Calculate Insurance premium – If an employee works 30 hours or more then they will have the
insurance premium deducted from their pay. If they don’t work at least 30 hours then they do not
have insurance so there is not deduction from their pay.
Using an Excel function, create a formula to calculate the insurance in the Insurance
column for each employee.
Use the Hours column from payroll and the Hours for Health Insurance and the Health
Insurance Premium from the Insurance table in your function.
33. Net Pay – add a formula for this column which is the Gross Pay minus the SS Tax, Fed Tax, State
Tax, and Insurance.
(Self Check 1 – copying the formulas for Gross Pay, SS Tax, Fed Tax, and State Tax from
the first employee to all the rows below should give you valid values if you use the correct
type of cell references.)
(Self Check 2 – changing the Hours for Health Insurance to 0 in the Insurance Table for
should display an insurance premium value for all employees.)
Be sure to set the Hours for Health Insurance value back to 30 after checking
34. Stats – cells E15:E18, Use functions to calculate the Total, Maximum, Minimum, and
Average for column E, the Gross Pay column.
35. In the Calculations Table enter 3 formulas for:
Employer Social Security Tax which is equal to the Total Gross Pay times the Employer
Social Security Tax in the Calculations Table.
Total Employee Insurance which is equal to the total of the Insurance column in the
Calculations Table.
Total Monthly Payroll which is equal to the Total Gross Pay plus the Employer Social
Security Tax from the Calculations table.
36. Create a workbook range name Payroll_Total using the Total Monthly Payroll cell.
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
6/9
3/4/2021
Excel Exam 1
37. Freeze Panes so that only rows 1 and 2 plus column A are always visible when you scroll.
An example is available on the Exam Videos page.
38. Format the worksheet to make it look business like and professional.
Part 5 – Complete Overview Worksheet
39. Select the Overview worksheet you created in Part 2
40. Starting in row 3, enter the following in column B:
Income: Interest and Sales using the values given below
Expenses:
Mortgage, and Payroll using 3D references
Tax, which is equal to 26% of the Income Total
Insurance, Phone, Internet, Utilities, and Advertising using values given below
Note: the Tax and Insurance values here are for the business.
41. Income
Interest
119.03
Sales
54191.00
Total Income
Expenses
Mortgage
3D reference for Monthly Payment from Loan worksheet
Payroll
3D reference for Payroll Total from Payroll worksheet
Tax
formula for 26% of Income Total
Insurance
Phone
Internet
Utilities
Advertising
2112.50
187.22
95.86
518.24
1018.37
Total Expenses
42. Enter a formula to calculate the Total Income which is the sum of Sales and Interest
43. Enter a formula to calculate the Total Expenses which is the sum of all expense values
44. In cell A19 enter the text Net Income
45. In cell B19 enter a formula to calculate the Net Income by subtracting the total expenses from
total income.
46. Create a workbook range name Net_Income using the Net Income cell.
Part 6 – Create Expenses Chart
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
7/9
3/4/2021
Excel Exam 1
47. Create a 3D pie chart of the Expenses from the Overview worksheet excluding the Total.
48. Add a legend below the pie chart with text labels for each expense.
49. Add a chart title March 2021 Expense Analysis above the chart.
50. Add percentage data labels to the Outside End for each slice of the pie.
These should be the only data labels for the chart
51. Use the Move Chart command to move your chart to a new worksheet tab.
52. Change the tab name to Expenses Chart
53. Change the tab color as indicated below
Expenses Chart
Gold, Accent 4, Lighter 40%
54. Move the Expenses Chart tab so it is the last tab on the right
How is this assignment graded?
Upload the saved file as dylan.audo_Excel_Exam1 as the Excel Exam 1 assignment
using the Exam 1 link
100% penalty – files with circular references.
Why? – Circular references indicate there are calculation errors in your worksheet.
Resubmit – This assignment can corrected be resubmitted 1 time to improve your score if
submitted by the initial review date listed on the class web site.
If you decide to resubmit your file to improve your score then you must resubmit the
corrected file by the date listed on the class web site.
Grading Rubric
Points
Requirements
8
Workbook
file has required document properties
worksheets are formatted
worksheet tabs are in the correct order/color
correct range names used
6
Overview worksheet
includes Header & Footer elements
includes 3D referencesformula for calculating Taxes
correct formulas for Total Income, Total Expenses, Net Income
8
Loan worksheet
professional layout
correct formulas for Loan Amount, Total Interest, Total Cost of
Loan used correct function for Monthly Payment
includes range name
12
Payroll worksheet
correct formulas (average, minimum, maximum)
includes the 12 employees from Exam 1
used formula in employee insurance cells
calculated Total Employee Insurance
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
8/9
3/4/2021
Excel Exam 1
calculated Social Security Tax
calculated Total Monthly Payroll
includes range name
freeze panes so rows 1 & 2 and column A is always visible when scrolling
6
40
Expenses Chart
used 3D Pie Chart type
includes correct title
includes percentage data labels
moved chart to a new worksheet
Total possible for uploading – dylan.audo_Excel_Exam1
Drop Box – Upload Your Assignments
Updated 02/26/21
* Exams Home *
cis.cuyamaca.edu/draney/110/content/exams/Excel_Exam1_2021sp.asp
Reference ->
9/9

Purchase answer to see full
attachment

Order a unique copy of this paper
(550 words)

Approximate price: $22

Our Basic features
  • Free title page and bibliography
  • Plagiarism-free guarantee
  • Unlimited revisions
  • Money-back guarantee
  • 24/7 support
Our Options
  • Writer’s samples
  • Expert Proofreading
  • Overnight delivery
  • Part-by-part delivery
  • Copies of used sources
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

AcademicWritingCompany guarantees

Our customer is the center of what we do and thus we offer 100% original essays..
By ordering our essays, you are guaranteed the best quality through our qualified experts.All your information and everything that you do on our website is kept completely confidential.

Money-back guarantee

Academicwritingcompany.com always strives to give you the best of its services. As a custom essay writing service, we are 100% sure of our services. That is why we ensure that our guarantee of money-back stands, always

Read more

Zero-plagiarism tolerance guarantee

The paper that you order at academicwritingcompany.com is 100% original. We ensure that regardless of the position you are, be it with urgent deadlines or hard essays, we give you a paper that is free of plagiarism. We even check our orders with the most advanced anti-plagiarism software in the industry.

Read more

Free-revision guarantee

The Academicwritingcompany.com thrives on excellence and thus we help ensure the Customer’s total satisfaction with the completed Order.To do so, we provide a Free Revision policy as a courtesy service. To receive free revision the Academic writing Company requires that the you provide the request within Fifteen (14) days since the completion date and within a period of thirty (30) days for dissertations and research papers.

Read more

Privacy and Security policy

With Academicwritingcompan.com, your privacy is the most important aspect. First, the academic writing company will never resell your personal information, which include credit cards, to any third party. Not even your lecturer on institution will know that you bought an essay from our academic writing company.

Read more

Adherence to requirements guarantee

The academic writing company writers know that following essay instructions is the most important part of academic writing. The expert writers will, therefore, work extra hard to ensure that they cooperate with all the requirements without fail. We also count on you to help us provide a better academic paper.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2020 at 10:52 AM
Total price:
$26
The price is based on these factors:
Customer Academic level
Number of pages required
Urgency of paper