University of California Income Statement Direct Departmental Expenses Worksheet

User Generated

nqvqnfivc666

Business Finance

University of California San Diego

Description

finish the final exam excel. and there are some example for the excel ..................................

Unformatted Attachment Preview

(6 points) [January 2020 - March 2020] Month Units Purchased 1,000 1,000 1,000 January February March Total Beginning Inventory (The end of December, 2018): 1,000 units purchased at $ 18 each Item Sales = 3,000 units @ $20 each Beginning Inventory (BI) Purchases (P) Ending Inventory (EI) LIFO $60,000 $18,000 COGS (Cost of Goods Sold) = BI+P-EI Expenses Net Income (=Sales-COGS-Expenses) $10,000 ch 2020] Cost/each $16 $14 $12 ): FIFO $60,000 $18,000 $10,000 Total Value (7 points) Cash Accounts receivable Inventory Prepaid expenses Land Building Furniture Depreciation Account payable Utility bills Payroll taxes Mortgage $ $ $ $ $ $ $ $ $ $ $ $ 20,000 1,000 1,300 500 30,000 30,000 2,500 9,000 4,000 1,000 1,000 12,000 Assets Current Assets Total current assets Fixed Assets Total fixed assets Total Assets sets Liabilities & Capital (Equity) Liabilities Current Liabilities Total Current liabilities Long-term Liabilities Mortgage Total Liabilities Capital (6 points) $ Amount Sales Food Beverage Total Sales Direct Departmental Expenses Cost of Food sold Cost of Beverage sold Promotional Items Employee meals Total Direct Departmental Expenses $30,000 $20,000 $4,000 $4,000 $1,000 $1,500 Gross Margin Operating Expenses Payroll Employee benefits Direct operating expenses marketing Utility Administrative and General expenses Maintenance Total Operating Expenses $5,000 $300 $500 $200 $600 $400 $500 Occupational Expenses Rent Insurance Property tax Total Occupational Expenses $1,000 $2,000 $200 Depreciation $2,000 Net Income Using the given information below, please determine how much cash come Year 2019 Changes in Assets and Liabilities Increaase in Accounts Receivable Decrease in Inventories Decrease in Accounts payable Increase in taxes payable Cash flows from investing activities Proceeds from sale of equipment Down payment on equipment purchases Cash flows from financing activities Net borrowing under line-of-credit $5,000 $5,000 $7,000 $8,000 $3,000 $1,500 $40,000 e how much cash comes in (cash inflow) or goes out (cash outflow) Year 2018 $2,000 $8,000 $10,000 $5,000 $10,000 Cash flow changes (7 points) Income Statement $ Amount % of Sales Sales Food Beverage Total Sales $ $ $ 35,000 20,000 55,000 63.64% 36.36% 100.00% Direct Departmental Expenses Cost of Food sold Cost of Beverage sold Promotional Items Employee meals Total Direct Departmental Expenses $ $ $ $ $ 10,000 5,000 3,000 2,000 20,000 18.18% 9.09% 5.45% 3.64% 36.36% Gross Margin $ 35,000 63.64% Operating Expenses Payroll Employee benefits Direct operating expenses marketing Utility Administrative and General expenses Maintenance Total Operating Expenses $ $ $ $ $ $ $ $ 7,000 200 800 600 400 100 900 10,000 12.73% 0.36% 1.45% 1.09% 0.73% 0.18% 1.64% 18.18% Occupational Expenses Rent Insurance Property tax Total Occupational Expenses Depreciation $ $ $ $ $ 2,000 1,000 2,000 5,000 1,000 3.64% 1.82% 3.64% 9.09% 1.82% Net Income $ 19,000 34.55% Using the given information below, please determine how much cash comes i Changes in Assets and Liabilities Decrease in Accounts Receivable Decrease in Inventories Decrease in Accounts payable Decrease in taxes payable Cash flows from investing activities Proceeds from sale of equipment Down payment on equipment purchases Cash flows from financing activities Net borrowing under line-of-credit Year 2015 Year 2014 $3,000 $5,000 $7,000 $6,000 $5,000 $8,000 $9,000 $8,000 $3,000 $1,500 $30,000 $ $ $10,000 how much cash comes in (cash inflow) or goes out (cash outflow) Cash flow changes $2,000 $3,000 -$2,000.00 -$2,000.00 Cash Inflow Cash Inflow Cash Outflow Cash Outflow $3,000.00 -$1,500.00 Cash Inflow Cash Outflow $20,000 Cash Inflow Statement of Cash Flow Year 2015 Cash Flows from operating activities Net income (From income statement) NA Adjustments to Reconcile Net Income to Net Cash Changes in Assets and Liabilities Decrease in Accounts Receivable Decrease in Inventories Decrease in Accounts payable Decrease in taxes payable Total Adjustments Net cash provided by operating activities $ $ $ $ 3,000 5,000 7,000 6,000 Cash Flow from Investing Activities Proceeds from sale of equipment Down payment on equipment purchases Net cash provided by investing activities $ 3,000 $ 1,500 Cash flows from financing activities Net borrowing under line-of-credit Net cash provided by financing activities $ 30,000 ow Year 2014 Cash flow changes NA $ 19,000 $ $ $ $ 5,000 8,000 9,000 8,000 $ $ $ $ $ $ 2,000 3,000 -2,000 -2,000 1,000 20,000 $ $ - $ $ $ 3,000 Cash Inflow -1,500 Cash Outflow 1,500 $ 10,000 $ $ Cash Inflow Cash Inflow Cash Outflow Cash Outflow 20,000 Cash Inflow 20,000  Mixed beverage “Screwdriver” Given information Vodka Bottle size: 33.8 oz nBottle cost: $ 10.95 nDrink size: 1.5 oz n Orange Tonic juice n Bottle size: 30 oz nBottle cost: $ 4.5 nDrink size: 3.5 oz n n Ideal beverage cost% = 28% Cost per portion Vodka $10.95/33.8 = $0.32/oz $0.32*1.5oz = $0.48 Orange juice $4.5/30oz = $0.15/oz $0.15*3.5oz = $0.525 Screwdriver = $0.48+$0.525 Screwdriver = $1.01 Selling Price for Screwdriver= $1.005/0.28 Selling Price = $3.59 Cash Accounts receivable Inventory Prepaid expenses Land Building Equipment Furniture Depreciation Account payable Utility bills Payroll taxes Mortgage $ $ $ $ $ $ $ $ $ $ $ $ $ 15,000 1,000 1,500 1,000 20,000 30,000 7,000 2,500 8,000 5,000 1,000 1,000 15,000 Assets Current Assets Cash Accounts receivable Inventory Prepaid expenses Total current assets $ 15,000 $ 1,000 $ 1,500 $ 1,000 $ 18,500 Fixed Assets Land Building Equipment Furniture Depreciation Total fixed assets $ $ $ $ $ $ Total Assets $ 70,000 20,000 30,000 7,000 2,500 (8,000) 51,500 Liabilities & Capital (Equity) Liabilities Current Liabilities Account payable $ 5,000 Utility bills $ 1,000 Payroll taxes $ 1,000 Total current liabilities $ 7,000 Long-term Liabilities Mortgage $ 15,000 Total Liabilities $ 22,000 Capital $ 48,000 Total Liability & Equity $ 70,000 This table is the ‘Weekly Payroll Analysis Data’. Based on the given information, p Weekly Sales = $30,500 Covers per week = 900 Job Category Labor Hours % of Labor Hours Payroll $ % of Total Payroll Servers Bussers Dishwashers Hostesses Cashiers Cooks Bartenders Totals 155 60 75 50 60 180 65 645 24.03% 9.30% 11.63% 7.75% 9.30% 27.91% 10.08% 100.00% $800 $650 $570 $555 $575 $2,300 $510 $5,960 13.42% 10.91% 9.56% 9.31% 9.65% 38.59% 8.56% 100.00% 1. Please complete the weekly payroll anlaysis data by filling in the blanks. 2. Which two job categories account for over 50% of the total labor hours scheduled? Servers and Cooks 3. Which job category has the lowest labor cost per labor hour? $5.16 Servers 4. Which job category has the lowest sales per labor hour? $169.44 Cooks 5. Which job category has the lowest number of covers served for every hour scheduled? 5 Cooks the given information, please answer to the following questions. Labor Cost Sales per Covers per Labor Cost per Labor Labor Hour Labor Hour per Cover Hour $5.16 $196.77 5.81 $0.89 $10.83 $508.33 15 $0.72 $7.60 $406.67 12 $0.63 $11.10 $610.00 18 $0.62 $9.58 $508.33 15 $0.64 $12.78 $169.44 5 $2.56 $7.85 $469.23 13.85 $0.57 You don’t need to fill this blank!!
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi there! As usual here is the final file. Please don't hesitate to ask for any clarifications.

(6 points)
[January 2020 - March 2020]
Units
Purchased
January
1,000
February
1,000
March
1,000
Total
3,000
Beginning Inventory (The end of December, 2018):
1,000 units purchased at $ 18 each = ($ 18,000)
Month

Item
Sales = 3,000 units @ $20 each
Beginning Inventory (BI)
Purchases (P)
Ending Inventory (EI)

LIFO
$60,000
$18,000
$42,000
$18,000

COGS (Cost of Goods Sold) = BI+P-EI

$42,000

Expenses
Net Income (=Sales-COGS-Expenses)

$10,000
$8,000

ch 2020]
Cost/ea...


Anonymous
Awesome! Made my life easier.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags