PLEASE SHOW THE WORK ON EXCEL

Integrated Income Statement and Balance sheet for the Bar that the class wants to open. According to the assumptions listed below.

In addition to the Income Statement and Balance Sheet please calculate the following:

• Current Ratio
• Quick Ratio
• DSO
• ROE (Return on Equity)
• ROTA (Return on Total Assets)
• ROIC (Return on Invested Capital)
• Assuming the Bar will be in business for 10 years. What are you willing to pay for it today if you want a 15% return on your investment?

Business Assumptions:

• Bar
• Seating Capacity 50 people. Always full, no empty seats.
• Operates 5 days per week.
• Operates 50 weeks per year.
• Operates 11 hours per day that it is open.
• A customer spends $ 50 per visit to the bar.
• A customer spends 1 hour at the bar per visit.
• Credit Sales account for 30% of Sales. Credit Card Processing fee is 2%
• Credit Sales are collected in 30 Days
• Cash Sales are 70% of Sales
• Cost of Good Sold (Liquor) is $ 15 dollars per customer visit. (Sales of $ 50 cost of $15)
• Total Staff is 16 people
• Pay is $ 15 per hour. No overtime. No one can work more than 40 hours per week
• Employees are paid every 2 weeks.
• Inventory turnover ratio is 20 times
• Rent is $ 20 per square foot per month. Bar is 1,000 sqft
• Alcohol serving permit. $ 50,000 per year
• Insurance is $ 2,000 per month
• Working Capital (cash on hand) should be at least 5% of Sales
• You pay for booze in 30 days. You need this number to get your Accounts Payable.
• You have a long term lease. This is a long term liability. However, the current period (or year) of rent is considered a short term liability.
• Since there is no depreciation there is no difference deferred tax. However, taxes are paid quarterly, 30 days in arears. So Oct, Nov, and Dec taxes will be paid end of Jan. Therefore there will be a taxes payable current liability. The rent is paid monthly but 30 days in arears. So there will be a short term rent payable, equal to one month's rent