Module 2 Excel Exercise (1)
xlsx
keyboard_arrow_up
School
Centennial College *
*We aren’t endorsed by this school
Course
MISC
Subject
Finance
Date
Jun 13, 2024
Type
xlsx
Pages
8
Uploaded by ProfessorMosquitoPerson1045
Module 2 Exercise
Name:
Rommel Companero
Student ID:
301365797
Exercise 2.1
Use the figures given below to complete the statement of income for the "Local" restaura
Calculate the percentage for each category. Food Sales
$970,028.12 Salaries and Wages
$300,443.40 Beverage Sales
$284,337.68 Direct Operating Expenses
$194,315.26 Beverage Cost
$72,724.82 Employee Benefits
$19,288.63 Food Cost
$356,607.56 Occupancy Costs
$57,277.25 Depreciation Expense
$95,576.14 Interest
$27,821.47 Exercise 2.2
Calculate the break-even point for Exercise 2.2 in sales dollars. Exercise 2.3
Complete the pro-forma for the upcoming year with the following projections.
You will need calculate the change, the projected sales, and the percentage of sale for e
1. Food sales are expected to decrease by 35% due to new competitor in town.
2. Beverage sales are also expected to decrease by 10%
3. Food and beverage cost % will remain the same.
4. The cost percentages of Salaries and Wages needs to decrease by 1%
5. Employee benefits will increases or decreases at the same rate as salaries and wages
6. Direct operating expenses will reduce by $2000
7. Occupancy costs will increase by $3000
8. Interest expense will increase by 5% due to rate hikes.
9. Depreciation will remain the same.
ant.
each category.
s.
Exercise 2.1
The Local Bistro
Income Statement
For year ending 20XX
Current Year
(%)
Sales
Food
$970,028.12
77.33%
Beverage
$284,337.68
22.67%
Total Sales
$1,254,365.79
100.00%
Cost of Sales
Food
$356,607.56
36.76%
Beverage
$72,724.82
25.58%
Salaries & Wages
$300,443.40
23.95%
Total COGS
$729,775.78
58.18%
Gross Profit
$524,590.02
41.82%
Other Controllable Expenses
Direct Operating Expenses
$194,315.26
15.49%
Employee Benefits
$19,288.63
1.54%
Total Controllable Expenses
$213,603.89
17.03%
Controllable Income
$310,986.13
24.79%
Non-Controllable Expenses
Occupancy Costs
$57,277.25
4.57%
Interest
$27,821.47
2.22%
Depreciation
$95,576.14
7.62%
Total Non-Controllables
$180,674.86
14.40%
Operating Profit/Loss
$130,311.27
10.39%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
food sales divide food cost
fbeverage sales divide beverage cost
should divide by total sales
|
|
|
|
|
|
|
|
|
|
|
|
|
|
V
Exercise 2.2
Break Ev
Break-even point ($) =
Contribution Margin Index
=
Break-even point ($) =
Break-even point ($) =
Contribution Margin Index
=
=
Break-even point ($) =
Break-even point ($) =
ven Formula Step-By-Step
Fixed Cost Contribution Margin Index
(Total Sales - Total COGS - Total Controllable Expenses)
Total Sales
Non-Controllable Expenses
Contribution Margin Index
Occupancy + Interest + Depreciation
Contribution Margin Index
310986.13
1254365.79
0.25
180674.86
0.25
728753.93
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Exercise 2.3
The Local Bist
Pro-Forma Income St
Current Year
Sales
Food
$ 970,028.12 Beverage
$ 284,337.68 Total Sales
$ 1,254,365.79 Cost of Sales
Food
$ 356,607.56 Beverage
$ 72,724.82 Salaries & Wages
$ 300,443.40 Total COGS
$ 729,775.78 Gross Profit
$ 524,590.02 Other Controllable Expenses
Direct Operating Expenses
$ 194,315.26 Employee Benefits
$ 19,288.63 Total Controllable Expenses
$ 213,603.89 Controllable Income
$ 310,986.13 Non-Controllable Expenses
Occupancy Costs
$ 57,277.25 Interest
$ 27,821.47 Depreciation
$ 95,576.14 Total Non-Controllable Expenses
$ 180,674.86 Operating Profit/Loss
$ 130,311.27
tro
tatement
Change
Upcoming Year ($)
(%)
$ (339,509.84)
$ 630,518.28 71.13%
$ (28,433.77)
$ 255,903.91 28.87%
$ (367,943.61)
$ 886,422.19 100.00%
$ (124,812.65)
$ 231,794.91 36.76%
$ (7,272.48)
$ 65,452.34 25.58%
$ (3,004.43)
$ 297,438.97 33.56%
$ (135,089.56)
$ 594,686.21 67.09%
$ (232,854.05)
$ 291,735.97 32.91%
$ (2,000.00)
$ 192,315.26 21.70%
$ (192.89)
$ 19,095.74 2.15%
$ (2,192.89)
$ 211,411.00 23.85%
$ (230,661.16)
$ 80,324.97 9.06%
$ 3,000.00 $ 60,277.25 6.80%
$ 1,391.07 $ 29,212.55 3.30%
- $ 95,576.14 10.78%
$ 4,391.07 $ 185,065.93 20.88%
$ (235,052.23)
$ (104,740.96)
-11.82%
Related Documents
Related Questions
Hi teacher please help me this question general accounting
arrow_forward
eBook
Show Me How
Office 365
Question Content Area
Profit center responsibility reporting
Championship Sports Inc. operates two divisions—the Winter Sports Division and the Summer Sports Division. The following income and expense accounts were provided from the trial balance as of December 31, 20Y9, the end of the fiscal year, after all adjustments, including those for inventories, were recorded and posted:
Line Item Description
Amount
Sales—Winter Sports Division
$9,600,000
Sales—Summer Sports Division
15,000,000
Cost of Goods Sold—Winter Sports Division
5,500,000
Cost of Goods Sold—Summer Sports Division
9,500,000
Sales Expense—Winter Sports Division
780,000
Sales Expense—Summer Sports Division
1,000,000
Administrative Expense—Winter Sports Division
700,000
Administrative Expense—Summer Sports Division
1,440,000
Advertising Expense
1,099,000
Transportation Expense
150,000
Accounts Receivable Collection Expense
67,000
Warehouse Expense
966,000…
arrow_forward
hello please solve home work
arrow_forward
Q Subject: financial accounting
arrow_forward
Please answer do not image format
arrow_forward
Please see below.
arrow_forward
Narrative report of each daily during the implementation of business plan which is banana ice cream. Please answer it in paragraph By Day 1, Day 2, Day 3, Day 4, Day 5.
arrow_forward
Homework i
A company reports the following sales-related information.
Sales, gross
Sales discounts
$295,000 Sales returns and allowances
5,900 Sales salaries expense
Prepare the net sales portion only of this company's multiple-step income statement.
Net sales
Multiple-Step Income Statement (Partial)
Saved
< Prev.
$ 22,000
11,900
10 of 10 HH
hp
n
arrow_forward
Hello teacher please solve question
arrow_forward
Safari File Edit View
L
100
History Bookmarks Window Help
Week 3 Overview - MGT6017_X70_Operations Management
JUL
26
===
Problems
1. A manufacturing company has the following financial information (in millions of $):
Sales
Cost of goods sold
Direct labor
Purchased materials
Overhead
All other costs
Pretax earnings
Administrative costs.
Pretax earnings
$823
85
Criteria
310
tv
94
a. What is the profit leverage effect of purchasing in this company?
b. What is the increase in earnings from an 8 percent savings in purchasing?
2. A service company has the following financial information (in millions of $)
Sales
Cost of outsourced facilitating goods
Cost of in-house services provided
250
84
$510
210
199
77
prod.reader-ui.prod.mheducation.com
24
a. What is the profit leverage effect of reducing the cost of the facilitating goods in this company?
b. It has been suggested that the in-house services costs could be reduced by 10 percent in the coming year by implementing lean
systems. What effect…
arrow_forward
Revenues:
Food and beverage
Delivery service
Total revenues
Restaurant operating costs:
Chipotle Mexican Grill, Inc.
Operating Income
For the Years Ended December 31
(in thousands)
Year 2
Amount
Year 2
Year 1
Percent
Amount
Year 1
Percent
$7,457
100 %
$5,921
100 %
90
%
64
%
$7,547
100 %
$5,985
100 %
Food, beverage, packing
$(2,309)
%
$(1,933)
906
%
Labor
(1,918)
90
%
(1,593)
%
Occupancy (rent)
(416)
%
(388)
%
%
Marketing, utilities, other
(1,197)
%
(1,030)
General and administrative
(607)
%
(466)
%
Depreciation
(255)
%
(238)
%
%
Miscellaneous expenses
(40)
%
(47)
%
Total operating expenses
$(6,742)
$(5,695)
%
%
%
$290
$805
Operating income
h. Based on the calculations above, which of the following statements are correct?
arrow_forward
Help please
arrow_forward
calculate this account questions profit ratio
arrow_forward
Required information
Problem 9-2A (Algo) Allocation of indirect expenses to departments LO P2
[The following information applies to the questions displayed below.]
National Retail has two departments, Housewares and Sporting.
Indirect expenses for the period follow.
Rent
Advertising
Insurance
Total
$ 45,000
25,000
10,000
$ 80,000
The company occupies 4,000 square feet of a rented building. In prior periods, the company divided the $80,000 of
indirect expenses by 4,000 square feet to find an average cost of $20 per square foot, and then allocated indirect
expenses to each department based on the square feet it occupied.
The company now wants to allocate indirect expenses using the allocation bases shown below.
Department
Housewares
Sporting
Total
Square Feet
2,040
1,960
4,000
Sales
$ 220,000
280,000
$ 500,000
Value of Insured
Assets
$ 44,000
56,000
$ 100,000
arrow_forward
Hello tutor please provide this question solution general accounting
arrow_forward
cost accounting
arrow_forward
Express the total costs, net profit (NP) in money and as a percentage in the table below.
Sales
Food Costs
Labor Costs
Overheads
Total Costs
Net Profit
Net Profit percent
$140.55
$40.99
$50.50
$23.34
$175.98
$55.45
$61.25
$34.00
$268.94
$81.38
$90.45
$74.88
$555.65
$120.00
$155.98
$111.54
$1010.80
$300.70
$240.54
$230.78
arrow_forward
General Accounting Question Solution Please Provide
arrow_forward
Need help with this question solution general accounting
arrow_forward
Provide correct option general accounting question
arrow_forward
What questions 36,37,38,39
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you

College Accounting, Chapters 1-27
Accounting
ISBN:9781337794756
Author:HEINTZ, James A.
Publisher:Cengage Learning,
Century 21 Accounting Multicolumn Journal
Accounting
ISBN:9781337679503
Author:Gilbertson
Publisher:Cengage
Related Questions
- Hi teacher please help me this question general accountingarrow_forwardeBook Show Me How Office 365 Question Content Area Profit center responsibility reporting Championship Sports Inc. operates two divisions—the Winter Sports Division and the Summer Sports Division. The following income and expense accounts were provided from the trial balance as of December 31, 20Y9, the end of the fiscal year, after all adjustments, including those for inventories, were recorded and posted: Line Item Description Amount Sales—Winter Sports Division $9,600,000 Sales—Summer Sports Division 15,000,000 Cost of Goods Sold—Winter Sports Division 5,500,000 Cost of Goods Sold—Summer Sports Division 9,500,000 Sales Expense—Winter Sports Division 780,000 Sales Expense—Summer Sports Division 1,000,000 Administrative Expense—Winter Sports Division 700,000 Administrative Expense—Summer Sports Division 1,440,000 Advertising Expense 1,099,000 Transportation Expense 150,000 Accounts Receivable Collection Expense 67,000 Warehouse Expense 966,000…arrow_forwardhello please solve home workarrow_forward
- Narrative report of each daily during the implementation of business plan which is banana ice cream. Please answer it in paragraph By Day 1, Day 2, Day 3, Day 4, Day 5.arrow_forwardHomework i A company reports the following sales-related information. Sales, gross Sales discounts $295,000 Sales returns and allowances 5,900 Sales salaries expense Prepare the net sales portion only of this company's multiple-step income statement. Net sales Multiple-Step Income Statement (Partial) Saved < Prev. $ 22,000 11,900 10 of 10 HH hp narrow_forwardHello teacher please solve questionarrow_forward
- Safari File Edit View L 100 History Bookmarks Window Help Week 3 Overview - MGT6017_X70_Operations Management JUL 26 === Problems 1. A manufacturing company has the following financial information (in millions of $): Sales Cost of goods sold Direct labor Purchased materials Overhead All other costs Pretax earnings Administrative costs. Pretax earnings $823 85 Criteria 310 tv 94 a. What is the profit leverage effect of purchasing in this company? b. What is the increase in earnings from an 8 percent savings in purchasing? 2. A service company has the following financial information (in millions of $) Sales Cost of outsourced facilitating goods Cost of in-house services provided 250 84 $510 210 199 77 prod.reader-ui.prod.mheducation.com 24 a. What is the profit leverage effect of reducing the cost of the facilitating goods in this company? b. It has been suggested that the in-house services costs could be reduced by 10 percent in the coming year by implementing lean systems. What effect…arrow_forwardRevenues: Food and beverage Delivery service Total revenues Restaurant operating costs: Chipotle Mexican Grill, Inc. Operating Income For the Years Ended December 31 (in thousands) Year 2 Amount Year 2 Year 1 Percent Amount Year 1 Percent $7,457 100 % $5,921 100 % 90 % 64 % $7,547 100 % $5,985 100 % Food, beverage, packing $(2,309) % $(1,933) 906 % Labor (1,918) 90 % (1,593) % Occupancy (rent) (416) % (388) % % Marketing, utilities, other (1,197) % (1,030) General and administrative (607) % (466) % Depreciation (255) % (238) % % Miscellaneous expenses (40) % (47) % Total operating expenses $(6,742) $(5,695) % % % $290 $805 Operating income h. Based on the calculations above, which of the following statements are correct?arrow_forwardHelp pleasearrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- College Accounting, Chapters 1-27AccountingISBN:9781337794756Author:HEINTZ, James A.Publisher:Cengage Learning,Century 21 Accounting Multicolumn JournalAccountingISBN:9781337679503Author:GilbertsonPublisher:Cengage

College Accounting, Chapters 1-27
Accounting
ISBN:9781337794756
Author:HEINTZ, James A.
Publisher:Cengage Learning,
Century 21 Accounting Multicolumn Journal
Accounting
ISBN:9781337679503
Author:Gilbertson
Publisher:Cengage