Log in
Accounting
1
answer
0
watching
75
views
11 Jun 2018

Given the following information, create a personal cash flowprojection:

A. You earn $26.00 per hour in an hourly position.

B. You can expect to work

a. 28 hours per week from January through June

b. 20 hours per week from July through October

c. 16 hours per week November and December

d. The company is closed for holidays for the last week ofDecember and first week of January; 0 hours per week for those 2weeks

C. Your net pay is 85% of your gross pay

D. Your rent is $1,000 per month and includes utilities

E. Groceries are $400 per month

F. Transportation (car/bus/gas) is $90 per month

G. Clothing (purchase/cleaning) is $1,000 per year

H. Miscellaneous purchases are $500 per month
Provide the following: 1. Prepare a monthly cash flow showingincome and expenses 2. Can you save enough during the year to coverone month of expenses? 3. How much can you reasonably spend onentertainment (eating out, movies, concerts, theme parks, etc.) 4.Is there a positive cash flow in each month? Or do you need to savein some months to cover others? If so, which months are surplus,and which are short? 5. What are 3 things you can do to increaseyour income? 6. What are 3 things you can do to reduce yourexpenses? 7. Can you live a reasonably comfortable life within thefinancial boundaries set forth in the assignment? 8. How does yoursituation change is you add in $300 of student loan payments permonth?

Watch
1
answer
0
watching
75
views

For unlimited access to Homework Help, a Homework+ subscription is required.

Reid Wolff
Reid WolffLv2
12 Jun 2018

Unlock all answers

Get 1 free homework help answer.
Already have an account? Log in
Share

Related questions

You will do this by developing a cash proforma budget for hisbusiness venture (note: a cash proforma is a spreadsheet that helpsforecast income and expenses over a period of time). If donecorrectly, it can be used to plan for and manage the business.

He is planning to open his coffee house on the 1st ofMarch 2016.

Assignment

Using Microsoft Excel, construct a monthly proforma cash budgetfor your client for the first year of operations.

Use the file called “Starlucks Coffee HouseTemplate” for your starting point. This is the filethat must be downloaded and used as the basis for your assignment.Do not make any changes to this pre-designed template other thaninstructed.

Place the cash proforma analysis on a worksheet labeled“Cash Flow".

Place all your case assumptions data on a separate worksheet andlabel the worksheet "Assumptions” (note:each piece of data must appear in its own cell on the Assumptionsheet).

Place your startup costs on a third worksheet labeled"Startup Costs"

Create an additional worksheet for your scenario onerecommendation. Label the worksheet“Recommendation”.

Charts (graphs)

You will be creating two appropriate charts so create and label twoadditional worksheets for the charts (each chart will be in its ownworksheet).

Chart One – “Monthly Product Revenue” – thischart should show the total monthly

revenue of each product for the entire year.

Chart Two: – “Total Product Net Income” – thischart should show the total net income, by month, for the entireyear. This will be used to determine any trends or projections inproduct sales.

Make sure both charts are formatted correctly (i.e. appropriatetitle, legend where appropriate, data series properly labeled,etc.) and they are appropriate for business use.

Assumptions – provided by yourclient:

Product Selling Prices:

Frappuccino beverages will sell for $5.75 each
Espresso beverages will sell for $4.75 each
Hot chocolate beverages will sell for $3.25 each
Brewed coffee will sell for $2.10 each
Smoothies sell for $4.50 each
Pastries will sell for $3.25 each

Cost of Goods Sold (COGS):

Frappuccino ingredients cost $2.15 per unit

Espresso ingredients cost $2.05 per unit
Hot chocolate ingredients cost $1.23 per unit
Brewed coffee ingredients cost $.95 per unit

Smoothie ingredients cost $1.05 per unit

Pastries ingredients cost $1.35 per unit

The building rent is $2,500 per month.

Phone, Internet and Wi-Fi will cost about $600 per month.

Electricity should average about $800 a month.

Insurance will be $1200 a month.

Advertising and promotion will be $500 a month.

Assume that each month contains 4.2 weeks.

Operating Hours

The coffee house will be open six days a week (closed onMonday).

During the week (Tuesday – Friday), the coffee house will beopen from 7am – 11 pm. It will need three hourly employees and amanager during these hours.

On the weekends (Saturday and Sunday), the coffee house will beopen 10am – 10 pm and will need five hourly employees and amanger.

Your client will be the manager and draw a salary of $40,000 peryear. He will also work during the busiest times, and fill in forthe assistant manager on days off and sick days. The assistantmanager will receive an annual salary of $30K. The hourly workerswill be paid $7.25 an hour.

During the week, your client expects an average of 25 customersan hour. During the weekend, he expects an average of 45 customersan hour.

Demand Rate

Your client believes that on average 1/4 of customers will buy aFrappuccino, half will buy an espresso, a quarter of them will buya Hot chocolate, 25% will purchase brewed coffee, one out of 5 willbuy a smoothie, and 3 out of 4 customer will purchase a pastryitem.

Startup costs for the coffee house include:

Kitchen equipment: $26,275

Sales equipment: $3,500
Coffee house fixtures (chairs, tables etc.): $8,500

Start-up inventory: $1,875

Pre-opening marketing: $2,000

Licenses: $1,900

Security deposit: $5,000

First Insurance Payment: $1200

Your client has $5,000 and plans to borrow the rest from thebank with a five-year loan at 7.1% interest.

(Use the appropriate financial function to calculate the monthlytax payment)

Assume a tax rate of 15% if Income Before Taxes (IBT) is equalto or is greater than $9,000. Assume a tax rate of 10% if IBT isless than $9,000.

(Use the appropriate logical function to calculate the monthlytax payment)

Your client feels that starting with April, monthly revenue forall products, will grow an average of 1.25% per month.

Scenario One: “What if” Analysis

A former school friend of your client is an amateursinger/guitarist and has planted the idea in your client’s head ofhiring him to provide live entertainment on the weekends (Saturdayand Sunday evenings 6 - 11). The friend states he can guarantee 7more customers per hour if your client will hire him at $1,000 amonth.

What is your recommendation: Would it be a profitable idea tohire the musician?


Recommendation:

Show your client how your recommendation would affect his bottomline by recreating the proforma for this scenario, and applying thedata analysis to determine profitability.

(hint) you do not have to start this new proforma from scratch,but note, it is a completely independent proforma. The new proforma(worksheet) must update accordingly from the data worksheets.


Plan on showing your analysis and discussing the proforma changesthat occur under both scenarios and how it affectsprofitability.

Use a formatted text box (not a comment) to explain yourrecommendations. This will be approximately a 2-3 paragraphwrite-up. The recommendation should be complete, concise, andcorrect (use spell check).

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Month 12

Revenue:

Frappuccino

Espresso

Hot chocolate

Brewed coffee

Smoothies

Pastries

Monthly Revenue:

Expenses:

Cost of Goods Sold (COGS)

Rent

Phone

Electricity

Insurance

Advertising

Hourly Wages

Salaries

Loan Payment

Total Monthly Expenses:

Income Before Taxes (IBT)

Tax

Net Income

Cash Flow

The purpose of this case study is to help you integrate the managerial accounting concepts that were covered in class and apply them to a real-world business setting. Business Description You will assume the role of an entrepreneur starting a small company. Your company will produce and sell gourmet cupcakes through a storefront in a location of your choice. Your business is scheduled to launch on January 1, 2018. Cost information: 1 Cost of goods sold: a. Ingredients are .30 per cupcake b. Boxes and Cupcake Cups are .05 per cupcake 2 Equipment that will be required to be acquired at the start of business includes ovens, racks, display case, counter, cash register, and other baking equipment and will cost $140,000. The equipment is expected to last 10 years without salvage value. Straight-line method of depreciation should be used. 3 On average one person can make, bake, and decorate 24 cupcakes per hour. Bakers are paid $15.00 per hour. 4 Sales personnel are required for 56 hours per week and are paid $10.00 per hour. 5 Monthly rent, which includes utilities, is $1,500. 6 Business insurance is purchased at a cost of $1,000 per year. 7 Advertising costs are expected to be $6,000 per year.   Requirements: Using separate tabs in a spreadsheet, provide your answers for the following. 1 Name your company (5 points) 2 What and how much are the variable costs? Present each item in cost per cupcake basis (5 points). 3 What and how much are the fixed costs? Present each item in total cost per year (5 points). 4 Write out the annual cost formula in Y = a + bX format (5 points). 5 Develop a price using a target price (what do you think a customer will pay for one of your cupcakes? What are other companies charging for gourmet cupcakes?) Provide support for your target price (10 points). 6 Develop a price using cost-based pricing assuming that you expect to sell 24,000 cupcakes the first year and would like to have a 20% profit (10 points). 7 Using the price you calculated using cost based pricing in #6, calculate contribution margin per cupcake and contribution margin ratio (5 points). 8 Based on the price you calculated in #6, calculate how many cupcakes need to be sold in order to break-even. Calculate how much sales in dollars are needed to break-even (10 points). 9 Prepare a cost/volume/profit chart (15 points). 10 Prepare the company’s forecasted functional income statement for the year ended on 12/31/2018 based on the sale of 36,000 cupcakes (10 points). 11 Based on the sale of 36,000 cupcakes during the first year of business, calculate the margin of safety and the operating leverage for the business. What do these figures tell you about how risky the business is? (15 points) 12 If sales could increase by 10% (to 39,600 cupcakes), by how much in dollars would net operating income increase? By what percentage would net operating income increase? Use the formula for leverage to calculate (5 points). 13 Prepare a contribution format income statement assuming sales of 39,600 cupcakes (10 points). 14 Calculate how many cupcakes need to be sold in order to make a $50,000 target profit for the year (5 points). 15 Calculate the total amount of cash that will be needed at the start of the business in order to buy all necessary equipment and machines and cover the first three months of fixed expenses. This amount will be your initial investment in the business. Note that the equipment will be paid in full on the first day of business. Other expenses will be paid on a monthly basis (10 points). 16 Prepare a cash budget for the company’s first year of operations based on the sales calculated in item 14. Assume all sales are cash sales and that all costs and expenses are paid in cash. You decide to maintain a minimum cash balance of $5,000 (15 points). 17 After your thorough analyses of costs, sales, and profitability of your cupcake business throughout this project, what is your overall impression of the future potential of the business? Provide a short assessment (10 points).

