I have an exam due for CIS 110 principal information systems, the exam is on microsoft excel and I have every step listed below

Anonymous
timer Asked: Mar 20th, 2019
account_balance_wallet $30

Question Description

Part 1 - Create a new Workbook for the exam

  1. Start Excel and create a new blank workbook. Save your new workbook as firstname.lastname_Exam3Files not created in Microsoft Excel 2016 may not earn full credit.
  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:
  4. Overview Blue-Gray Text 2, Lighter 60%
    Loan Orange Accent 2, Lighter 40%
    Payroll Blue Accent 1, Lighter 40%
    Office 2016 Theme ColorsFill Colors
    Office 2016 Theme ------->Office 2016 Themes
  5. Review - Your worksheets tabs should look like this worksheet tabs
  6. Add 3 document properties via the Document Properties panel. Author: Title: Exam3Comments: location where you completed the exam examples
    • Important! : Your location in the comments must match the location you submit you file from or you will have a deduction.
    • If you completed it at home then list - "my home PC"
    • If you complete it on campus then list the room and computer number using room E206 system 32 would be entered as - "E206 system 32" using college computers - "Cuyamaca Tech Mall system 206" or Grossmont Lab system 308

Part 2 - Overview Worksheet - Enter and Format cells

  1. Make the Overview worksheet the active worksheet
  2. 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.An example of the easy way to add headers and footers is available on the Exam Videos page.Header / FooterExample header / footer
  3. In cell A1 enter Southwest Mini-Market #183
  4. Merge and center the text in cell A1 across columns A to E
  5. Change the font size and background color of cell A1 to an appropriate combination for a title.
  6. Enter the following into the Overview worksheet starting in cell A3.
    Income
    Interest - Checking
    Sales
    Total Income
    Expenses
    Mortgage
    Payroll
    Taxes
    Insurance
    Phone
    Internet
    Utilities
    Advertising
    Total Expenses
  7. Change the font size for Income and Expenses then indent the other entries except Total.
  8. 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

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.
This type of 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.
  1. Enter the text Loan Calculation in cell A1
  2. Merge and center the text in cell A1 across columns A to E
  3. Change cell A1 font size and background color to an appropriate combination for a title
  4. Input area - Starting in cell A3 create the following. Use the following for your input area text and values
    Store Cost - 9100 Cuyamaca St. 618,300.00
    Down Payment51,100.00
    Annual Percentage Rate4.25%
    Loan Term - Years 30
  5. Output area - select an appropriate area to enter formulas to calculate the following for your output area values.
    • Loan Amount is the difference between the cost of the store and the down payment
    • Monthly Payment - payments are at the end of the month and displayed as a positive value.
    • Total Cost of Loan which is the total of all payments
    • Total Interest which is the difference between the Loan Amount and Total Cost of Loan
  6. Loan Amount
    Monthly Payment
    Total Interest
    Total Cost of Loan
  7. Create a range name for the Workbook using the monthly payment amount with the name Loan_Payment.
  8. Format the worksheet to make it look business like and professional.
    • 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 them stay the same 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 will need to multiply this by 4 to get the monthly pay.
  • This assignment is a very simplified payroll example.
  • If you are interested you can download a full California example here however it is much more complex than this assignment. This is not needed or required for the exam.
  • Here is the unformatted example Unformatted Example
  1. Enter Monthly Payroll in cell A1, then merge and center the text across columns A to J
  2. Change the font size and background color of cell A1 to an appropriate combination for a title
  3. Add the same 12 employees used in Exam 1 by adding their last name in column A and first name in column B with the column titles in row 2.
  4. Add a Total row below the employees.
  5. Add the following columns for each employee starting in row 2 column C: Rate, Hours, Gross Pay, SS Tax, Fed Tax, State Tax, Insurance, and Net Pay - Example Use the same Pay Rate you entered for your employees in Exam 1
  6. Enter values for Hours in column D with the following guidelines: Make up the weekly hours for each employee using any value from 20 - 40 hours
  7. Enter a formula in column E to calculate the monthly Gross Pay amount for each employee.
  8. Add the following tables to the worksheet starting below your payroll data and calculations
    Insurance Table
    Health Insurance Premium520.75
    Hours for Health Insurance30
    Tax Rate Table
    Employee Social Security 7.65%
    Fed Income 15.00%
    State Income 4.80%
    Employer Social Security 7.85%
    Calculations Table
    Total Employee Insurance
    Total Employer Social Security Tax
    Total Monthly Payroll
  9. Use references to values in the Insurance and Tax Rate Tables in formulas for the SS Tax, Fed Tax, State Tax columns where the calculated value is the Gross Pay times the tax.
  10. Use a Function to calculate the totals for the SS Tax, Fed Tax, and State Tax in the total row.
  11. Employees who work 30 hours or more will have the insurance premium deducted from their pay. Add a formula to calculate the insurance in the Insurance column for each employee based on the value in the Hours column and the Hours for Health Insurance in the Insurance table.
  12. Add a formula to calculate the Net Pay 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 done correctly.)
    • (Self Check 2 - changing the value in the Insurance Table for Hours for Health Insurance to 0 should display the insurance premium for all employees.) Be sure to set the value at 30 after checking
  13. Use functions to find Gross Pay column values for Payroll Total, Maximum, Minimum, and Average.
    • Place the formulas under the Gross Pay column values.
  14. Add row titles for your functions to clearly identify the Total, Maximum, Minimum, and Average values.
  15. Enter a formula for the Employer Social Security Tax which is equal to the Total Gross Pay times the Employer Social Security Tax in the Calculations Table.
  16. Enter a formula for the Total Employee Insurance which is equal to the total of the Insurance column in the Calculations Table.
  17. Calculate the Total Monthly Payroll which is equal to the Total Gross Pay plus the Employer Social Security Tax in the Calculations Table.
  18. Create a workbook range name for the value Total Monthly Payroll named Payroll_Total.
  19. Freeze Panes so that only rows 1 and 2 plus column A are always visible when you scroll. There is an example on the Exam Videos page if you forgot how to do this.
  20. Format the worksheet to make it look business like and professional.

