Most employers expectbusiness graduates to be Excel proficient, so it is vital that you learnto use this tool effectively during your time at . Whilst many of you may already use Excel, others might have limited knowledge of the software. Excelis a core instrument in the arsenal of all business professionals, and this project is a self-directed learning exercise that introduces you to how a spreadsheet can be used to calculate, model, and present complex financial information.
Your instructor will not giveyou a ‘lecture’ on how to complete this project but may provide advice and direction if you encounter difficulties. Excel knowledge and proficiency primarily comes from using and learning the package, but there are many useful YouTube videos and Excel sites that provide illustrative examples of how to use Excel. If you get ‘stuck’ on the basic functionality of Excel explained with this assignment briefing, consult the help facility in Excel or use Google to find a solution.
This Excel project testsyour basic accounting knowledge by asking you to prepare the periodic management accounts for Rainbow Manufacturing, a manufacturing entity. You will use some basic Excel features (formula, formatting options, comment boxes and worksheet links) to prepare the followings accounting statements for Rainbow Manufacturing:
See the relevant chapter of your textbook and the instructions provided in this document for illustrative examples of the format and layout for these accounting statements.
Description of the Project:
Open a new Excel workbook. At the bottom are tabs that reference the available “sheets”. Each new Excel workbook contains three worksheets, labeled Sheet1, Sheet2 and Sheet3. To add additional sheets, go to the Insert menu and choose the Worksheet option. This will add a sheet to the left of the active sheet (the sheet you are currently in). Another way to add a sheet is to left click the “+” icon next to “Sheet3”.
To make the sheet names more useful, double click on the tab for Sheet1. This will highlight the current sheet name and allow you to type over it. Rename the first worksheet to “Data”.
Now enter all the question data for the company in the Data worksheet as shown above. When doing this, do not “hard code” the sub-totals for inventories or costs, use Excel’s =sum formula to do this. You can include a cell reference in a formula by typing the = sign and then highlighting, (moving the cursor) to the cell you want to reference and hitting enter, or any other mathematical function key (+, -, *, /) to continue the equation.
The next screenshot shows the results of the formulae in cell’s B11 and C11 to calculate the total inventories (press Ctrl ~ to show formulae instead of values, repeat this key sequence to restore the values). Alternatively, the formulae in cell B11 could have been entered as “=B5+B7+B9”. You can also select “Show Formulas” from the Formula tab to toggle between the values and the formulas.
Once the basic data has been entered, you need to add an additional columns (shown as green in the snap shot below)in the Data sheet to calculate different categories of costs and expenses. The company has factory overhead, selling, administrative, and finance costs, which need to be sorted and categorized by adding five new columns to your Data worksheet. Apart from the headings for each column, thesecalculations will require us to input formulas that reference the cells that contain the original costs. Here is what the additional columns should look like when you are done:
The formulas needed to create this section appear below:
Hopefully, you can see that you only need to enter the numeric figures once, Excel cell references and formulae do the rest of the hard work for you. If the total of your factory overhead, admin, selling, and finance costs does not equal $765,000, you have done something wrong.
Sometimes it is helpful to print a copy of your data section with the column and row headings. This allows you to see the cell references for data items and eliminates the need to move back and forth between sheets. To print the row number and column letters select the “page layout” option in the file menu and then select “print titles”. Tick the box for row and column headings, as shown below.
It is important to print titles, dates and page numbers on printed documents. If you click the ‘page layout’ tab on the ribbon and select ‘print titles’ you can specify what to print in the header and footer of each worksheet. In the Data worksheet, add the following ‘custom footer’ via the ‘print titles’ dialogue box:
We can now use the data you entered in the Data worksheet to create a cost of goods manufactured statement (COGM) for RainbowManufacturing. Rather than creating this in the Data worksheet, create a new worksheet and name it “COGM”.
We will now create a cost of goods manufactured statement in worksheet “COGM” using information from our “Data” worksheet. The problem is that we have to switch the view from one worksheet to another and back again. Excel allows you to view multiple worksheets at once. Click on “view” and select “new window”. Then select “view side by side” in the view menu. You should now be able to simultaneously view two worksheets and can adjust the position and size of each window. Play around with this.
In the COGM worksheet, construct the COGM statement using an appropriate layout. In order to use Excel effectively, you must use formulas to link data from each worksheet. As we wish to use information from the Data worksheet in the COGM worksheet, we must includelinking formulae that allow us to subsequently update the numbers in the data section during future periods. Remember the total factory overhead cell we calculated in cell D30 (renamed as “FOH”, explained in instruction # 5) of the Data sheet? We can link to this calculation in our COGM sheet, thus saving the need to recalculate it again, and if it total changes next year, we only need to update the data sheet amounts.
|Linked to Cell Data!D30 which is renamed as FOH.|
When creating a formula that references a cell from another worksheet, you need to include an exclamation point for Excel to recognize the reference as a sheet reference. For example, the formula to link the Beginning RM inventory amount in cell B5 in the COGM sheet that are contained in the data sheet in cell B5 would appear as: = data!B5
Alternatively, you can directly press “=” in cell B5 in the COGM sheet and then directly selecting the cell B5 in the Data sheet to link the amount for Beginning RM inventory.
When you have finished your COGM statement should look like this:
If you linked the two worksheets correctly, changes to the numeric cells within the Data worksheet will be automatically updated in the COGM worksheet calculations. Do not hard code any data within the COGM worksheet – draw on the existing data sheet using linking formulae. Test your formula.
You should now have an appropriately linked COGM worksheet that reports a cost of goods manufactured of $1,418,000 for the year ended June 30, 2020. Check your work before proceeding to the next section.
If you examine cells B8 and D15 of yourCOGM worksheet, you may notice that they display negative numbers in a non-accounting format. What we desire is to show the negative numbers in parentheses and we can achieve that by using a custom number format.
Click on the ribbon and select “Home” and then look for the “format” button under cells. Alternatively, you can select the cell you want to change and right click the mouse. Once at this point, select “format cells”, “number” and then “custom”. Type the following entry into the type dialogue box:#,##0_);(#,##0)
This will create a custom number format that puts negative numbers in parentheses. It also shows a cell that contains the figure “0” as “-“. This format can be copied to other cells using the format painter, just like in Microsoft Word. Once you have entered the custom number format in cell B8, you can apply it to the rest of your worksheet. Select the block of cells you want to format and then right click the mouse. Select “format cells” and “number”, and then select the custom format you created. You will find it at the end of the list of custom number formats.
You can highlight key information within a spreadsheet by adding notes that provide the user with a summary explanation of cell content or references. Notes may be used to specify where the information for a calculation has been referenced from, or where that cell value is used within other computations. To add a note to the active cell, right click the mouse and select “New Note”. Select C11 of the COGM worksheet and add the comment as shown below. This will produce a text box within which you can type a message. The box maybe moved and the size changed by clicking on the box and using the mouse.ogm
You should make ensure that notes are always visible and printed with your work.
To ensure that notesare constantly displayed – select the cell that the comment reports on (i.e. the cell not the comment box), right click your mouse and select “show/hide note”.
To print notes, go to “page layout” and select the “print titles” option in the menu. Choose the “comments” drop down tab and select the option you want. You will need to go to page setup for each worksheet you are using.
Sometimes it is helpful to name cells. For example, when you are working with a large spreadsheet and you need to find information quickly. Instead of scrolling through rows and columns of data, you can use the GoTo function by pressing F5 and selecting the cell name you are trying to use.
To name a cell,select the cell you wish to name, and then click on the area that displays the cell column and row number (see the top left-hand corner of the Excel ribbon). In the screen below,cell E30 of the Data worksheet calculates the total factory overhead, so should be renamed as “FOH”. In a similar way, Cell D16of your COGM sheet calculates the cost of goods manufactured, so should be renamed as “COGM”. You should also rename C9, the cost of raw materials used cell, to “RM”, and C10, the direct labor cost as “DL” in COGM worksheet in the same way.
To navigate to the named cell, hit the F5 key. This will display the GoTo box. You can either highlight a cell in the listor key in the cell name in the box. You may also click on the down arrow key next to the name box. This will bring up a list of cells that have been named. Highlight the cell name that you want to move to and click on it and the cursor will move to that cell. You can also use cell names when entering formulae, which makes it easier to understand the calculation being performed by Excel. If you make a mistake and name the wrong cell, you can edit cell names from the name manager under “Formulas” and “Name Manager” on the ribbon, as shown below:
We can use data within the COGMworksheet to produce a calculation of cost of goods sold for the company. Create a new and separate worksheet entitled “COGS” for preparing a statement of cost of goods sold (COGS) in a similar manner to before. Ensure that you use formula references to link this new worksheet with your earlier calculations. Do not hard code any cost data, refer to the data sheet using linking formulae. If you do not remember the layout for the COGS, consult your textbook or lecture notes.
The formulae used in the COGS worksheet are shown on the next page:
Also rename the cell B9 to “COGS” as shown earlier above. We can now use the COGM and COGS statements to prepare an income statement for the year ended June 30, 2020. Construct this in a new worksheet called ‘income’ using the procedures we used above. The income statement should use an appropriate presentation style that separates yearly expenses by function:
Now that you have successfully completed the “data”, “COGM”, and “COGS” worksheets, you should have accurate calculations for the total expenses in each functional category. All that remains is to integrate these calculations in the income statement using linking formulae. As a check, if you include all of the entity’s COGS, selling, administrative, finance, and income tax expenses, the income statement should report a net income for the year of $288,000, if 20% tax is charged on the $360,000 of income before tax. These figures are shown below:
Here are the basic formulae used to construct the income statement.
While the tax calculation formula used in cell C16 provides the correct tax amount based upon the 2020 data, it will report an incorrect tax expense should the company’s income before tax exceeds $400,000 in future years. We will need to address this problem with the use of Excel’s IF statement function before moving on, as the tax rate could be 20% or 30% depending on whether the reported income is less than $400,000 or at least/more than $400,000 respectively.
Before moving on, rename the following cells:
Rename cell C12 to “OI” (i.e. operating income)
Rename cell C15 to “IBT” (i.e. income before tax)
Rename cell C17 to “NI” (i.e. Net income)
Do not rename the row labels in column A, rename the cells that hold the formula calculationsfor income before tax and net income amounts within the income statement. If you do not rename these cells correctly your IF statements in section 6 will show an error.
The use of “=IF” statementsallows the spreadsheet to perform different calculations or processes depending on the criteria, or conditions present. The function takes the form:
=IF (conditional argument, to do if argument is true, to do if argument is false)
Excel checks the validity of the conditional argument to see if it is true or false. If the condition is true, then Excel will perform the instructions within the commas immediately following the conditional argument. If the condition is false, it will skip the first set of instructions and follow the last set of instructions.
A conditional argument relies on the following arithmetic operators: =, >, <, >=, <=, <>
The “to do” statements may be a series of calculations or a “label”. It can take some time to construct an =IF statement. You must convert what you are doing into a series of logical statements that Excel can interpret. =IF statements can also consider multiple conditional arguments with the use of “AND” and “OR” options.
=IF(AND(B5=1,G15>7),B5,G15) [these cell references are just an example and do not refer to any of the cells or data in this question].
This statement requires that both conditions be met. Cell B5 must equal 1 and cell G15 must be greater than 7 for the cell to take the value in B5. If both are not met, then the cell will take on the value in G15.
With the OR statement only one of the two conditions need be met for the cell to take on the value in cell B5. If neither is met than it will take the value in cell G15. For AND, all statements would have to be met, with OR only one would need to be met. The use of imbedded =IF statements along with the AND and OR options allows for very complicated logical conditions to be used.
This project includes two tax rates, 20% if income before tax is less than$400,000 or 30%, if income is $400,000 or more. Excel can calculate the income tax using an =IF statement to determine which level of income has been reached. Excel interprets the =IF statement as follows: is the income before taxes in cell C15 less than the cutoff amount of $400,000?If yes, Excel will multiply C15 by the lower 20% tax rate included in your data sheet. If no, Excel will multiply C15 by the30% tax rate.
=IF(C15<$400,000,C15*0.20,C15*.30) (literally this reads: If C15 is less than $400,000, multiply C15 by 20%, otherwise multiply C15 by 30%).However, as you should avoid hard coding values in an Excel formula, you should not use the 20%, 30%, and$400,000 variables directly in the=if statement. Instead, you should reference the cells that contain this informationwithin your”Data” worksheet (these cell references will differ if you didn’t follow the layout within this briefing):
The screenshot below shows the key tax data from the “Data” worksheet calculated earlier.
The key tax rates and income threshold data should be contained in the following cells within your Data sheet:
After checking this data in your Data worksheet, return to your income statement worksheet. Did you remember to rename cells C12, C15 and C17 as required above? If not, do this before proceeding further.
Go to cell C16 of your income statement.
In cell C16, we want a formula that calculates the correct tax amount for the reported income before tax.Therefore, we will change the earlier formula to construct the =IF statement in cell C16.
If you renamed cell C12 to “OI”, cell C15 to “IBT”, and cell C17 to “NI”, you could simplify the IF statement entered in cell C16 of the income statement like this:
What this tells Excel is that if the income before tax in C15 is less than the $400,000 income level, charge tax at the lower 20% tax rate multiplied by the reported income before tax in C15, otherwise charge tax at the higher rate of 40%.
This is much easier to understand, as you can see that the tax expense is based upon the income before tax and a certain threshold amount of income.
If you are confused about where to insert this =IF formula, think about what you are trying to do. You are calculating the tax expense for the year. As a result, the formula must be entered in the cell within the income statementthat calculates the “tax expense/credit”. If you did not follow this Excel tutorial correctly, and changed cell locations, you will need to modify the cell references in your IF statement.
What happens if the company makes an annual loss? Where a loss is incurred, an entity receives a tax credit that is carried forward and deducted from future taxable income. If there is a negative value for income before tax in cell C15, the company should report a tax credit on its income statement. Look at what happens to the calculations for net income and tax.
Go to cell C15 and enter a negative loss amount of ($400,000) – this should provide a tax credit equal to: ($400,000) x 30% = ($120,000) in cell C16. However, our current =if statement calculation only uses the lower 20% tax rate as the pre-tax income is less than $400,000. It is the relative size of the loss that matters when calculating tax credits.
Restore (i.e. undelete) your existing formula in cell C15 to “C12-C14” before moving on, as IBT must be $400,000.
We can use the ABS function in Excel to help account for taxation where there is a pre-tax loss. The ABS function reports the absolute value of a cell, so if a number is negative it will treat it as a positive number. This will allow the correct tax credit to be calculated when the company incurs a net loss. The earlier =if statement in cell C16 must be modified to include the absolute value of cell C15(or “IBT” if you renamed C15 correctly).
Cell A15 of the income statement worksheet contains the text label “Income before tax”. If the company makes a loss, this label will be incorrect. Assuming you renamed cell C15 to “IBT” earlier, we can use another IF statement to have Excel change the text label in column A depending on whether the company makes a loss or a profit. Enter this =IF statement in cell A15:
=IF(IBT>0,”Income before tax”,”Loss before taxes”)
NOTE: The use of quotation marks is essential for Excel to understand you want a specific text phrase used.
If you failed to rename cell C15 to “IBT”, the above formula will not work, and Excel will report an error!
You can do the same thing for the “Net Income” text label in cell A17. Assuming that you have renamed cell C17 to “NetIncome” earlier, you can now enter the following formula in cell A17 to show a correct label for net income/loss:
=IF(NetIncome>0,”Net income for year”,”Net loss for year”)
Play around with this, but error check your work – you know what the final answers should be!
Once you have completed the =IF statements, you have finished preparing the yearly manufacturing accounts and income statement for the company. Save this Excel workbook as “Excel Project 1 Base File”. Your Base workbook should have four separate but linked worksheets. If you failed to use linking formulae, you must go back and do so, as any change in the Data worksheet should be reflected in the COGM, COGS, and income statements sheets. Please check your work for errors if yourending net incomewas not $288,000.
10. Adjusting for accounting errors, omissions, and expense analysis
Unfortunately, you are not finished yet. Use “Save as” tab to save a copy of your base file as “Adjusted file” before attempting this section. Your basic financial model is complete, but it must be modified for extra data and errors.
The company’s accountant has discovered the following error. In the “Adjusted file”, insert rows at appropriate places for any omission or include correct figure where it is incorrect in the Data sheet to make the following adjustments as needed:
You must link this new Expense Analysis table/worksheet (relevant cells in column “E”)with the respective data from your 2020 income statement. Do not hard code the 2020 data. Use appropriate formula to calculate the total expenses in cell E11 and percentages in column F and G.
Modify your existing worksheets to account for these errors. Add the extra expenses, percentages figures to your Data sheet and check formulareferences to these in the appropriate financial statement worksheet.DO NOT HARD CODE THESE CHANGES IN THE INCOME STATEMENT.
If you use formula and link the worksheets in your Base workbook correctly, any changes made for the adjustments will be automatically transpired to the COGM, COGS and Income worksheets.
After making all the adjustments, save the file using “save as” option and rename the workbook usingyour last name, first initial and the project number. For example “Siddique.S._Excel Project 1 Adjusted”
You must submit the following two filesin Canvas Under the Assignment: ACC 2020 Excel Project 1 by the deadline:
Instruction to save the Excel project into a PDF file:
Please note, failure to follow the submission instructions will result in a substantial loss of points. You must submit your project in Canvas using the relevant link. A project submitted via email will not be graded.
You must prepare and turn in your own Excel spreadsheet, not a joint solution with another student. Do not copy, share or plagiarize files. An assignment which is turned in late will have a penalty. No assignments will be accepted more than one week late.
If you are struggling with this assignment, you must ask for help. I am happy to address queries in class.
Reading for this project
You should consult our textbookfor extra guidance on how to produce the statements of cost of goods manufactured, cost of sales and income statements that we are modeling.
Excel Project 1 Assessment Grid
|ACC 2020 Fall2020|
|Excel project 1|
|1||Prepare data section and 4 separate worksheets||15|
|2||No hard coding & appropriate use of formulae||10|
|3||Add comment boxes and name cells||3|
|4||Use =if to determine income taxes||5|
|5||Correct the accounting errors in part 10||20|
|6||Show row and column headings and footer (See instructions)||5|
|7||Present and fit each worksheet on separate page||2|
|Elements||Meet or exceed expectations 90% – 100%||Meet some expectations 70 – 89%||Meet only a few expectations 0 – 69%||Points|
|Prepare data section and 4 separate worksheets||Prepare all the five worksheets as required.||Prepare at least 4 worksheets.||Prepare less than 4 worksheets.|
|No hard coding & appropriate use of formulae||No hard coding. All the formulae are correctly applied.||Some hard coding and incorrect use of formulae is less than 10 to 20%||Many hard coding and incorrect use of formulae is more than 20%.|
|Add notes/comments and name cells||Use appropriate use of notes and name cells as instructed.||Insert notes and name some of the cells.||Fails to insert notes and name few or no cells.|
|Use =if to determine income taxes||Apply formula as per instructions exactly.||Formula is somewhat correct.||Incorrect formula.|
|Adjust for the accounting errors in part 10||Make all the 5 adjustments correctly.||At least 3 – 4 adjustments are correctly made.||Less than 3 adjustments are made correctly.|
|Show row and column headings and footer in PDF (See instructions)||Instructions are properly followed.||Instructions are followed to some extent.||Failed mostly to follow the instructions.|
|Present and fit each worksheet on separate page in PDF||Each worksheet is presented and fit in separate page.||1 or 2 worksheets are broken into two pages.||Failed mostly to follow the relevant instructions provided in the instruction# 11.|
End of the Project!
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.