Microsoft Office Tutorials and References
In Depth Information
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 ﬁ 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
• 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 ﬁ 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 reﬂ ect the ﬁ 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 Proﬁ ts by Category
Elite Software sells computer software and supplies. Merchandise is divided into six categories based on
proﬁ 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, proﬁ t margins, proﬁ ts (sales × proﬁ t margin), total sales, total proﬁ ts, and functions to determine
the most and least sales, proﬁ t margins, and proﬁ 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 reﬂ ecting the
company’s proﬁ ts by category.