Part 5 - Complete Overview Worksheet

  1. Select the Overview worksheet
  2. Enter the text in column A and the values, formulas, or 3D references in column B of your worksheet.
    • Note: the Tax and Insurance values here are for the business.
  3. Income
    Interest - Checking 319.03
    Sales 64191.00
    Expenses
    Mortgage3D reference for Monthly Payment from Loan worksheet
    Payroll3D reference for Payroll Total from Payroll worksheet
    Taxformula for 26% of Income Total
    Insurance 1622.50
    Phone 187.22
    Internet 121.86
    Utilities 418.24
    Advertising 1218.37
  4. Enter a formula to calculate total income, which is the sum of Sales and Interest
  5. Enter a formula to calculate the total expenses to total all the expense values
  6. In cell A19 enter the text Net Income
  7. In cell B19 enter a formula to calculate the Net Income by subtracting the total expenses from total income.
  8. Create a range name for the Workbook using the net income value with the name Net_Income.

Part 6 - Create Expenses Chart

  1. Create a 3D pie chart of the Expenses from the Overview worksheet excluding the Total
  2. Add a legend below the pie chart with text labels for each expense.
  3. Add a chart title March 2019 Expense Analysis above the chart.
  4. Add percentage data labels to the outside end for each slice of the pie.
    • These should be the only data labels for the chart
  5. Use the Move Chart command to move your chart to a new worksheet tab.
  6. Change the tab name to Expenses Chart
  7. Change the tab color as indicated below
    Expenses Chart Gold, Accent 4, Lighter 40%
  8. Move the Expenses Chart tab so it is the last tab on the right worksheet tabs

How is this assignment graded?

Upload the saved file as _Exam3 as the Exam 3 assignment using the Exam 3 link
100% penalty - files with circular references.
Why? - Circular references indicate there are calculation errors in your worksheet. These should be corrected before submitting.
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 references

formula 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

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

Expenses Chart

used 3D Pie Chart type

includes correct title

includes percentage data labels

moved chart to a new worksheet

40Total possible for uploading - _Exam3
Drop Box - Upload Your Assignments

Tutor Answer

mickeygabz
School: Cornell University

See attached

20190320231600yousif.mikha_exam3.xlsx

Overview

Yousif.Mikha

Southwest Mini-Market #183
Income
Interest - Checking
Sales
Total Income

$
$
$

319.03
64,191.00
64,510.03

$
$
$
$
$
$
$
$
$

2,790.28
61,970.61
16,772.61
1,622.50
187.22
121.86
418.24
1,218.37
85,101.69

Expenses
Mortgage
Payroll
Taxes
Insurance
Phone
Internet
Utilities
Advertising
Total Expenses
Net Income

3/21/2019

$ (20,591.66)

12:16 AM

1

Loan Calculation
Store Cost - 9100 Cuyamaca St.
Down Payment
Annual Percentage Rate
Loan Term - Years

Loan Amount
Monthly Payment
Total Interest
Total Cost of Loan

$618,300.00
$51,100.00
4.25%
30

$567,200.00
$2,790.28
$437,301.90
$1,004,501.90

Monthly Payroll
Last Name
Mikha
Doe
Doe
Smith
Cole
Hartley
Michelson
Cardozo
Miller
Jean
Smith
Leon
Total
Average
Minimum
Maximum

First Name
Yousif
John
Jane
Jack
Harry
Lauren
Donna
Oscar
Mike
Billy
Mary
Sergio

Insuran...

flag Report DMCA
Review

Anonymous
Goes above and beyond expectations !

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors