please fill in the excel sheet using information provided in the text

U.S. population. According to the U.S. Census Bureau, in 2006 baby boomers represented 26% of the populace. In Let's Go Acro manufactures travel trailers bought primarily_ that year there were just under 78 million boomers living by young families and retirees interested in a light, low-cost in the United States, with the largest populations living in trailer that can easily be pulled by a mid-sized family car. The _California, Texas, New York, Florida, and Pennsylvania. market for travel trailers has expanded nicely over the past _ Research indicates that for an organization to meet the few years due to the number of families seeking a relatively_ needs of the senior marker, including baby boomers, the low-cost, outdoor vacation experience. But in the view of Let's following must be addressed: Go Aero's president, Mark Newman, the real growth in the future is in the retiree market. Newman believes the vigorous - Independence and control, health of the average retiree, coupled with theyotional trend toward a return to nature, will translate into continuing sales - Intellectual stimulation and self-expression, growth for Let's Go. As Newman loves to say, "camping - Security and peace of mind, - Quality and value. recently moved from number seven to number six on the list Seniors respond to benefit-driven messages; to attract of top 10 leisure activities in the United States, and the baby them, advertising has to communicate tangible benefirs boomers are getting older every day." rather than features and amenities. THE RETIREE MARKET MARKETING AND SALES Baby boomers (born between 1/1/46 and 12/31/64) carry a lot The forecasted increase in Let's Go's sales can be seen in the of consumer clout. company's sales projections presented in Exhibit 1 (actual for According to the National Opinion Research Center at _ the years 2005 through 2010 and projected for the years 2011 the University of Chicago, 74\% of boomers (aged 47-65) through 2015). Although the weather can have a significant own their own home, 46% are satisfied with their financial_ impact on the travel trailer industry (i.e., hurricane season, situation, and 56% are married. The spending power of flooding, and even droughts have had negative effects on the this demographic is likely to increase. People who are 50 sales and rentals of travel trailers), Let's Go's management years old and older are expected to inherit an estimated believes these problems will be mitigated in the future $14 to $20 trillion dollars during the next twenty years. by global warming. All sales projections are done by Mark Also, baby boomers make up a significant part of the total Newman in his role as Let's Go's president. To keep from loxing sales, the company maintains fininhed spods inventory on hand at the end of each month equal to 300 contract, and her effoess to locate an alternative vendof, trailen plus 20% of the next menth's sales. The finished roods . willing to accept the conditions of a just in-time contract: inventory on December 31, 2010, was badgeted to be 1,000 current aluminum vender refuice to sign a just in-time prime trailen. Jim West, Let' Gojvice president of marketiry'g and vendor contract due to Tom's uneven production schedule sales, would rather ree a minimum finished goods inventory and his refusal to pay on time. Tom has been seen reading of no less than 1,500 trailers. Jim refuses to talk to Tom Sloan, the belp wanted ads, and Vicky over heard him talking to an Lee's Go's production managet. Tom is always trying to get employment agency. Jim to consider adopeing flexible inventory levels, which Jim _ In kecpine with the policy net by Tom at Let's Gov is certain woeld affect his yearly bonus. The vice prevident of production manager, the amount of sheet aluminum on hand sales and marketing is cligible for a 205 bonus bused on sales. at the end of cach month must be equal to one half of the Unfortunately, Jim did noc receive a bonus in 2010 . Sales were following month's production needs for sheet aluminum. up, but Mark refused to give Jim the bonux, although it was The raw materials inventory on December 31, 2010, wat carned, due to the high number of customer cumplaints. Jim budgeted to be 39.000 aquare yardu. The company does noe was really steamed when he heard "no bonus." Didn't Mark__kep track of work-in-process inventonies. know those complain were for poot quality? All of Jim's efferts Budected cxpenses fot Aluminum and ocher muterinh, to grow sales and attract customers werc, once again, destroyed as well as wages, beat, lipht and power, equipment rental, by Tom Sloan and his production failures. equipment purchases, depreciation, and selling and TRAILER PRODUCTION administrative for the first six aronths of 2011 are givea belowe: Sheet aluminum represents the company's single most expensive raw material. Each travel trailer requires 30 tquare yatds of sheet aluminum. The wholetale cost of sheet aluminum varies dramatically according to the time of year. The cost per square yard can vary from 515 in the spring. when new construction tends to start, to $8 in December and January, when demand is lowest. The use of aluminum in vehicles, including travel trailers, is incteasing rapidly due to a beightened need for fuel efficient, environmentally friendly vehicles. Aluminum can provide a weight savingr of up to 55% compated to an equivalent steel structure, improving gas mileage significanely. The aluminum industry and suppliers are dispersed across four-fiftht of the country, yet they are Largely concentrated in four regions: the Pacifie Northwer, industrial Midwest, nottheastein seaboard, and mid-South. Although this is a broad iscographic presence, Let's Go Aero will be affected by distribution costs. Vicky Drapet, Let's Go's vice president of purchaving and materiak handling. is eager to implement just-in-time as a way of lowering Let's Go's aluminum cost. To offiet the expence of distribution, Let's Go is located in Penasylvania. Vicky's projected 20 os bontw, recently announced by Mark and effectivo for year-end 2011 , is based on her ability to lower total material cost. Initially enthusiastie about her job and ability to earn a significant bonus, Vicky has become discouraged and angry, She is unable to convince let's Go's current aluminum supplier to sign a prime vender COMPETITION CASH All forms of vacation and leisure activities, including theme Let' Go's vice president of finance. Becky Newman, has : parks, beach or cabin rentak, health spas, resorts, and cruise requested an $00,000,90day loan from the bank at a yet to vacations compete with Let's Go Aero Travel Trailers for be determine interest rate. Since Let's Go has experienced the consumer dollar. Other recreational purchasers such as difficulty in paying off its loani in the past, the loan officer automobiles, snowmobiles, boses, and jet-skis are at the bank has asked the company to prepare a cash budget inditect comperitors Thavel trailer manufacturers such as Crossroads RV, Jayco, requested loan amount. The eash balance on January 1,2011 , Caachman RV, and Scamp also offer a moderate-to low-priced is budgeted at $100,000 (the minimum cash balance required travel trailer. Manufacturers that offer more diverse product__ by Let's Go's board of directors). lines such as high-end trailers with luxury accommodations could compete for the fairly affluent senioc market. HUMAN RESOURCES Coachman RV, a direct Let's Go competitor, has become a leader in the recreational vehicle, motor home, and travel To accomplish the company's corponate stratepic roak, Let' Go trailer industry through a commitment to quality and value _ Acro Thavel Trailers encourages upward communicarion among based on excellence in engincering and attention to detail. all its employees, from senior management to line employees. Creative engineering, combined with high-accuracy analysis, Decivion making although not an entirely democratic process, reduced material costs at Coachman by mote than 60% and _ is hased on a team approach, Newman, as Let' Gos peesident, labor costs by 78%. encourager managers to think in terns of the marketplace BUDGET PREPARATION and to look at the business of travel trailers as a whole rather than as functional department successes and decisions. In face, Newman is so committed to the idea of cooperative To minimizc company time lost on clerical work, Let's management and teaenwork that he has hited thee separate Go's accounting department prepares and distributes all budgets to the various departments every six months. Per Mark Newman, "Frecing departmental manugers froms the budgeting process allows them to concentrate on more pressing matters." In keeping with the recently announced bonus plan for the vice president of purchasing and materials handling, Newman has instructed the accounting human resource consultants in the pust six months to lead the department to budget aluminum at $8 per square foot. The accounting manager recently received a 20% bonus for company's mansgers through team-building exercives. having prepared the budgets on time with little or no help REQUIRED from the other functional aresk. Dircuss the advantages and diadvantages of the budgets ceah budgets should remain as presented in question 2 you have prepared. Who in the eompany does the budget. Lse the following approach for the production budget help and whom, potentially, does it hur. Does the budget Discuss the advantager and dicadvantages of the second belp or hurr the saler department? What about production and third sets of production, purchasing and cash budects and finance? How are the various functional ateas affected you have prepared Who within the company do these and why? budaces help and whom, potentially do they hurt? Do these 3. Andy Baxter, newly hired by Let's Go Aero from a budgets help or hurt the sales department? What about competitor, suggeses preparing the production budget production and finance? How ate the various functional ussuming stable production. Prepate a second and third set of production, purchasing, and cash budgets. Hold production to a constant 3,000 thailers per month for the second sec of budgets and 3,500 tratees per month for the thind ser of budzets. The format for the purchaving and FYMIRIT 1 4. What metric should Let's Go eve to mekure the Actual wales in dollan for the last rwo months of 2010 and perfotmance of each manager in this case? What boous bodected sales fot the first six months of 2011 follow: y)etem woald you sugect that incomporates these meaures and also encourares the managers to work as a team? The detail sales for 2010 (actaal) and 2011 (pejected) by manth ate as follow: Pase experietce abow that 25% fof a month's wics are the ale, and 606 in the secoed month followise the sale. The remainder is uncollectible. ABOUT 1MA With a worldwile necwork of more than 60.000 professionaly, IMMA the woldry leadin e creanination dedicated to empoweting accounting and finasce pecfevionsls to drive: butinets performance. iMA provides a by tamic fonms for preofersionals to adrasce their carten though Cemified profemisnal education, necwirtieg and advocisy of the hiphest ectical and perleusecal standantu. Fot moet Buidgeted sales PRODUCTION BUDGET Add: desired ending inventory Total needs Less: beginning inventory Traller production PURCHASES BUDGET \begin{tabular}{l} \hline \\ Traler production \\ Sheet metal needs per traller (S \\ Total production needs (oo0s) \\ Add desired ending inventony \end{tabular} Add: desired ending inventory Total materials needs Less: beginning inventory 39 Total sheet metal purchases Cost per square yard Total cost (000s) \begin{tabular}{|l|} \hline \\ \hline Cash beginning balance. \\ Add ash collections \end{tabular} Add: cash collections November December Ianuary February March io April 31 May 32. June 33 Total cash collections 34 Total cash avallable 15 tess: cash disbursements \begin{tabular}{ll} 36 & Aluminum \\ \hline 37 & Othermateriats \\ 38 & Wages \\ 39 & Heat, light, \& power \\ 40 & Equipment rental \\ \hline \end{tabular} Equipment purchases Selling \& admin Total cash disbursements Excess (deficiency) Financing: Bortowinge: Original Botrowings: Additional Hepayments 49 Totalfinancing 12 Sheet metal needs per traller (Sq yds) 13 Total production needs (O00s) 14 Add: desired ending inventory 15. Total materials needs 16 Less: beginning irventory 39 17 Total sheet metal purchases 18 Cost per square yard 19 Total cost (000s) 20] 21] CASHBUDGET 22 Cash beginning balance 24 Add: cash collections 25 November 26 December 27 lanuary 28 February 29 March 30 April 31 May 32 lune. 33 Total cash collections 34 Total cash avallable 35 Less: cash disbursements \begin{tabular}{|l|l|} \hline 36 & Aluminum \\ \hline 37 & Othermaterials \\ \hline 38 & Wages \\ \hline 39 & Heat, light, \& power \\ 40 & Equipment rental \\ \hline 41 & Ecupiment purchases \\ \hline \end{tabular} Equpment puirchases 42 Selling \& admin 43 Total cash disbursements 44 Excess (deficiency). 45 Financing: 46 Borrowings: Original 47 Borrowings: Additional 48 Repsyments. 49 Total financing 50 Cash balance ending. 51 To be invested (excess) 52 53 54 U.S. population. According to the U.S. Census Bureau, in 2006 baby boomers represented 26% of the populace. In Let's Go Acro manufactures travel trailers bought primarily_ that year there were just under 78 million boomers living by young families and retirees interested in a light, low-cost in the United States, with the largest populations living in trailer that can easily be pulled by a mid-sized family car. The _California, Texas, New York, Florida, and Pennsylvania. market for travel trailers has expanded nicely over the past _ Research indicates that for an organization to meet the few years due to the number of families seeking a relatively_ needs of the senior marker, including baby boomers, the low-cost, outdoor vacation experience. But in the view of Let's following must be addressed: Go Aero's president, Mark Newman, the real growth in the future is in the retiree market. Newman believes the vigorous - Independence and control, health of the average retiree, coupled with theyotional trend toward a return to nature, will translate into continuing sales - Intellectual stimulation and self-expression, growth for Let's Go. As Newman loves to say, "camping - Security and peace of mind, - Quality and value. recently moved from number seven to number six on the list Seniors respond to benefit-driven messages; to attract of top 10 leisure activities in the United States, and the baby them, advertising has to communicate tangible benefirs boomers are getting older every day." rather than features and amenities. THE RETIREE MARKET MARKETING AND SALES Baby boomers (born between 1/1/46 and 12/31/64) carry a lot The forecasted increase in Let's Go's sales can be seen in the of consumer clout. company's sales projections presented in Exhibit 1 (actual for According to the National Opinion Research Center at _ the years 2005 through 2010 and projected for the years 2011 the University of Chicago, 74\% of boomers (aged 47-65) through 2015). Although the weather can have a significant own their own home, 46% are satisfied with their financial_ impact on the travel trailer industry (i.e., hurricane season, situation, and 56% are married. The spending power of flooding, and even droughts have had negative effects on the this demographic is likely to increase. People who are 50 sales and rentals of travel trailers), Let's Go's management years old and older are expected to inherit an estimated believes these problems will be mitigated in the future $14 to $20 trillion dollars during the next twenty years. by global warming. All sales projections are done by Mark Also, baby boomers make up a significant part of the total Newman in his role as Let's Go's president. To keep from loxing sales, the company maintains fininhed spods inventory on hand at the end of each month equal to 300 contract, and her effoess to locate an alternative vendof, trailen plus 20% of the next menth's sales. The finished roods . willing to accept the conditions of a just in-time contract: inventory on December 31, 2010, was badgeted to be 1,000 current aluminum vender refuice to sign a just in-time prime trailen. Jim West, Let' Gojvice president of marketiry'g and vendor contract due to Tom's uneven production schedule sales, would rather ree a minimum finished goods inventory and his refusal to pay on time. Tom has been seen reading of no less than 1,500 trailers. Jim refuses to talk to Tom Sloan, the belp wanted ads, and Vicky over heard him talking to an Lee's Go's production managet. Tom is always trying to get employment agency. Jim to consider adopeing flexible inventory levels, which Jim _ In kecpine with the policy net by Tom at Let's Gov is certain woeld affect his yearly bonus. The vice prevident of production manager, the amount of sheet aluminum on hand sales and marketing is cligible for a 205 bonus bused on sales. at the end of cach month must be equal to one half of the Unfortunately, Jim did noc receive a bonus in 2010 . Sales were following month's production needs for sheet aluminum. up, but Mark refused to give Jim the bonux, although it was The raw materials inventory on December 31, 2010, wat carned, due to the high number of customer cumplaints. Jim budgeted to be 39.000 aquare yardu. The company does noe was really steamed when he heard "no bonus." Didn't Mark__kep track of work-in-process inventonies. know those complain were for poot quality? All of Jim's efferts Budected cxpenses fot Aluminum and ocher muterinh, to grow sales and attract customers werc, once again, destroyed as well as wages, beat, lipht and power, equipment rental, by Tom Sloan and his production failures. equipment purchases, depreciation, and selling and TRAILER PRODUCTION administrative for the first six aronths of 2011 are givea belowe: Sheet aluminum represents the company's single most expensive raw material. Each travel trailer requires 30 tquare yatds of sheet aluminum. The wholetale cost of sheet aluminum varies dramatically according to the time of year. The cost per square yard can vary from 515 in the spring. when new construction tends to start, to $8 in December and January, when demand is lowest. The use of aluminum in vehicles, including travel trailers, is incteasing rapidly due to a beightened need for fuel efficient, environmentally friendly vehicles. Aluminum can provide a weight savingr of up to 55% compated to an equivalent steel structure, improving gas mileage significanely. The aluminum industry and suppliers are dispersed across four-fiftht of the country, yet they are Largely concentrated in four regions: the Pacifie Northwer, industrial Midwest, nottheastein seaboard, and mid-South. Although this is a broad iscographic presence, Let's Go Aero will be affected by distribution costs. Vicky Drapet, Let's Go's vice president of purchaving and materiak handling. is eager to implement just-in-time as a way of lowering Let's Go's aluminum cost. To offiet the expence of distribution, Let's Go is located in Penasylvania. Vicky's projected 20 os bontw, recently announced by Mark and effectivo for year-end 2011 , is based on her ability to lower total material cost. Initially enthusiastie about her job and ability to earn a significant bonus, Vicky has become discouraged and angry, She is unable to convince let's Go's current aluminum supplier to sign a prime vender COMPETITION CASH All forms of vacation and leisure activities, including theme Let' Go's vice president of finance. Becky Newman, has : parks, beach or cabin rentak, health spas, resorts, and cruise requested an $00,000,90day loan from the bank at a yet to vacations compete with Let's Go Aero Travel Trailers for be determine interest rate. Since Let's Go has experienced the consumer dollar. Other recreational purchasers such as difficulty in paying off its loani in the past, the loan officer automobiles, snowmobiles, boses, and jet-skis are at the bank has asked the company to prepare a cash budget inditect comperitors Thavel trailer manufacturers such as Crossroads RV, Jayco, requested loan amount. The eash balance on January 1,2011 , Caachman RV, and Scamp also offer a moderate-to low-priced is budgeted at $100,000 (the minimum cash balance required travel trailer. Manufacturers that offer more diverse product__ by Let's Go's board of directors). lines such as high-end trailers with luxury accommodations could compete for the fairly affluent senioc market. HUMAN RESOURCES Coachman RV, a direct Let's Go competitor, has become a leader in the recreational vehicle, motor home, and travel To accomplish the company's corponate stratepic roak, Let' Go trailer industry through a commitment to quality and value _ Acro Thavel Trailers encourages upward communicarion among based on excellence in engincering and attention to detail. all its employees, from senior management to line employees. Creative engineering, combined with high-accuracy analysis, Decivion making although not an entirely democratic process, reduced material costs at Coachman by mote than 60% and _ is hased on a team approach, Newman, as Let' Gos peesident, labor costs by 78%. encourager managers to think in terns of the marketplace BUDGET PREPARATION and to look at the business of travel trailers as a whole rather than as functional department successes and decisions. In face, Newman is so committed to the idea of cooperative To minimizc company time lost on clerical work, Let's management and teaenwork that he has hited thee separate Go's accounting department prepares and distributes all budgets to the various departments every six months. Per Mark Newman, "Frecing departmental manugers froms the budgeting process allows them to concentrate on more pressing matters." In keeping with the recently announced bonus plan for the vice president of purchasing and materials handling, Newman has instructed the accounting human resource consultants in the pust six months to lead the department to budget aluminum at $8 per square foot. The accounting manager recently received a 20% bonus for company's mansgers through team-building exercives. having prepared the budgets on time with little or no help REQUIRED from the other functional aresk. Dircuss the advantages and diadvantages of the budgets ceah budgets should remain as presented in question 2 you have prepared. Who in the eompany does the budget. Lse the following approach for the production budget help and whom, potentially, does it hur. Does the budget Discuss the advantager and dicadvantages of the second belp or hurr the saler department? What about production and third sets of production, purchasing and cash budects and finance? How are the various functional ateas affected you have prepared Who within the company do these and why? budaces help and whom, potentially do they hurt? Do these 3. Andy Baxter, newly hired by Let's Go Aero from a budgets help or hurt the sales department? What about competitor, suggeses preparing the production budget production and finance? How ate the various functional ussuming stable production. Prepate a second and third set of production, purchasing, and cash budgets. Hold production to a constant 3,000 thailers per month for the second sec of budgets and 3,500 tratees per month for the thind ser of budzets. The format for the purchaving and FYMIRIT 1 4. What metric should Let's Go eve to mekure the Actual wales in dollan for the last rwo months of 2010 and perfotmance of each manager in this case? What boous bodected sales fot the first six months of 2011 follow: y)etem woald you sugect that incomporates these meaures and also encourares the managers to work as a team? The detail sales for 2010 (actaal) and 2011 (pejected) by manth ate as follow: Pase experietce abow that 25% fof a month's wics are the ale, and 606 in the secoed month followise the sale. The remainder is uncollectible. ABOUT 1MA With a worldwile necwork of more than 60.000 professionaly, IMMA the woldry leadin e creanination dedicated to empoweting accounting and finasce pecfevionsls to drive: butinets performance. iMA provides a by tamic fonms for preofersionals to adrasce their carten though Cemified profemisnal education, necwirtieg and advocisy of the hiphest ectical and perleusecal standantu. Fot moet Buidgeted sales PRODUCTION BUDGET Add: desired ending inventory Total needs Less: beginning inventory Traller production PURCHASES BUDGET \begin{tabular}{l} \hline \\ Traler production \\ Sheet metal needs per traller (S \\ Total production needs (oo0s) \\ Add desired ending inventony \end{tabular} Add: desired ending inventory Total materials needs Less: beginning inventory 39 Total sheet metal purchases Cost per square yard Total cost (000s) \begin{tabular}{|l|} \hline \\ \hline Cash beginning balance. \\ Add ash collections \end{tabular} Add: cash collections November December Ianuary February March io April 31 May 32. June 33 Total cash collections 34 Total cash avallable 15 tess: cash disbursements \begin{tabular}{ll} 36 & Aluminum \\ \hline 37 & Othermateriats \\ 38 & Wages \\ 39 & Heat, light, \& power \\ 40 & Equipment rental \\ \hline \end{tabular} Equipment purchases Selling \& admin Total cash disbursements Excess (deficiency) Financing: Bortowinge: Original Botrowings: Additional Hepayments 49 Totalfinancing 12 Sheet metal needs per traller (Sq yds) 13 Total production needs (O00s) 14 Add: desired ending inventory 15. Total materials needs 16 Less: beginning irventory 39 17 Total sheet metal purchases 18 Cost per square yard 19 Total cost (000s) 20] 21] CASHBUDGET 22 Cash beginning balance 24 Add: cash collections 25 November 26 December 27 lanuary 28 February 29 March 30 April 31 May 32 lune. 33 Total cash collections 34 Total cash avallable 35 Less: cash disbursements \begin{tabular}{|l|l|} \hline 36 & Aluminum \\ \hline 37 & Othermaterials \\ \hline 38 & Wages \\ \hline 39 & Heat, light, \& power \\ 40 & Equipment rental \\ \hline 41 & Ecupiment purchases \\ \hline \end{tabular} Equpment puirchases 42 Selling \& admin 43 Total cash disbursements 44 Excess (deficiency). 45 Financing: 46 Borrowings: Original 47 Borrowings: Additional 48 Repsyments. 49 Total financing 50 Cash balance ending. 51 To be invested (excess) 52 53 54