I need help with this excel case entering the proper formulas

The Postal Service AnalysisConstants2013201420152016No Saturday delivery cost savingsNA$3,000,000,000### $3,000,000,000Minimum cash needed to start yearNA$2,000,000,000### $2,000,000,000Expected work forceNA620,000600,000580,000Expected increase in average compensaTonNA2%2%3%Expected decrease in Frst class unitsNA5%6%7%Pension and health costs, no pre-fundingNA$3,000,000,000### $3,000,000,000Pension and health pre-funding costNA$5,000,000,000### $5,000,000,000Expected other than Frst class revenueNA$30,000,000,000 $32,000,000,000 $34,000,000,000Interest rate on debt owedNA3.5%3.5%3.5%Expected other operaTng expenseNA$9,000,000,000 $9,200,000,000 $9,400,000,000Other Business net incomeNA$1,000,000,000 $2,000,000,000 $4,000,000,000Inputs2013201420152016Allowed to Enter Other Business? (Y/N)NANANASaturday Delivery Eliminated? (Y/N)NANANAIncrease in Price of ±irst Class Stamp ($.xx)NARemove BeneFts Pre-±unding? (Y/N)NANANAExpected % Change in Energy PricesNAAdded a²riTon in yearNASummary of Key Results2013201420152016Net Income (Loss)in YearNAEnd-of-year cash on handNAEnd-of-year debt owedNACalcula±ons2013201420152016Average yearly employee compensaTon$75,000ABCDE123456789101112131415161718192021222324252627282930

Excel CaseTHE POSTAL SERVICE ANALYSISDecision Support Using ExcelPREVIEWThe postal service of a large country has been losing money for years. Various opera±ng reforms and Fnancial reforms have beenproposed. In this case, you will use Excel to examine whether the proposals can restore the postal service to Fnancial health.BACKGROUNDA large country’s postal service has been losing money for years. The postal service is o²cially a private en±ty, but the governmentoversees its Fnances and opera±ng decisions and provides loans in the case of cash shor³alls. The postal service faces seriouschallenges. Proposed opera±ng changes and Fnancial changes need approval from appropriate unions and the legislature beforethey can be implemented.ChallengesThe postal service has been hurt severely by the rise of the Internet. Every e-mail sent is a le´er not wri´en and a Frst-class stampthat was not purchased from the postal service. Also, the postal service’s shipping business has been hurt by the rise of privateshipping companies, such as µedEx.The postal service owns many trucks and airplanes. The related energy costs are signiFcant, so increases in the cost of fuel posea risk. Analysts es±mate that a 1 percent increase in the market price of fuel adds $25 million a year to postal service costs.Possible Opera±onal and µinancial ChangesRevenue would improve by raising the price of a Frst-class stamp, which costs less now in real terms than it did in 2001. But, bylaw, the postal service cannot raise the price of a stamp by more than the rate of in¶a±on. This rate has been low in recent years,so the price of a stamp has increased by a penny per year at most in the last decade.In 2006, the government passed a law requiring the postal service to prepay 75 years of pension and re±ree healthcare costs bythe end of 2016. In e·ect, the postal service was forced to prepay re±rement beneFts of workers who are not yet born! Thisrequirement has increased yearly costs by $5 billion. The postal service wants the prepayment requirement li¸ed.The postal service says it could save $3 billion per year by discon±nuing Saturday home mail service, and surveys have shownthat most ci±zens would be willing to give up Saturday service. However, this policy would require legisla±ve ac±on, and lawmakershave refused to approve it.The postal service is prohibited by law to par±cipate in other businesses. µor example, it cannot put adver±sing on its deliverytrucks. The postal service wants to partner with drugstore chains to deliver prescrip±on medicines, but it is prohibited from doing so.The postal service had 640,000 employees at the end of 2013. In the next Fve years, it expects to shed 20,000 employees peryear by normal a´ri±on. Management thinks it could lay o· up to 20,000 addi±onal employees per year and maintain the same levelof service, but the legislature would need to approve these layo·s. So far, the postal unions have persuaded the legislature toprevent layo·s.If the postal service loses money in a year, the government covers the shor³all with a loan. In e·ect, the government serves asthe postal service’s “bank.” In recent years, the postal service has lost money, and now its loans under the line of credit total $12billion. By law, the postal service cannot owe the government more than $15 billion. Lawmakers are now worried about the fate ofthe post o²ce. They want the amounts owed to decrease, but it is becoming increasingly clear that losses and more debt areinevitable unless changes occur.A legisla±ve commi´ee has asked you to create a DSS model of postal service opera±ons with and without reforms. Your DSSmodel needs to account for the e·ects of possible opera±ng changes. Your model will let you develop “what-if” scenarios with theinputs, see the Fnancial results, and then help you decide if the post o²ce can be made into a proFtable opera±on.ASSIGNMENT 1: CREATING A SPREADSHEET µOR DECISION SUPPORTIn this assignment, you will produce a spreadsheet that models the postal service business for the next Fve years. Then, inAssignment 2, you will write a memorandum that documents your analysis and conclusions.

• 1495071982-Post Office Case original.xlsx
• 1495071987-Postal Service.docx

SuperTutor

(15)

Status NEW Posted 18 May 2017 01:05 AM My Price 11.00

-----------

Attachments

file 1495071987-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer