CIS 110 San Diego Mesa College Income Statement for Southwest Mini Market Excel Task
Start Excel and create a new blank workbook. Save your new workbook as _Excel_Exam1 (including the underscore characters)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, PayrollUsing the standard Office theme change the tab colors as follows:OverviewBlue-Gray Text 2, Lighter 60%LoanOrange Accent 2, Lighter 40%PayrollBlue Accent 1, Lighter 40%Office Theme ColorsOffice 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.Review - Your worksheets tabs should look like this Add 3 Document Properties via the Document Properties panel.Author:Title: Excel Exam 1Comments: location where you completed the exam.ExamplesHome: My Home PCCuyamaca College room E206 System 32: E206 System 32Part 2 - Overview Worksheet - Enter and Format cellsMake 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.An example of the easy way to add headers and footers is available on the Exam Videos page. Example header / footerIn cell A1 enter Southwest Mini-Market #211Merge and center the text in cell A1 across columns A to EIn cell A1 change the font size and background color to an appropriate title combination.Similar to what you have done in your Simnet projects.In the Overview worksheet cell A3 enter the following shown below.IncomeInterestSalesTotal IncomeExpensesMortgagePayrollTaxesInsurancePhoneInternetUtilitiesAdvertisingTotal ExpensesIncrease the font size for Income and Expenses then indent the other entries except Totals.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 PaymentTo 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.In cell A1 of the Loan worksheet enter the text Loan Calculation.Merge and center the text in cell A1 across columns A to EIn cell A1 change the font size and background color to an appropriate title combination.Input area - Starting in cell A3 create the following. Use the following for your input area text and values.Store Cost - 2100 Cuyamaca St.721,100.00Down Payment32,100.00Annual Percentage Rate3.125%Loan Term - Years30Output 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 PaymentMonthly 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 paymentsTotal Interest - the difference between the Loan Amount and Total Cost of LoanLoan AmountMonthly PaymentTotal InterestTotal Cost of LoanCreate the workbook range name Loan_Payment using the monthly payment amount cell.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 CalculationsYou 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.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 In cell A1 enter Monthly Payroll, then merge and center the text across columns A to JIn cell A1 change the font size and background color to an appropriate title combination.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 PayAdd 12 employees starting in row 3.for the first employee use your name last name in column A and your first name in column BAdd 11 names to complete the employee list (use any names you want).Create pay rate data for each employeeIn column C, enter the hourly pay in the Rate, using values between 15 to 50Create hours data for each employeeIn column D, enter values for weekly Hours using any value from 20 - 40 hoursIn cell A15 enter Total to use as a total row below the employees.Create a formula to Calculate the monthly Gross PayIn column E, monthly Gross Pay is 4 times the weekly hours times the rate.Add the following tables to the worksheet starting below your payroll data and calculationsInsurance TableHealth Insurance Premium520.75Hours for Health Insurance30Tax Rate TableEmployee Social Security7.65%Fed Income15.00%State Income4.00%Employer Social Security7.85%Calculations TableTotal Employee InsuranceTotal Employer Social Security TaxTotal Monthly PayrollColumns 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.Use a Function to calculate the totals for the SS Tax, Fed Tax, and State Tax columns in the Total row 15.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.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 checkingStats - cells E15:E18, Use functions to calculate the Total, Maximum, Minimum, and Average for column E, the Gross Pay column.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.Create a workbook range name Payroll_Total using the Total Monthly Payroll cell.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.Format the worksheet to make it look business like and professional.Part 5 - Complete Overview WorksheetSelect the Overview worksheet you created in Part 2Starting in row 3, enter the following in column B:Income: Interest and Sales using the values given belowExpenses:Mortgage, and Payroll using 3D referencesTax, which is equal to 26% of the Income TotalInsurance, Phone, Internet, Utilities, and Advertising using values given belowNote: the Tax and Insurance values here are for the business.IncomeInterest119.03Sales54191.00Total IncomeExpensesMortgage3D reference for Monthly Payment from Loan worksheetPayroll3D reference for Payroll Total from Payroll worksheetTaxformula for 26% of Income TotalInsurance2112.50Phone187.22Internet95.86Utilities518.24Advertising1018.37Total ExpensesEnter a formula to calculate the Total Income which is the sum of Sales and InterestEnter a formula to calculate the Total Expenses which is the sum of all expense valuesIn cell A19 enter the text Net IncomeIn cell B19 enter a formula to calculate the Net Income by subtracting the total expenses from total income.Create a workbook range name Net_Income using the Net Income cell.Part 6 - Create Expenses ChartCreate a 3D pie chart of the Expenses from the Overview worksheet excluding the Total.Add a legend below the pie chart with text labels for each expense.Add a chart title March 2021 Expense Analysis above the chart.Add percentage data labels to the Outside End for each slice of the pie.These should be the only data labels for the chartUse the Move Chart command to move your chart to a new worksheet tab.Change the tab name to Expenses ChartChange the tab color as indicated belowExpenses ChartGold, Accent 4, Lighter 40%Move the Expenses Chart tab so it is the last tab on the right How is this assignment graded?Upload the saved file as _Excel_Exam1 as the Excel Exam 1 assignment using the Exam 1 link100% 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 RubricPointsRequirements8Workbookfile has required document propertiesworksheets are formattedworksheet tabs are in the correct order/colorcorrect range names used6Overview worksheetincludes Header & Footer elementsincludes 3D referencesformula for calculating Taxescorrect formulas for Total Income, Total Expenses, Net Income8Loan worksheetprofessional layoutcorrect formulas for Loan Amount, Total Interest, Total Cost ofLoan used correct function for Monthly Paymentincludes range name12Payroll worksheetcorrect formulas (average, minimum, maximum)includes the 12 employees from Exam 1used formula in employee insurance cellscalculated Total Employee Insurancecalculated Social Security Taxcalculated Total Monthly Payrollincludes range namefreeze panes so rows 1 & 2 and column A is always visible when scrolling6Expenses Chartused 3D Pie Chart typeincludes correct titleincludes percentage data labelsmoved chart to a new worksheet40Total possible for uploading - _Excel_Exam1Requirements: excel13 mins ago