ACTG 2p21 Spring 2021

Spreadsheet Assignment 1

Purpose: To build spreadsheet design and presentation skills, and begin learning how to prepare spreadsheets for others to use. You will not be able to complete this quickly, but you should complete it on your own. HOWEVER, you may talk to others in your class and both ask and answer ANY questions of ANY kind related to this project. When I say "complete it on your own" I mean that you need to make all final decisions and do all the typing on your spreadsheet. If you and a friend prepare 1 spreadsheet and make copies to turn in, that would constitute plagiarism. If you sit with a friend and talk about the project, but each do your own typing and final decisions, that would not constitute plagiarism for this assignment. In Spring 2016, 10 students were caught and penalized for plagiarism on one of the spreadsheet assignments with a grade reduction and a record in their academic transcript. Although that is the most in one year, similar incidents have occured in recent years.

The following exhibit presents data about the operations of the Brock Manufacturing Company for the year 20x5. Brock maintains separate accounts for direct and indirect material within raw materials inventory. Here is an example of a different company and its 2-schedule Income statements in this file. Brock Manufacturing staff has previously prepared their income statements using the 2-schedule approach (Income statement and separate COGS schedule), however the CEO feels that some changes are needed. Brock was recently acquired by an international company with different accounting presentation policies. Your boss asked you to prepare a spreadsheet to automate the preparation of the financial statements using both the single-schedule format (COGS inside the Income Statement) approach as well as the acquiring company's methods. Having both statements will allow Brock employees to compare the methods.

The acquiring company uses IAS #2 standards for measurement of inventory and classifies expenses based on nature rather than function, so your boss wants two statements: One based on traditional single statement presentation and one based on the classification by nature [that is, no cost of goods sold or cost of goods manufactured].

This spreadsheet will be used each period and for different divisions [Brock Manufacturing is not the only division]. The new parent company is EuroManufacturing Inc. Because of the desire for re-use you must make the spreadsheet as generic as possible. In other words, your data, computation and output pages cannot be too specific to this division [Brock Manufacturing Company, but must make sense when different data is imported from different divisions. The company recently adopted the Spreadsheet Guidelines as official company practice and you completed training last month.

Using Microsoft Excel, complete the spreadsheet by carrying out the following activities:

Prepare a job control checklist. Do this first. You need to copy this page as well as any problem requirements from the book as well as anything I say in class and convert it to a checklist. It is best to reduce the items to short phrases instead of complete sentences. Do not turn in the assignment unless all items are completed. Complete this before doing anything listed below. This checklist is part of the assignment and is turned in with the spreadsheet.
Open a new spreadsheet and name it Yourlastname_Yourfirstname-SS1.xlsm [the m means it will have macros]
Set up the spreadsheet in accordance with the design guide.
Create and fill in the Index area (including the introduction),
Download the file SS1data.txt using "File-Save as", and open it with notepad [look in Accessories]. Observe the lack of formatting. This is the type of file that is saved from data-focussed computer systems [probably more than 50% of the systems you have seen.
In Excel, read the help file information on Importing Data.
Place your new excel file and the SS1data.txt file in the same subdirectory [folder] and import the data onto your data sheet using the Data Import process [Data | From Text] to import data from a text file. Note that SS1data is tab delimited and each line contains a different number of tabs.
After you have gotten the Data Import routine to work, prepare a macro to run the Refresh command [a part of the Data Import process: Data | Refresh All]. You want to be able to import next month's data with one button click. There are instructions for creating macros in the design guide.
The problem requirements are to be carried out in the Output sections. You need to prepare two sets of financial statements. One set is as shown by your professor using the single statement method. The second set is based on IAS #2 using the classification by nature. For this spreadsheet there are no formulas related to the financial statements in the Computations page. All work can be done on the Output pages.
On the computations page you need to create some "filters" to detect errors in the data import procedure. This is because it is possible that a later user could be sent the wrong file and import it without noticing that the data was not correct. On the computations page you can use excel functions to check to see if there are numeric and character values in the correct places, including checking several rows below the expected data. Excel has logical functions you can use to test each cell for numeric or text data. If your functions detect an error, you must have the function type "IMPORT ERROR" in large red letters on the output page to notify the user that the data has been imported incorrectly.
In the index area add a button next to each element which is connected to a macro. The macro should be to "go to" the cell listed in the index.
In the index area add another button next to the "go to" button which is also connected to a macro. The macro should be to print the area listed in the index.
Do the same for the data, computations, and output areas.
Protect any cells that would not normally be changed during use (do not use password protection).

Turn in the following:

Upload a copy of your Excel file to Sakai. Include your checklist as a tab in the file.

· Grading will focus on the completion of the professional assignment and delivery of a professional product. Marks will be deducted for anything that would detract from the credibility of the document package in a professional setting. Look-and-feel in the sense of colour are not part of this assignment, however a professional appearance within the scope of ordinary software and hardware available to students are the expected minimum. Printing must be dark and clear. Anything you turn in will be marked, so if you use color I will give a mark on your aesthetic choice of color as well as its ability to transfer to greyscale properly.