Important: Do this in excel, for part 1 and 2: select any random departments, and any random employee names and ID. you may select any random inputs for salary and everything else. then do the part 3 to 5 based on what you choose.
(1)**On one sheet:
a) create a simple listing of 3 departments relative to your industry
b) In a separate column, create a list for:
? Full-Time ? Part-Time
(2)**On a separate new sheet, create a listing of 20 employees including:
a) Employee ID
b) Last Name
c) First Name
d) Department Name
? Use Data Validation to create a listing of departments from which to select (use the listing
from the sheet created in step 1)
e) Job Title
f) Full-time or Part Time
? Use Data Validation to create a listing from which to select (use the listing from the sheet
created in step 1)
g) Annual Salary
-Note: You will eventually be creating an Organization Chart for these employees.
(3)***CREATE A COPY OF THE EMPLOYEE LISTING SHEET (created in step 2)...ON THE NEW SHEET...
a) Sort the table alphabetically by department, then by salary highest to lowest
b) Apply Data Bar Conditional Formatting to the salary
c) Filter the table by the department of your choice
(4) ***CREATE ANOTHER COPY OF THE EMPLOYEE LISTING SHEET FROM STEP 2...ON A NEW SHEET
a) Copy the sheet and prepare it to apply Subtotals. Subtotal for the average salary by department.
b) Collapse to show only level 2
Revised 9.20.2020
(5)**Using the EMPLOYEE LISTING sheet created in STEP 2, create a Pivot Table on a new worksheet showing:
a) the sum of the annual salaries for full-time versus part-time employees by job title
b) The report should be set to filter by Department
SALES BUDGET: March April May June Total Sales 65000 $85,000 $115,000 $80,000 SCHEDULE OF EXPECTED CASH COLLECTIONS: April May June March sales $39,000 April sales $34,000 $51,000 May sales $46,000 $69,000 June sales $32,000 July Sales Total Cash Collections $73,000 $97,000 $101,000 MERCHANDISE PURCHASES BUDGET: April May June Budgeted COGS 46,750 63,250 44,000 Add desired ending inventory 25.300 17.600 16.500 Total needs 72,050 80,850 60,500 Less beginning inventory (18.700) (25.300) (17 600) Required purchases 53. 350 55 550 42.900 BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES April May June Accounts payable $20,075 April purchases $26,675 $26,675 May purchases $27,775 $27,775 June purchases $21,450 July purchases Total cash payments $46, 750 $54,450 $49,225 CASH BUDGET April May June Cash balance $8,000 $5,100 $15,300 Add collections from customers $73.000 $97.000 $101 000 Total cash available $81.000 $102 100 $16.300 Less Disbursements Merchandise purchases $46,750 $54,450 $49,225 Advertising $7,000 $7,000 $7,000 Salaries $12,000 $12,000 $12,000 Shipping 3% Sales $2,550 $3,450 $2,400 Other Exp 6% Sales $5, 100 $6,900 $4,800 Equipment purchases $14.500 $3.000 Total Disbursements $87.900 586 800 575 425 Excess (deficiency) of receipts over disbursements $6.900 $15.300 $40 875 Financing: Borrowings $12,000 $0 $0 Repayments -$12,000 Interest -$360 Total financing $12.000 -512.360 Cash balance, ending $5, 100 $15,300 $28,515Part A - The Wildcat Magazine prepares annual financial statements. The December 31, 2016 and December 31, 2017, trial balances contained the following account information: Dec 31, 2016 Dec 31,2017 Supplies $2,500 $3,300 interest Receivable 0 800 Salaries Payable 8,000 6,500 Unearned Revenue 45,000 56,300 The following information is also known: a) The 2017 income statement reports $12,000 in supplies expense. b) Wildcat Magazine loaned money to a supplier on November 15*, 2017 at a 10% annual interest rate. The loan must be repaid with interest on March 15', 2018. c) $112,000 was paid to employees for wages during 2017. d) Wildcat received $178,200 from customers for annual subscriptions during 2017. Required: 1. What was the cost of supplies purchased during 2017? 2. How much money was loaned to the supplier on November 1\"", 2017? 3. What was the total amount of salaries expense reported on the 2017 income statement? 4. How much subscription revenue was recognized (recorded) in 2017? 12. Which ethical framework is framed around the Categorical Imperative? ) Culbaral Relativism bj Social Contract Theory c) Kantianism d) Act Utilitarianism c) Cmegorical UtilitareLatianium 13. Which ethical framework suffers from the problem of moral luck? a) Virtue Theory bj Subjective Relativism c) Kantianlar dj Social Contract Theory c) Act Utilitarianism 14. Which ethical framework could be described as utilitarianism focused on just oneonly?" ") Divine Command Theory bj Ethical Egoism ") Kantianism dj Rule LuMarianism el Act Utilitarianism 15. Which ethical framework recognizes that our moral decision-making skills develop over time? al Divine Command Theory by Virtue Theory cl Social Contract Theory d Rule Unlitarianism cl Cultural Relativism 16. "What's right for you may not be right for me" is from... ") Kantianism bj Rule Utilitarianism c) Virtue Theory d) Social Contract Theory c) A Pixar movie 17. Plato was Socrates" student The above statement is false by The above statement is trueons saved Balance Sheet Items (Liabilities) Accounts Payable 550 560 Notes Payable 83 93 Total Current Liabilities Long Term Liabilities 1020 990 O Total Liabilities Preferred Stock 100 100 Common Stock 3289 3289 Retained Earnings 1350 1000 Total Liabilities and EquityWhat does Year 0 represent in a cash flow timeline? When the very first cash flow occurs 5-2 Future Values Calculate the future value given the following information below: Amount invested at year 0: $80 Number of years invested: Interested rate with annual compounding: 5-3 Present Values-Question 1 Calculate the present value given the following information below: Amount at end of year 6 $1,000 Interested rate with annual compounding: 3%