The purpose of this case study is to help you integrate the managerial accounting concepts that were covered in class and apply them to a real-world business setting. Business Description You will assume the role of an entrepreneur starting a small company. Your company will produce and sell gourmet cupcakes through a storefront in a location of your choice. Your business is scheduled to launch on January 1, 2018.

Cost information:

1 Cost of goods sold: a. Ingredients are .30 per cupcake b. Boxes and Cupcake Cups are .05 per cupcake

2 Equipment that will be required to be acquired at the start of business includes ovens, racks, display case, counter, cash register, and other baking equipment andwill cost $140,000. The equipment is expected to last 10 years without salvage value. Straight-line method of depreciation should be used.

3 On average one person can make, bake, and decorate 24 cupcakes per hour. Bakers are paid $15.00 per hour.

4 Sales personnel are required for 56 hours per week and are paid $10.00 per hour.

5 Monthly rent, which includes utilities, is $1,500.

6 Business insurance is purchased at a cost of $1,000 per year.

7 Advertising costs are expected to be $6,000 per year.

Requirements: Using separate tabs in a spreadsheet, provide your answers for the following.

1 Name your company (5 points)

2 What and how much are the variable costs? Present each item in cost per cupcake basis (5 points).

3 What and how much are the fixed costs? Present each item in total cost per year (5 points).

4 Write out the annual cost formula in Y = a + bX format (5 points).

5 Develop a price using a target price (what do you think a customer will pay for one of your cupcakes? What are other companies charging for gourmet cupcakes?)Provide support for your target price(10 points).

6 Develop a price using cost-based pricing assuming that you expect to sell 24,000 cupcakes the first yearand would like to have a 20% profit (10 points).

7 Using the price you calculated using cost based pricing in #6, calculate contribution margin per cupcake and contribution margin ratio(5 points).

8 Based on the price you calculated in #6, calculate how many cupcakes need to be sold in order to break-even. Calculate how much sales in dollars are needed to break-even (10 points). 9 Prepare a cost/volume/profit chart (15 points).

10 Prepare the company’s forecasted functional income statement for the year ended on 12/31/2018 based on the sale of 36,000 cupcakes (10 points).

11 Based on the sale of 36,000 cupcakes during the first year of business, calculate the margin of safety and the operating leverage for the business. What do these figures tell you about how risky the business is? (15 points)

12 If sales could increase by 10% (to 39,600 cupcakes), by how much in dollars would net operating income increase? By what percentage would net operating income increase? Use the formula for leverage to calculate (5 points).

13 Prepare a contribution format income statement assuming sales of 39,600 cupcakes (10 points).

14 Calculate how many cupcakes need to be sold in order to make a $50,000 target profit for the year (5 points).

15 Calculate the total amount of cash that will be needed at the start of the business in order to buy all necessary equipment and machines and cover the first three months of fixed expenses. This amount will be your initial investment in the business. Note that the equipment will be paid in full on the first day of business. Other expenses will be paid on a monthly basis (10 points).

16 Prepare a cash budget for the company’s first year of operations based on the sales calculated in item 14. Assume all sales are cash sales and that all costs and expenses are paid in cash. You decide to maintain a minimum cash balance of $5,000 (15 points).

17 After your thorough analyses of costs, sales, and profitability of your cupcake business throughout this project, what is your overall impression of the future potential of the business? Provide a short assessment (10 points).

Related Documents

ACC 250 Lecture Notes - Lecture 15: Payroll Tax, Current Liability, Promissory Note
OC1069651
ACC 131 Lecture Notes - Lecture 3: Accrual, Accounting Information System, Accounting
violetllama898
ACC 100 Lecture Notes - Cash Flow Statement, Deferral, Deferred Income
redmammoth987

Weekly leaderboard

Home
Homework Help 3,900,000
Accounting 580,000
Start filling in the gaps now
Log in