Microsoft Office Tutorials and References
In Depth Information
Cases and Places
Cases and Places
Apply your creative thinking and problem solving skills to design and implement a solution.
E ASIER •• M ORE D IFFICULT
1: Public Safety Division Budget Proposal
San Pueblo’s Public Safety division comprises three departments —Streets and Sanitation, Fire, and
Police. The departments have submitted fi gures comparing this year’s budget with next year’s budget in
four categories (Table 6–12). Develop a template that can be used to prepare each department’s budget
and the Public Safety division’s consolidated total budget within one workbook. Include this year’s bud-
get, next year’s budget, and the variance [(next year’s budget – this year’s budget) / this year’s budget]
for each expenditure. Indicate totals where appropriate. Create an embedded chart on the Public Safety
division’s worksheet comparing the division’s expenditures this year and next.
Table 6–12 San Pueblo’s Public Safety Division Expenditures
Streets and
Sanitation
Fire
Police
Next Year
This Year
Next Year
This Year
Next Year
This Year
Equipment
212000
198150
62350
78345
225175
220650
Maintenance
68350
62450
22750
17000
98375
102500
Miscellaneous
48125
44520
37600
38200
47500
32800
Salaries and
Benefi ts
116000
112400
198000
211000
150000
162750
2: Creating a Consolidated Balance Sheet
Jeans-For-Teens is a New York-based company that sells high-end jeans globally. After launching
its Web site fi ve years ago, the company has attracted so many clients from Europe that the own-
ers opened a shop in Paris. The New York and Paris shops’ assets last year, respectively, were: cash
$825,101 and $650,450; accounts receivable $557,190 and $325,860; marketable securities $345,213
and $211,450; inventory $845,258 and $326,120; and equipment $82,250 and $56,200. The liabilities
for each store were: notes payable $1,512,381 and $345,000; accounts payable $213,360 and $179,900;
and income tax payable $82,100 and $125,350. The stockholders’ equity was: common stock $812,300
and $235,000; and retained earnings $324,242 and $162,400.
Use the concepts and techniques presented in this project to design a template as a balance
worksheet to refl ect the fi gures above. Include totals for assets, liabilities, and stockholders’ equity. Use
the template to create a balance worksheet for the New York store, the Paris store, and the consolidated
balance worksheet for the corporation.
•• 3: Analyzing Company Profi ts by Category
Elite Software sells computer software and supplies. Merchandise is divided into six categories based on
profi t margin: individual application packages (22%), integrated application packages (9%), entertain-
ment software (16%), system software (25%), learning aids (18%), and supplies (10%). Last year’s sales
data has been collected for the State Street and Western Avenue Stores as shown in Table 6–13.
Develop a template that can be used to determine marketing strategies for next year. Include
sales, profi t margins, profi ts (sales × profi t margin), total sales, total profi ts, and functions to determine
the most and least sales, profi t margins, and profi ts. Use the template to create a worksheet for each
outlet, a consolidated worksheet for the entire company, and a chart on a separate sheet refl ecting the
company’s profi ts by category.
 
 
Search JabSto ::




Custom Search