restaurantcash-flow_exercises_13.xlsx

CF_1

Budget 2019 JANUARY FEBRUARY MARCH APRIL MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER

FOOD REVENUE 60,107 58,526 73,096 122,204 129,179 270,270 337,816 370,389 214,567 108,188 82,739 63,513 1,890,594 71.5%

BEVERAGE REVENUE 14,666 14,550 17,257 32,025 34,345 81,081 100,860 111,784 60,368 29,200 21,838 18,076 536,049 20.3%

F&B REVENUE 74,773 73,076 90,353 154,229 163,525 351,351 438,675 482,173 274,935 137,387 104,576 81,589 2,426,643 91.8%

OTHER REVENUE 3,301 3,237 4,027 8,087 15,378 38,378 48,515 54,390 26,113 6,952 5,045 4,216 217,640 8.2%

TOTAL REVENUE 78,073 76,313 94,381 162,316 178,903 389,729 487,190 536,564 301,048 144,340 109,622 85,805 2,644,283 100.0%

FOOD COST 16,071 15,698 19,514 33,008 34,894 73,824 92,543 101,707 58,119 28,999 22,202 16,990 513,569 27.2%

BEVERAGE COST 4,145 4,110 4,885 9,100 9,704 23,027 28,611 31,683 17,057 8,301 6,199 5,133 151,955 28.3%

F&B COST 20,216 19,808 24,399 42,107 44,598 96,851 121,154 133,390 75,177 37,300 28,401 22,123 665,524 27.4%

STAFF 52,667 52,667 52,667 52,667 52,667 52,667 52,667 52,667 52,667 52,667 52,667 52,667 632,000 23.9%

OVERTIME 5,465 5,342 7,550 12,985 14,312 33,127 41,411 45,608 25,589 11,547 8,770 6,006 217,713 8.2%

WAGES AND SALARIES 58,132 58,009 60,217 65,652 66,979 85,794 94,078 98,275 78,256 64,214 61,436 58,673 849,713 32.1%

OTHER op EXPENSES 8,211 8,034 9,911 17,067 18,887 41,181 51,538 56,820 31,820 15,157 11,520 9,007 279,153 10.6%

TOTAL COSTS 86,558 85,850 94,527 124,826 130,464 223,826 266,770 288,484 185,253 116,671 101,357 89,804 1,794,390 67.9%

GOP (P&L) = Profit = Total R – Total Costs -8,485 -9,537 -147 37,490 48,439 165,903 220,420 248,079 115,795 27,669 8,264 -3,999 849,893 32.1% Profit Margin

JANUARY FEBRUARY MARCH APRIL MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER

Incoming (invoices, cash, creditcards) 87940 84166 101483 169288 191760 395391 520729 582204 369502 184442 125242 97370 F&B revenue receipts are 90% of the current month and 10% of previous month. December 2018 F&B revenue was 60,000

VAT input (operating) 7807 7631 9438 16232 17890 38973 48719 53656 30105 14434 10962 8580 Other revenue receipts are 50% of current month and 50% of previous month. December 2018 other revenue was 10,000€

Sales VAT is 10% in all cases

Loans from owners

Bank loan

Other incomings

Total incomings 87940 84166 101483 169288 191760 395391 520729 582204 369502 184442 125242 97370

Outgoings

Operating outflows 31458 32209 36290 56075 70971 123241 181764 209942 161846 85713 51727 39883 F&B costs are paid 50% of current month and 50% of previous month. December F&B expenses were 17500

Other expenses, are paid 80% of current month and 20% of previous one. December other expenses were 10,000€

Total op. costs (exc salaries) 28426 27842 34310 59174 63485 138032 172692 190210 106997 52457 39921 31131 avg VAT of expenses is 14,7%

Salary 50608 50485 52693 58128 59455 123413 86554 90751 70732 56690 53913 96292

VAT output (operating) 4179 4093 5044 8699 9332 20291 25386 27961 15729 7711 5868 4576 2 extra payments in June and December

VAT reconciliation 3629 3539 4394 7533 8558 18682 23333 25696 14376 6723 5094 4004

Investments

Loan repayments 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000

taxes

insurance 2000

Interests 60 60 60 60 60 60 60 60 60 60 60 60

total outflow 86755 87292 94437 122797 140044 266396 292712 327448 248014 152186 111794 141240

Beginning balance 5000 6185 3059 10105 56597 108313 237308 465325 720080 841568 873824 887273

Closed balance 6185 3059 10105 56597 108313 237308 465325 720080 841568 873824 887273 843403

CF_2

Budget 2019 JANUARY FEBRUARY MARCH APRIL MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER

FOOD REVENUE 90,000 88,000 75,000 125,000 130,000 180,000 190,000 205,000 180,000 122,000 85,000 78,000 1,548,000 67.1%

BEVERAGE REVENUE 31,500 30,800 26,250 43,750 45,500 63,000 66,500 71,750 63,000 42,700 29,750 27,300 541,800 23.5%

F&B REVENUE 121,500 118,800 101,250 168,750 175,500 243,000 256,500 276,750 243,000 164,700 114,750 105,300 2,089,800 90.6%

OTHER REVENUE 3,301 3,237 4,027 8,087 15,378 38,378 48,515 54,390 26,113 6,952 5,045 4,216 217,640 9.4%

TOTAL REVENUE 124,801 122,037 105,277 176,837 190,878 281,378 305,015 331,140 269,113 171,652 119,795 109,516 2,307,440 100.0%

FOOD COST 27,000 26,400 22,500 37,500 39,000 54,000 57,000 61,500 54,000 36,600 25,500 23,400 464,400 30.0%

BEVERAGE COST 8,820 8,624 7,350 12,250 12,740 17,640 18,620 20,090 17,640 11,956 8,330 7,644 151,704 28.0%

F&B COST 35,820 35,024 29,850 49,750 51,740 71,640 75,620 81,590 71,640 48,556 33,830 31,044 616,104 29.5%

STAFF 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 720,000 31.2%

OVERTIME 12,480 12,204 10,528 17,684 19,088 28,138 30,501 33,114 26,911 17,165 11,980 10,952 230,744 10.0%

WAGES AND SALARIES 72,480 72,204 70,528 77,684 79,088 88,138 90,501 93,114 86,911 77,165 71,980 70,952 950,744 41.2%

OTHER op EXPENSES 18,720 18,306 15,792 26,526 28,632 42,207 45,752 49,671 40,367 25,748 17,969 16,427 346,116 15.0%

TOTAL COSTS 127,020 125,533 116,169 153,959 159,460 201,985 211,874 224,375 198,918 151,469 123,779 118,423 1,912,964 82.9% Prepare cash-flow.

P&L -2,220 -3,496 -10,892 22,878 31,419 79,394 93,141 106,765 70,194 20,183 -3,984 -8,907 394,476 17.1% Find any liquidity problem.

If we need to get cash we will use a bank loan with the following conditions: reinbursement of principal in 5 years (equal amounts) annual interests of 3% to be paid in equal parts

JANUARY FEBRUARY MARCH APRIL MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER

Incoming (invoices, cash, creditcards) 127435 134869 119231 177438 204471 282016 326971 356568 319002 216582 143813 123003 F&B revenue receipts are 80% of the current month and 20% of previous month. December 2018 F&B revenue was 60,000

VAT input (operating) 12480 12204 10528 17684 19088 28138 30501 33114 26911 17165 11980 10952 Other revenue receipts are 50% of current month and 50% of previous month. December 2018 other revenue was 10,000€

Sales VAT is 10% in all cases

Loans from owners

Bank loan

Other incomings

Total incomings 127435 134869 119231 177438 204471 282016 326971 356568 319002 216582 143813 123003

Outgoings

Operating outflows 52152 61629 55301 75896 90790 118338 136575 146918 135171 99171 67954 56082 F&B costs are paid 60% of current month and 40% of previous month. December F&B expenses were 17500

Other expenses, are paid 80% of current month and 20% of previous one. December other expenses were 10,000€

Total op. costs (exc salaries) 54540 53330 45642 76276 80372 113847 121372 131261 112007 74304 51799 47471 avg VAT of expenses is 14,7%

Salary 63909 63632 61956 69112 70516 130995 81930 84543 78340 68594 63408 113809

VAT output (operating) 8017 7839 6709 11213 11815 16735 17842 19295 16465 10923 7614 6978 2 extra payments in June and December

VAT reconciliation 4463 4364 347 6471 7273 11402 12660 13819 3504 6243 4365 3973 VAT reconciliation is paid 10 days after the month is closed. VAT reconciliation of december was 4000€

Investments vat included 20,000 40,000

Loan repayments 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000

taxes

insurance 2000

dividends 60000 60000

Interests 60 60 60 60 60 60 60 60 60 60 60 60

total outflow 122583 131686 139665 153539 230640 262795 233224 247340 259075 238067 137788 175924

Beginning balance 2000 6852 10036 -10398 13501 -12668 6553 100300 209528 269455 247969 253994

Closed balance 6852 10036 -10398 13501 -12668 6553 100300 209528 269455 247969 253994 201073