Problems: Spreadsheets #2
For this Problems assignment, you will continue to research your company and create a
spreadsheet. The following is a partial summary of the Checklist for Effective Business
Spreadsheets (at the end of the Professor’s Notes)
Your professor will be the intended audience, as the recipient of the Excelfile.
Your message will be to summarize an operational expense analysis for the Human
Resources Department for the First Quarter.
Your purpose is to create an Excel file using proper business formatting, adding
formulas/functions, use the loan amortization template, and insert achart.
The type of business communication will be a business spreadsheet. You will use Excel
2010 or Excel 2013(.xlsx) to create the spreadsheet.
The rules and formats for this type of business communication (business spreadsheet) are
in the Professor’s Notes.
Step A: Use the Excel Amortization Template
Assume your company took out a loan on May 1, 2013 during the fourth quarter of the last
Fiscal Year (ending June 30, 2013). The loan amount/principle was $2,500,000 for a period
of 15 years at 5% APR. Open the Excel loan amortization template and enter the loan
information. (Hint – go to File, New, Sample Templates, LoanAmortization)
Your company requires each department to include in its budgets a percentage of the loan
payment. Because the budget is analyzed by quarter, you need to calculate three months of
loan payments. Once you calculate this number multiply it by 20% (as your department
share of the loan payment). You will enter this amount during StepB.
Step B: Operational Expense Budget
Open an Excel 2010 (xlsx) file, and name it SectionNumber_LastName_Spreadsheets_2 (For
example, the file name would read –111090_Morris_Spreadsheets_2.xlsx)
Name the first sheet, Q1
In the first sheet, type the following data from the screen shot below. Note that you do not
type the text in red font color; this indicates where you need to add a formula, function, or
data from the loan amortization.
Add the amount from Step A in B31 and C31.
Add the appropriate business formatting per the screen shot below (e.g., font/font size,
Merge and Center, company logo, Wrap Text, bold, indents, borders, number/comma style,
and percent style.
Review the sample for this assignment to help you visualize thesteps.
Problems_Spreadsheets_2_Instructions Page 2
Add the functions and formulas where indicated in red. Note that if a cell has “Addfunction”
you cannot use a formula instead.
Hint – The formulas for column D will show a negative number if the Actual came in less
than the Budget amount.
Hint – The formulas for column E will show a negative percent if the “Increase or
Decrease from Budget” is a negative number.
Hint – The formula for B9 and C9 will calculate the employer’s percentage rate for Social
Security plus the employer’s percentage rate for Medicare on the Employer FICA Taxes;
use a hard-coded percentage.
Format the sheet per the business rules.
Add a pie chart that shows the Budget for Q1 Total and the Actual for Q1 Total. Keep the
chart as an object. Move it underneath the Note in A34 and resize so it this sheet fits on one
page. Format the chart per the business rules.
Set the page layout to center on page, horizontally.
You need to create a chart for the following subtotals: Total Payroll – Budget and Actual;
Total Fringes – Budget and Actual; Total Travel – Budget and Actual; Total ProfessionalFees
– Budget and Actual; Total General – Budget and Actual; and, Loan Portion – Budget and
Actual). Select the correct cells/ranges and select an appropriate chart type.
Problems_Spreadsheets_2_Instructions Page 3
Save this chart as a new chart sheet, Expense Chart. (If you accidentally add it to the Q1
sheet, move it to a new sheet.)
Format the sheet per the business rules.
Make sure that the first sheet tab is Q1, followed by the sheet tab, ExpenseChart.
For both sheets, add a footer with the Sheet Tab name in the left side and the following text
in the right side – Created by add your name.
Delete the unused sheets
Step C: Submit your work
Click Problems: Spreadsheets #2
Click the “Start a New Thread” button.
Click on “Problems” rubric to determine how you will be graded.
Type a business appropriate subject
Add an appropriate business message.
Click Add
Click the Upload button to attach your file.
Verify you now see your file name.
Click the “Post” button.
Step D: Discussion
For sections meeting on campus, we will discuss and evaluate your work during class with
other students.
For web sections, you will review the other students’ threads to evaluate your own work.
Step E: Editing your work
Click on — Problems: Spreadsheet #2.
When you enter the topic, find your own thread.
Click on your topic title.
Click –“ Reply to Thread”
Add a descriptive subject, like — MyEdits
In the HTML editor area, click the unordered drop down arrow .
Choose Ordered List (e.g. 1, 2, 3) to start automatic numbering. Describe in detail at least
10 edits/revisions that you need to make to your file. If you determine you have no
revisions, please provide a summary of what you learned from the week? Was the
assignment hard? Was the assignment easy? From answering the questions, you must have
at least 250 words.
To identify the word count, you will have to type your response in a word processor (e.g.
Microsoft Word) to determine the word count. Then, copy your answer in the
myLearning HTML text editor.
Click the “Post” button.