Instructions
MISY XXXXXXXXXXProject 2
Name: BennettAlycia IIII I I I I II
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 inco
ect FileName, misorder of the worksheet, inco
ect worksheet name, or inco
ect information on the bar code
Complete all of the worksheets.
IfFunction1
Name: BennettAlycia IIII I I I I II
Contractor Pay Per Day Begin Date Ending Date Limit Days Actual Days Used Gross Total Payment Late Penalty Adjusted Payment
Allan $2,000.00 21-May-04 9-Jul-04 40 50 $100,000.00
Allison $500.00 24-Apr-04 17-Jun-04 60 55 $27,500.00
Amanda $2,000.00 28-May-04 5-Jul-04 60 39 $78,000.00
Audrey $1,200.00 21-Mar-04 16-May-04 40 57 $68,400.00
Cassandra $2,500.00 27-May-04 3-Jul-04 40 38 $95,000.00
Clint $700.00 1-Apr-04 24-Apr-04 60 24 $16,800.00
Collin $450.00 15-Apr-04 16-May-04 60 32 $14,400.00
Katie $1,000.00 12-Apr-04 15-May-04 35 34 $34,000.00
Roy $2,000.00 28-Apr-04 25-Jun-04 60 59 $118,000.00
Wesley $400.00 26-Mar-04 21-May-04 60 57 $22,800.00
Weslie $2,000.00 2-May-04 29-May-04 60 28 $56,000.00
Westley $3,700.00 17-Mar-04 2-May-04 40 47 $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 inco
ect answers)
IfFunction2
Name: BennettAlycia IIII I I I I II
Employee Name Base Pay WorkDate Travel Miles Travel Reimbursement Adjusted Pay
Allan $275.00 17-Aug-04 50
Allison $300.00 5-Aug-04 140
Amanda $400.00 10-Aug-04 200
Audrey $300.00 1-Aug-04 90
Cassandra $275.00 3-Aug-04 80
Dwight $300.00 16-Aug-04 140
Katie $500.00 26-Aug-04 30
Roy $270.00 8-Aug-04 140
Wesley $300.00 14-Aug-04 90
Weslie $300.00 17-Aug-04 140
Westley $275.00 4-Aug-04 140
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 inco
ect answers)
Indirect Expenses
Casa Grande Resort & Spa
Profit Center Analysis of Indirect Expenses
Spa Lounge Banquet
Room Restaurant Business
Center Conference
Rooms Gift Shop Children's Game Room Total
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 Sales 36,715.00 136,500.00 106,500.00 325,600.00 14,790.00 45,125.00 37,000.00 8,650.00
Direct Expenses 14,750.00 152,975.00 53,890.00 252,975.00 8,435.00 22,475.00 31,800.00 6,940.00
Indirect Expenses
Administrative
Depreciation
Energy
Insurance
Maintenance
Marketing
Total Indirect Expenses
Net Income
Square Footage 2500 5100 8800 6000 900 5700 750 1200
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: BennettAlycia IIII I I I I II
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
Spa Executive Room Restaurant Business
Center Conference
Rooms Reception Total
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 Sales 36,715.00 106,500.00 325,600.00 14,790.00 45,125.00 8,650.00 537,380.00
Direct Expenses 14,750.00 53,890.00 252,975.00 8,435.00 22,475.00 6,940.00 359,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
Rent 6,100.60 21,474.10 14,641.43 2,196.22 13,909.36 2,928.29 61,250.00
Electricity 1,965.55 9,827.73 17,573.39 5,054.26 5,342.10 6,236.96 46,000.00
Utility 1,245.02 4,382.47 2,988.05 448.21 2,838.65 597.61 12,500.00
Maintenance 2,589.64 9,115.54 6,215.14 932.27 5,904.38 1,243.03 26,000.00
Miscellaneous 2,232.60 11,163.02 19,961.08 5,740.98 6,067.93 7,084.37 52,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 Footage 2,500 8,800 6,000 900 5,700 1,200 25,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: BennettAlycia IIII I I I I II
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.