In this activity, you will prepare a financial report. For this, you will first read the following scenario and then complete the steps given below.
Benjamins Machinery Inc. (BM) is a small manufacturing company located in Seekonk, MA. The owners are working on the companys annual financial reporting. The first financial report is an MS Excel worksheet that compares the company’s balance sheets for the previous two years. The report form already has the labels entered for the required line items.
The owners have asked you to complete the report by entering the numeric data and the formulas to perform the financial calculations. Complete the following steps:
1. Open the Balance workbook.
Select the following link to access the Balance workbook.
[XLS file size 12KB]
In the file, you will find two worksheets. The first is labeled Documentation and the second is labeled Balance Sheet. Save the workbook as Comparative in the folder where you are storing your files.
2. In cells B3 and B4 of the Documentation sheet, enter your name and the date. In cell A1, increase the font size of the title to 28 points.
3. The remaining instructions pertain to the Balance Sheet Worksheet. Click on the Balance Sheet worksheet tab. Increase the font size of the title in cell A1 to 28 points. Increase the font size of the subtitle in cell A2 to 20 points and change the word “Document” to “Comparison” in the subtitle.
4. In the corresponding cells of columns C and D in the Balance Sheet, enter the numbers for the companys assets and liabilities.
RowColumn CColumn D 4 627,858645,7855405,811 431,9826395,648 417,61574,151 2,1521026,298 31,252 12 1,750,0001,800,000 1377,93982,164 19 193,644241,191 2032,151 31,115 21100,000 120,000 22 126,524144,135 25850,000 815,000 271,525,000 1,560,00028304,508 335,181
5. Set the widths of column A to 12.00, column B to 28.00, columns C and D to 14.00 each, column E to 2.00, and column F to 10.00.
6. In cells C8 and D8, enter formulas to calculate the Current Assets value for LAST YEAR and THIS YEAR. It is equal to the sum of the cash, accounts receivable, inventories, and prepaid expenses values of corresponding columns (hint: use the SUM function).
7. In cells C14 and D14, enter formulas to calculate the Net Fixed Assets value for LAST YEAR and THIS YEAR. It is equal to the difference between the fixed assets value and the accumulated depreciation values of the corresponding columns (hint: SUM[C12, -C13]).
8. In cells C16 and D16, enter formulas to calculate the TOTAL ASSETS value for LAST YEAR and THIS YEAR. It is equal to the sum of the current assets, other assets, and net fixed assets values of the corresponding columns.
9. In cells C23 and D23, enter formulas to calculate the Current Liabilities value for LAST YEAR and THIS YEAR. It is the sum of the accounts payable, accrued expenses, current portion of debt, and income taxes payable values of the corresponding columns.
10. In cells C29 and D29, enter formulas to calculate the Shareholders Equity value for LAST YEAR and THIS YEAR. It is the sum of the capital stock and retained earnings values.
11. In cells C31 and D31, enter formulas to calculate the TOTAL LIABILITIES & EQUITY value for LAST YEAR and THIS YEAR. It is the sum of the current liabilities, long-term debt, and shareholders’ equity.
12. In a balance sheet, the TOTAL ASSETS should equal the TOTAL LIABILITIES & EQUITY. Compare the values in cells C16 and C31, and then compare the values in cells D16 and D31 to confirm that this is the case for the Benjamins Machinery Inc. balance sheet in LAST YEAR and THIS YEAR. If the values are not equal for either year, check your worksheet for errors in either values or formulas.
13. In cell F4, enter a formula using cell references to calculate the percentage change in cash from LAST YEAR to THIS YEAR, which is equal to: (THIS YEARs value minus LAST YEARs value and then divide the result by LAST YEARs value).
14. Copy the formula in cell F4 and paste it in the nonadjacent range F5:F8; F10; F12:F14; F16; F19:F23; F25; F27:F29; F31 to show the percentage change in all values of the balance sheet.
15. Add borders around the cells in columns B, C, D, and F of the balance sheet, excluding the cells in rows 1, 2, 9, 11, 15, 17, 18, 24, 26, and 30.
16. Merge and Center the Title and subtitle using columns A through F. Then right justify the cells C3, D3 and F3.
17. Display the formulas in the Balance Sheet worksheet, and then set the gridlines and row/column headings to print. Printing the worksheet is not required.
18. Display the Balance Sheet worksheet in Normal view, hide the formulas, set the gridlines and column/row headings so that they won’t print, and then save the workbook.
Once you have successfully completed all of the steps in this assignment, post your Comparative workbook.
Compose your work in a .xls or .xlsx file type using Microsoft Excel and save it frequently to your computer. Please follow uploading guidelines provided by your instructor.
Check your work and correct any spelling or grammatical errors. When you are ready to submit your work, click Submit Assignment in the upper right corner. Click on Browse, browse your computer, and select your file. Click Open and verify the correct file name has appeared next to the Browse button. Enter your comments, if any, in the Comments area. Click on Submit Assignment.
Evaluation
This assignment will be graded using the rubric displayed below. Please review this rubric prior to beginning your work. You can also access the rubric on the Course Rubrics page within the Start Here module. All assignments, together, are worth 40% of your final course grade.
