Need assistance solving so I can check my answers for these 5 problems. Formulas (on a separate sheet) would be helpful. Thank you

_x000D_ Accounting and Microsoft Excel_x0 -> data creation_x -> data sharing_x0 -> data analytics_ -> data mining_x0 -> data reporting_ express_x000D_ This file may not be redistributed without th written authority of Paul Goldwater PhD As Please do not (otherwise you will jeopardize the grading pro -rename this workbook -rename any sheets or insert/delete any sheets -insert/delete any columns in a sheet -insert/delete any rows in a sheet Read the Excel assignment(s) carefully and do the following: You have to complete every cell colored yellow For every cell colored yellow, you have to decide: Dropdown -> Every cell with a dropdown, choose t Value -> enter the appropriate numeric, date/time Formula -> enter the appropriate formula e.g. '=A1 Array Formula -> enter the appropriate array formu The initial due date/time for this assignment is Sunday, April The retake due date/time for this assignment is Sunday, Apri Cell comments: every cell you must complete has a cell comment. The cell comment has sp pointer on the cell, the comment appears. Four items of information will be provided to th 1. 2. cell value - whether the cell value will be date, logical value, number, text cell formula - simple or array the formula tokens will be provided and sorted e.g. 0 (3), 1 (2), 12 (4 the formula operators */+-^() are not provided if a formula token is used more than once in the formula, the token 3. 4. cell formatting - Excel lets you change many of the ways it displays data in a cell. can access and modify the majority of these settings in the Format Cells dialog b cell name - any required cell name will be indicated Excel instructions: column AA will contain any Excel instructions for a chart, conditional for ng and Microsoft Excel_x000D_ -> data creation_x000D_ -> data sharing_x000D_ -> data analytics_x000D_ -> data mining_x000D_ -> data reporting_x000D_ be redistributed without the of Paul Goldwater PhD Assignment Instructions Homework: 2 e the grading process): do the following: ed yellow e to decide: pdown, choose the appropriate response from the dropdown meric, date/time or text value formula e.g. '=A1+B1' press Enter priate array formula e.g. '=LINEST(A1:A12, B1:B12, True, True)' press Ctrl+Shift+Ente nt is Sunday, April 02, 2017 --- 6:00:00 PM nt is Sunday, April 02, 2017 --- 6:00:00 PM . The cell comment has specific information for the student. When a cell has a comment, a red indicator appears in the cor tion will be provided to the student in the cell comment. value, number, text rted e.g. 0 (3), 1 (2), 12 (4), H18 (4), H20 (2), H36 (2), I29 (2), I33 (2), IF, ROUNDUP n the formula, the token count will appear in parentheses e.g. 0 (3), H18 (4) ys it displays data in a cell. For example, you can specify the number of digits to the right of a decimal point, or you can add a n the Format Cells dialog box (on the Format menu, click Cells). The student is required to only set 13 commonly used forma or a chart, conditional formatting, tables, data tables, range names, pivot tables, etc. ess Ctrl+Shift+Enter dicator appears in the corner of the cell. When you rest the mal point, or you can add a pattern and border to the cell. You 13 commonly used formatting characteristics. Corrine Company owns a warehouse that it no longer needs in its own operations. The warehouse was built at a estimated useful life was 12 years. There are two proposals for the use of the warehouse: 1 Rent it at $63,000 per year, which includes estimated costs of $24,400 per year for maintenance, heat 2 Sell it outright to a prospective buyer who has offered $195,100. Any capital gain would be used at th Disregard income taxes. Required: a. Calculate the after-tax income if (1) Corrine Company keeps the warehouse and (2) if Corrine Compan If sell Cost Accumulated depreciation Book value Selling price Long-term gain Tax @ Net gain after tax Net cash inflow If rent Rent proceeds per year Maintenance, etc. Depreciation Net rent income before tax Tax @ Net rent after tax Net cash inflow b. Which proposal should the company accept? (The present value of $36,468 for 2 years at, say, 10%, is $63,292, even less than the $153,994 and mo the value of the warehouse 2 years hence is not mentioned. It might be sold at a large enough gain to now. Rent might increase, or expenses increase. Evidence is weighted in favor of a sale now for after-t e warehouse was built at a cost of $222,000, 10 years ago, at which time its use: year for maintenance, heat, and utilities to be paid by the lessor. The tax rate is 32% al gain would be used at the 26% rate. e and (2) if Corrine Company sells the warehouse. Cost Useful life Age s than the $153,994 and more accurate, making the sale even more attractive.) But, ld at a large enough gain to offset the difference between rent proceeds and a sale vor of a sale now for after-tax benefits. Excel Instructions No additional Excel instructions No pivot tables, no tables/lists, no range names, no charts. Wellington Corporation estimates that it will have $410,000 available for capital investments next year. Half of th half will be invested in the most desirable projects from the following list. None of the investments has a residua Project Number Added Investment 1 2 3 4 5 Expected AfterTax Cash Inflow Estimated Life of Project $20,500 $26,000 $3,800 $8,000 $10,160 5 4 13 2 2 $80,000 $80,000 $35,000 $17,000 $42,000 Required: Rank the projects in order of their desirability. Amount for capital investments Project Number Added Investment Prof observation: not need in THIS p Expected AfterTax Cash Inflow Estimated Life Rank of Project ents next year. Half of this will be reserved for emergency projects and nvestments has a residual value. ation: not need in THIS problem, but shall discuss in class. Year Internal Rate of Return 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Excel Instructions No additional Excel instructions No pivot tables, no tables/lists, no range names, no charts. A 1 2 B C D E F G H Managers make decisions and when they make decisions there will be some action and that leads to costs being incurred. Example - factory manager requests HR to employ workers. I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE Excel Instructions 3 4 5 Costs flow in an organization because when the manager makes a decision resources flow. Cost needs to be classified for accounting purposes. The following diagram is a useful classification process to help us to classify costs. 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Income Statement 20 21 22 23 24 25 26 Balance Sheet 27 Pivot table(s) - PTs have many properties, you will set only the following properties. Pivot table # 1 Name PivotTable1 Location including page fields No page fields in PT excluding page fields $I$67 TableStyle PivotStyleLight16 SourceType Microsoft Excel list or database CommandText SourceDataFile SourceData Table or Range of data - $B$40:$F$62 Field(s) Field # 1 Name (Source Name) Cost Caption (Custom Name) Cost Orientation xlRowField Position 1 Items Checked Utilities apportioned to shop Utilities apportioned to bakery Staffing shop Staffing careful, make sure you see all items, double click cell with items bakery Rent apportioned to bakery Rent appoprtioned to bakery Improvements Purchase supplies shop Purchase supplies bakery Purchase Items Unchecked Function NumberFormat SortOrder Field # 2 Level 3 Level 3 xlColumnField 1 Balance Sheet Income Statement (blank) Field # 3 Amount Average of Amount xlDataField 1 Average $#,##0 Sorts the specified field in descending order - Cost Auto sort order - Level 3 28 Filter: Label/Value Filters FilterType 29 30 31 Income Statement 32 33 34 35 36 No row field filter. No column field filter. PivotField.Name DataField Value1 Value2 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 Assume we have a bakery - a small bakery. Classify the following costs and assume all inventory was not sold. Classification Cost Amount Level 1 Level 2 Level 3 Pots & Pans $2,404 Real Estate & Improvements $111,320 Expenses While Training Bakery Employees $4,586 One Month's Rent apportioned to bakery $3,553 Utilities apportioned to bakery $2,933 Purchase supplies shop $483 Insurance apportioned to bakery $690 Equipment $105,228 Legal Fees $5,959 One Month's Rent apportioned to shop $358 City, County State Permits/Taxes $5,013 Grand opening advertising $4,380 Initial Franchise Fee $37,683 Exterior signage $6,919 Purchase supplies bakery $5,054 Expenses While Training Shop $4,582 Purchase ingredients bakery $11,985 Rent apportioned to bakery $2,850 Staffing shop $239 Utilities apportioned to shop $374 Staffing bakery $4,862 Insurance apportioned to shop $146 63 64 65 66 67 68 69 Prepare a pivot table based on the range of appropriate cells to answer the following questions: Remember fundamental purpose of a pivot table to filter, sort, aggregate(analyze) and report (tables, charts) Filters: none Sort: Cost on descending order Cost 70 71 72 73 74 75 What is the total cost: Product Period Asset Loss Slicer(s) Connections SourceName Caption DisplayHeader Height Left Top Width NumberOfColumns RowHeight ColumnWidth Style ItemsSelected ItemsUnselected No Slicers for this Pivot Table. Timeline(s) Caption Connections RowHeight ColumnWidth ShowHeader ShowSelectionLabel ShowHorizontalScrollbar ShowTimeLevel Style Height Left Top Width Level No Timelines for this Pivot Table. Workbook Data Model Tables Name RecordCount SourceName SourceWorkbookConnection Table # 1 Table1 Table # 2 PowerTable 22 22 WorksheetConnection_PT Basic.xlsm!Table1 LinkedTable_PowerTable Cost 130 Amount 20 Level 1 130 Level 2 130 Level 3 130 Cost 130 Amount 20 Level 1 130 Level 2 130 Level 3 130 76 77 78 79 80 81 82 83 84 85 86 Direct materials Direct labor Manufacturing overhead General & Administrative Selling & Distribution Financial Current Asset Plant & Equipment Not extraordinary Extraordinary 87 88 89 Balance Sheet Income Statement 90 91 92 93 94 95 96 97 98 This is very important - the pivot table is based on a range of cells - specifically cells $B$40:$F$62 Read about: Select cells, ranges, rows, or columns on a worksheet https://support.office.com/en-us/article/Select-cells-ranges-rows-or-columns-on-a-worksheet-6dc8a03a-fb80-4454-922e-965eb48a6d25 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 If you add/delete rows in the range - you will need to amend the PT data source - but you should not need to. Columns ColumnName, ColumnDataType Relationships PrimmaryKeyTable PrimmaryKeyColumn ForeignKeyTable ForeignKeyColumn There are no relationship tables in the Data Model. DAX Measures AssociatedTable There are no Measures in the Data Model. Name FormatInformation Formula Managers make decisions and when they make decisions there will be some action and that leads to costs being Example - factory manager requests HR to employ workers. Costs flow in an organization because when the manager makes a decision resources flow. Cost needs to be classified for accounting purposes. The following diagram is a useful classification process to he Assume we have a bakery - a small bakery. Classify the following costs and assume all inventory was not sold. Classification Cost Amount Level 1 Pots & Pans $2,709 Real Estate & Improvements $120,814 Expenses While Training Bakery Employees $4,860 One Month's Rent apportioned to bakery $3,968 Utilities apportioned to bakery $3,294 Purchase supplies shop $528 Insurance apportioned to bakery Equipment Legal Fees One Month's Rent apportioned to shop City, County State Permits/Taxes Grand opening advertising Initial Franchise Fee Exterior signage Purchase supplies bakery Expenses While Training Shop Purchase ingredients bakery Rent apportioned to bakery Staffing shop Utilities apportioned to shop Staffing bakery Insurance apportioned to shop $762 $112,735 $6,544 $390 $5,124 $4,821 $40,244 $7,752 $5,204 $5,092 $13,426 $3,044 $275 $402 $5,388 $162 Prepare a pivot table based on the range of appropriate cells to answer the following questions: Remember fundamental purpose of a pivot table to filter, sort, aggregate(analyze) and report (tables, Filters: none Sort: Cost on ascending order Cost What is the total cost: Product Period Asset Loss Direct materials Direct labor Manufacturing overhead General & Administrative Selling & Distribution Financial Current Asset Plant & Equipment Not extraordinary Extraordinary Balance Sheet Income Statement This is very important - the pivot table is based on a table - specifically named Table1 Read about: Overview of Excel tables https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c9433 You must know about tables: Rows Columns Column headings - unique Data in a column the same data type A Table is an Excel object - objects have: Properties - relevent to us Methods - ignore Events - ignore be some action and that leads to costs being incurred. ecision resources flow. iagram is a useful classification process to help us to classify costs. Income Statement Balance Sheet Income Statement ts and assume all inventory was not sold. Classification Level 2 Level 3 wer the following questions: , sort, aggregate(analyze) and report (tables, charts) le - specifically named Table1 cel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c Excel Instructions Table(s) (previously known as List) Name Range HeaderRowRange DataBodyRange ListColumns ListRows SourceType SourceData ShowTotals TableStyle Table (List) # 1 Table1 $B$40:$F$62 $B$40:$F$40 $B$41:$F$62 5 22 Range $B$40:$F$62 0 TableStyleMedium2 Pivot table(s) - PTs have many properties, you will set only the following properties. Pivot table # 1 Name PivotTable1 Location including page fields No page fields in PT excluding page fields $I$67 TableStyle PivotStyleLight16 SourceType Microsoft Excel list or database CommandText SourceDataFile SourceData Table or Range of data - Table1 Field(s) Field # 1 Name (Source Name) Cost Caption (Custom Name) Cost Orientation xlRowField Position 1 Items Checked City, County State Permits/Taxes Equipment Expenses While careful, make sure you see all items, double click cell with items Training Bakery Expenses While Training Bakery Employees Expenses While Training Shop Exterior signage Grand opening advertising Items Unchecked Function NumberFormat Sorts the specified field in ascending order - Cost SortOrder Filter: Label/Value Filters FilterType No row field filter. PivotField.Name DataField Value1 Value2 Slicer(s) Connections SourceName Caption DisplayHeader Height Left Top Width NumberOfColumns RowHeight ColumnWidth Style ItemsSelected ItemsUnselected No Slicers for this Pivot Table. Timeline(s) Caption Connections RowHeight ColumnWidth ShowHeader ShowSelectionLabel ShowHorizontalScrollbar ShowTimeLevel Style Height Left Top Width Level No Timelines for this Pivot Table. Workbook Data Model Tables Name RecordCount SourceName SourceWorkbookConnection Columns Table # 1 Table1 22 WorksheetConnection_PT Basic.xlsm!Table1 ColumnName, ColumnDataType Cost 130 Amount 20 Level 1 130 Level 2 130 Level 3 130 Relationships PrimmaryKeyTable PrimmaryKeyColumn ForeignKeyTable ForeignKeyColumn There are no relationship tables in the Data Model. DAX Measures AssociatedTable There are no Measures in the Data Model. ties. Field # 2 Level 3 Level 3 xlColumnField 1 Balance Sheet Income Statement Auto sort order - Level 3 No column field filter. Table # 2 PowerTable 22 LinkedTable_PowerTable Cost 130 Amount 20 Level 1 130 Level 2 130 Level 3 130 Data Model. Name Field # 3 Amount Sum of Amount xlDataField 1 Sum $#,##0 FormatInformation Formula A 1 2 B C D E F G H Managers make decisions and when they make decisions there will be some action and that leads to costs being incurred. Example - factory manager requests HR to employ workers. I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE Excel Instructions 3 4 5 Costs flow in an organization because when the manager makes a decision resources flow. Cost needs to be classified for accounting purposes. The following diagram is a useful classification process to help us to classify costs. Table(s) (previously known as List) 6 Name Range HeaderRowRange DataBodyRange ListColumns ListRows SourceType SourceData ShowTotals TableStyle 7 8 9 10 11 12 13 14 15 16 Table (List) # 1 PowerTable $B$40:$F$62 $B$40:$F$40 $B$41:$F$62 5 22 Range $B$40:$F$62 0 TableStyleMedium2 17 18 19 Income Statement 20 21 Pivot table(s) - PTs have many properties, you will set only the following properties. Pivot table # 1 22 Name Location including page fields excluding page fields TableStyle SourceType CommandText SourceDataFile SourceData Field(s) Name (Source Name) Caption (Custom Name) Orientation Position Items Checked careful, make sure you see all items, double click cell with items 23 24 25 26 Balance Sheet 27 28 29 30 31 Income Statement 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 Assume we have a bakery - a small bakery. Classify the following costs and assume all inventory was not sold. Classification Cost Amount Level 1 Level 2 Pots & Pans $2,190 Real Estate & Improvements $99,329 Expenses While Training Bakery Employees $4,211 One Month's Rent apportioned to bakery $3,417 Utilities apportioned to bakery $2,775 Purchase supplies shop $449 Insurance apportioned to bakery $644 Equipment $95,202 Legal Fees $5,732 One Month's Rent apportioned to shop $346 City, County State Permits/Taxes $4,315 Grand opening advertising $4,129 Initial Franchise Fee $33,980 Exterior signage $6,563 Purchase supplies bakery $4,565 Expenses While Training Shop $4,292 Purchase ingredients bakery $11,173 Rent apportioned to bakery $2,573 Staffing shop $233 Utilities apportioned to shop $343 Staffing bakery $4,506 Insurance apportioned to shop $141 Level 3 63 64 65 66 67 68 69 Prepare a pivot table based on the range of appropriate cells to answer the following questions: Remember fundamental purpose of a pivot table to filter, sort, aggregate(analyze) and report (tables, charts) Filters: none Sort: Cost on ascending order Amount 70 71 72 73 74 75 What is the total cost: Product Period Asset Loss 76 77 78 79 80 81 82 83 84 85 86 Direct materials Direct labor Manufacturing overhead General & Administrative Selling & Distribution Financial Current Asset Plant & Equipment Not extraordinary Extraordinary 87 88 89 Balance Sheet Income Statement 90 Items Unchecked Function NumberFormat SortOrder Filter: Label/Value Filters FilterType PivotTable1 No page fields in PT $I$67 PivotStyleLight16 PowerPivot Model xlExternal Field # 1 [PowerTable].[Cost].[Cost] Cost xlRowField 1 City, County State Permits/Taxes Equipment Expenses While Training Bakery Employees Expenses While Training Shop Exterior signage Grand opening advertising Initial Franchise Fee Insurance Field # 2 [PowerTable].[Level 3].[Level 3] Level 3 xlColumnField 1 Balance Sheet Income Statement Field # 3 [Measures].[Sum of Amount 2] Sum of Amount xlDataField 1 Sorts the specified field in ascending order [Measures].[Sum of Amount 2] Auto sort order - [PowerTable].[Level 3].[Level 3] No row field filter. No column field filter. DAX Measure $#,##0 PivotField.Name DataField Value1 Value2 Slicer(s) Connections SourceName Caption DisplayHeader Height Left Top Width NumberOfColumns RowHeight ColumnWidth Style ItemsSelected ItemsUnselected No Slicers for this Pivot Table. Timeline(s) Caption Connections RowHeight ColumnWidth ShowHeader ShowSelectionLabel ShowHorizontalScrollbar ShowTimeLevel Style Height Left Top Width Level No Timelines for this Pivot Table. Workbook Data Model Tables Name RecordCount SourceName SourceWorkbookConnection Table # 1 Table1 Table # 2 PowerTable 22 WorksheetConnection_PT Basic.xlsm!Table1 22 LinkedTable_PowerTable 91 92 93 94 95 96 97 98 This is very important - the pivot table is based on a table - specifically named PivotTable and PivotTable is added to the Data Model. Read about: Select cells, ranges, rows, or columns on a worksheet https://support.office.com/en-us/article/Select-cells-ranges-rows-or-columns-on-a-worksheet-6dc8a03a-fb80-4454-922e-965eb48a6d25 99 100 101 102 103 If you add/delete rows in the range - you will need to amend the PT data source - but you should not need to. Columns ColumnName, ColumnDataType Cost 130 Amount 20 Level 1 130 Level 2 130 Level 3 130 Cost 130 Amount 20 Level 1 130 Level 2 130 Level 3 130 Relationships PrimmaryKeyTable PrimmaryKeyColumn ForeignKeyTable ForeignKeyColumn There are no relationship tables in the Data Model. DAX Measures AssociatedTable There are no Measures in the Data Model. 104 105 106 107 108 109 110 111 112 113 Name FormatInformation Formula