Question

1. Open the  “Budget” tab and create the following monthly cash-flow budget:
   1. Revenues:  Caveman Cooking plans to sell, on average, $8,000 worth of cooked meat products each month ($96,000 in sales/12 months).  However, seasonal sales expectations are as follows:
      1. Jan and Feb  31% of average each month
      2. March 62% of average
      3. April 99% of average
      4. May and June 124% of average each month
      5. July through October 149% of average each month
      6. November 99% of average
      7. December 31% of average
   2. Expenses:  Caveman Cooking has the following monthly expenses
      1. 20% of their monthly revenue is page out as wages.
      2. Rental fee on public parking lot where they set up their Food Truck is $150/month
      3. Operating loan:  Caveman Cooking borrowed money to purchase their Food Truck and BBQ/Smoker equipment.  The total amount borrowed was $25,000 and was financed over 7 years at a rate of 6.5%.  They make monthly payments.
      4. Miscellaneous expenses:  meat, sauces, smoking wood, lighters, gloves, fuel, and advertising are expected to be about 25% of monthly sales.  
   3. Answer the following questions:
      1. Is Caveman Cooking able to cover all of their monthly expenses?
      2. What would happen if their average monthly sales estimate is wrong, and they are only averaging $6,000?

Answer 1

Given
8000	Monthly Average Revenue	6000
0.31	Jan, Feb and Dec
0.62	Mar
0.99	April & Nov
1.24	May & June
1.49	July through October
0.2	Wages
0.25	Misc
150	Truck rent
25000	Loan for 7 years @ 6.5%
420.26	Monthly Payment (calculated using repayment schedule for 6 years)

Calculations

Average Monthly Sales			8000
	Revenue	Wages	Rent	Repayment (monthly payment)	Misc Expen	Total Expenses	Profit
Jan	2480	496	150	420.26	620	1686.26	793.74
Feb	2480	496	150	420.26	620	1686.26	793.74
Mar	4960	992	150	420.26	1240	2802.26	2157.74
April	7920	1584	150	420.26	1980	4134.26	3785.74
May	9920	1984	150	420.26	2480	5034.26	4885.74
June	9920	1984	150	420.26	2480	5034.26	4885.74
July	11920	2384	150	420.26	2980	5934.26	5985.74
August	11920	2384	150	420.26	2980	5934.26	5985.74
Sept	11920	2384	150	420.26	2980	5934.26	5985.74
Oct	11920	2384	150	420.26	2980	5934.26	5985.74
Nov	7920	1584	150	420.26	1980	4134.26	3785.74
Dec	2480	496	150	420.26	620	1686.26	793.74
Total	95760	19152	1800	5043.12	23940	49935.12	45824.88

A.
Yes
Annual profit	45824.88
Average Monthly Profit	3818.74

B
Profits of the company will decrease by decreasing the average monthly sales to 6000
Annual profit	32657.88
Average Monthly Profit	2721.49
Change in Profit by changing the average Sales from 8000 to 6000
Annual profit	13167
Monthly	1097.25