This is an excel assignment and I am not sure what to categorize it as.
Instructions MISY 5325 Project 2 Name: ClarkAndrew III I IIII IIII Do Not Do not insert or delete either rows or columns in any of these worksheets. Do not move any of the worksheets in this workbook relative to other worksheets. Do not modify the name and bar code in the grey bar of all worksheets. Do not change the order of the data if the instructions do not state that. Do Before submit your assignment, please rename the file by removing the work "blank" in front of the file name. For example, if the file titled "blankDoeJohnProj2" must be renamed to "DoeJohnProj2" Please note that the file name is case-sensitive (i.e., uppercase letter is NOT the same as lowercase letter) 5 % will be deducted for incorrect FileName, misorder of the worksheet, incorrect worksheet name, or incorrect information on the bar code Complete all of the worksheets. IfFunction1 Name: ClarkAndrew III I IIII IIII ContractorPay Per DayBegin DateEnding DateLimit DaysActual Days UsedGross Total PaymentLate PenaltyAdjusted Payment Allan$2,000.0021-May-049-Jul-044050$100,000.00 Allison$500.0024-Apr-0417-Jun-046055$27,500.00 Amanda$2,000.0028-May-045-Jul-046039$78,000.00 Audrey$1,200.0021-Mar-0416-May-044057$68,400.00 Cassandra$2,500.0027-May-043-Jul-044038$95,000.00 Clint$700.001-Apr-0424-Apr-046024$16,800.00 Collin$450.0015-Apr-0416-May-046032$14,400.00 Katie$1,000.0012-Apr-0415-May-043534$34,000.00 Roy$2,000.0028-Apr-0425-Jun-046059$118,000.00 Wesley$400.0026-Mar-0421-May-046057$22,800.00 Weslie$2,000.002-May-0429-May-046028$56,000.00 Westley$3,700.0017-Mar-042-May-044047$173,900.00 Instructions 1. In this scenario, each contractor promises to complete the job within a specific day (i.e., Limit Days). However, sometimes, the actual days (i.e., Actual Days Used) they use to complete the job is more or less than what they promise. When they finish the job late, we will charge them a Late Penalty. Use If Function in cell I6 to compute the "Late Penalty" which is equal to $200 per day for day that they are late. 2. Enter the formula in cell J6 to compute Adjusted Payment, which is equal to the "Gross Total Payment" - "Late Penalty" 3. Using the fill-handle to copy the function/formula in cells I6 and J6 to compute late penalty and adjusted payment for all employees. 4. Please note that typing the answer (i.e., a number) directly into the cells will be graded as incorrect answers) IfFunction2 Name: ClarkAndrew III I IIII IIII Employee NameBase PayWorkDateTravel MilesTravel ReimbursementAdjusted Pay Allan$275.0017-Aug-0450 Allison$300.005-Aug-04140 Amanda$400.0010-Aug-04200 Audrey$300.001-Aug-0490 Cassandra$275.003-Aug-0480 Dwight$300.0016-Aug-04140 Katie$500.0026-Aug-0430 Roy$270.008-Aug-04140 Wesley$300.0014-Aug-0490 Weslie$300.0017-Aug-04140 Westley$275.004-Aug-04140 Instructions 1. In this scenario, each employee has to travel a lot for a company's business. So, the company decides to have a rule to compensate for the miles they travel. The company will pay 40 cents for the miles that the employees travel more than 100 miles. For example, if an employee travels 120 miles, he/she will be reimbursed for $8 (i.e., 20 *0.4)). Use If Function in cell F5 to calculate Travel Reimbursement. 2. Enter the formula in cell G5 to calculate Adjusted Pay, which is equal to the "Base Pay" plus "Travel Reimbursement" 3. Using the fill-handle to copy the function/formula from cells F5 and G5 to compute travel reimbursement for all employees. 4. Please note that typing the answer (i.e., a number) directly into the cells will be graded as incorrect answers) Indirect Expenses Casa Grande Resort & Spa Profit Center Analysis of Indirect Expenses SpaLoungeBanquet RoomRestaurantBusiness CenterConference RoomsGift ShopChildren's Game RoomTotal Total Net Revenue$ 78,865.00$ 492,800.00$ 486,725.00$ 592,500.00$ 225,125.00$ 196,475.00$ 88,450.00$ 19,450.00 Cost of Sales36,715.00136,500.00106,500.00325,600.0014,790.0045,125.0037,000.008,650.00 Direct Expenses14,750.00152,975.0053,890.00252,975.008,435.0022,475.0031,800.006,940.00 Indirect Expenses Administrative Depreciation Energy Insurance Maintenance Marketing Total Indirect Expenses Net Income Square Footage250051008800600090057007501200 Planned Indirect Expenses Administrative$72,500.00 Depreciation$61,250.00 Energy$46,000.00 Insurance$12,500.00 Maintenance$26,000.00 Marketing$52,250.00 Name: ClarkAndrew III I IIII IIII Instructions Use the Planned Indirect Expenses provided in the cells B19:B24 to calculate the following indirect expenses: a. Spa Administrative (B8) = Administrative Expenses * Spa Total Net Revenue / Resort Total Net Revenue b. Spa Depreciation (B9) = Depreciation Expenses * Spa Square Footage / Total Square Footage c. Spa Energy (B10) = Energy Expenses * Spa Total Net Revenue / Resort Total Net Revenue d. Spa Insurance (B11) = Insurance Expenses * Spa Square Footage / Total Square Footage e. Spa Maintenance (B12) = Maintenance Expenses * Spa Square Footage / Total Square Footage f. Spa Marketing (B13) = Marketing Expenses * Spa Total Net Revenue / Resort Total Net Revenue g. Spa Total Indirect Expenses (B14) = SUM(B8:B13) h. Spa Net Income (B15) = Revenue - (Cost of Sales + Direct Expenses + Total Indirect Expenses) i. Use the concept of absolute reference we learn in chapter 3 to modify some cell references in column B, then use the fill handle to copy the range B8:B15 to the range C8:I15 j. AutoSum the totals for Column J, where appropriate k. Format the number as it deems appropriate (i.e., any appropriate formats will be accepted) GoalSeek Blue Valley Resort Profit Analysis SpaExecutive RoomRestaurantBusiness CenterConference RoomsReceptionTotal Sales$70,000.00$350,000.00$625,850.00$180,000.00$190,251.00$222,120.00$1,638,221.00 Cost of Sales36,715.00106,500.00325,600.0014,790.0045,125.008,650.00537,380.00 Direct Expenses14,750.0053,890.00252,975.008,435.0022,475.006,940.00359,465.00 Indirect Expenses Administrative$3,097.87$15,489.36$27,697.19$7,965.96$8,419.62$9,829.99$72,500.00 Rent6,100.6021,474.1014,641.432,196.2213,909.362,928.2961,250.00 Electricity1,965.559,827.7317,573.395,054.265,342.106,236.9646,000.00 Utility1,245.024,382.472,988.05448.212,838.65597.6112,500.00 Maintenance2,589.649,115.546,215.14932.275,904.381,243.0326,000.00 Miscellaneous2,232.6011,163.0219,961.085,740.986,067.937,084.3752,250.00 Total Indirect Expenses$17,231.28$71,452.23$89,076.29$22,337.90$42,482.05$27,920.25$270,500.00 Net Income$1,303.72$118,157.77($41,801.29)$134,437.10$80,168.95$178,609.75$470,876.00 Square Footage2,5008,8006,0009005,7001,20025,100 Planned Indirect Expenses Administrative$72,500.00 Depreciation$61,250.00 Electricity$46,000.00 Insurance$12,500.00 Maintenance$26,000.00 Marketing$52,250.00 Use the data provided above to answer the folowing questions. Use the Goal Seek Tool and TYPE the answer into the blue cell. 1. In order to have the Total Net Income Equal to $500,000.00, the administrative expense must be 2. In order to have the Total Net Income Equal to $490,000.00, the electricity expense must be 3. In order to have the Total Net Income Equal to $480,000.00, the maintenance expense must be Name: ClarkAndrew III I IIII IIII Instructions 1. Use Goal Seek function to determine the value in cells H27-H29. Please ensure once you solved for an answer, click "cancel" to return to the original data before starting another goal seek.