Microsoft Office Tutorials and References
In Depth Information
EX 246
Excel Chapter 3 What-If Analysis, Charting, and Working with Large Worksheets
Table 3–18 Chesterton Trib Bimonthly Projected Earnings and Expenditures
Revenue
February
April
June
August
October
December
Subscriptions
12178.30
8391.50
15714.50
16340.10
12567.25
12800.15
Advertising
4130.20
6425.00
4123.15
5023.30
7015.75
9273.20
Assumptions
Marketing
15.60%
Payroll
21.50%
Commissions on
Advertising
3.25%
Production Costs
12.50%
Reportorial Expenses
5.00%
One shareholder lobbied to reduce marketing expenditures by 3% and payroll costs by 5%.
Perform a what-if-analysis refl ecting the proposed changes in expenditure assumptions. The reduction in
expenditures should result in a total operating income of $59,696.91 or an increase of $9,118.59. Submit
the workbook and results of the what-if analysis as requested by your instructor.
•• 3: Projected Used-Truck Savings
Cousin Abe and Aunt Esther own a paint company. Their good friend Billie Bob is retiring after 35 years
of delivering the morning newspaper. Billie Bob has offered them the opportunity to take his place
next year. The job requires, however, that they own a truck. They need to save enough money over the
next six months to buy a $10,000 used truck.
They have job orders at their paint company for the next six months: $22,150 in July, $22,480
in August, $32,900 in September, $31,200 in October, $45,301 in November, and $32,190 in
December. Each month, they spend 34.55% of the job order income on material, 3.00% on rollers
and brushes, 4.75% on their retirement account, and 39.5% on food and clothing. The remaining
profi ts (orders
total expenses) will be put aside for the used truck. Aunt Esther’s retired parents have
agreed to provide a bonus of $250 whenever the monthly profi t exceeds $2,000. Use the concepts and
techniques presented in this project to create and format the worksheet.
Cousin Abe has asked you to create a worksheet that shows orders, expenses, profi ts, bonuses,
and savings for the next six months, and totals for each category. Aunt Esther would like to save for
another used truck for $17,000. She has asked you to (a) perform a what-if analysis to determine the
effect on the savings by reducing the percentage spent on material to 25% (answer total savings =
$16,084.49), and (b) with the original assumptions, goal seek to determine what percentage of profi ts
to spend on food and clothing if $15,000 is needed for the used truck (answer = 29.165%). Submit the
workbook and results of the what-if analysis as requested by your instructor.
•• 4: College Expense and Resource Projections
Make It Personal
Attending college with limited resources can be a trying experience. One way to alleviate some of the
fi nancial stress is to plan ahead. Develop a worksheet following the general layout in Table 3–19 that shows
the projected expenses and resources for four years of college. Use the formulas listed in Table 3–20 and the
concepts and techniques presented in this project to create the worksheet.
Search JabSto ::




Custom Search