Hi I’m working on 12 month sales forecasting
Hi I’m working on 12 month sales forecasting and I need to know how to set it up in excel based on the criteria
in the attached file ATTACHMENT PREVIEW Download attachmentCase 7 James Confectoners—ParT 2How can a small confectoner forecasT cash Fow?Telford James and his wife Ivey, the second-genera±on owners of James Confec±oners, a family-ownedmanufacturer of premium chocolates that was started by Telford’s father, Frank, in 1964 in Eau Claire,Wisconsin, have become increasingly concerned that turmoil in the banking and ²nancial industriescould have a nega±ve impact on their business. They have read the headlines about bank closures,heightened government scru±ny of the banking industry, and ±ght credit condi±ons, especially for smallbusinesses. The company has a $150,000 line of credit with Maple Leaf Bank, but the Jameses want toincrease it to $250,000 as a precau±onary move. Last week, they contacted Claudia Fernandes, theirpersonal banker at Maple Leaf, about increasing their line of credit. Fernandes said that in addi±on toreviewing the James Confec±oners’ most recent balance sheet and income statement, she would need acash ³ow forecast for the upcoming year.Although Telford and Ivey have prepared budgets for James Confec±oners and have analyzed their²nancial statements using ra±o analysis, they have not created a cash ³ow forecast before. They expectsales to increase 6.2 percent next year to $4,139,213. Credit sales account for 96 percent of total sales,and the company’s collec±on pa´ern for credit sales is 8 percent in the same month in which the sale isgenerated, 54 percent in the ²rst month aµer the sale is generated, and 34 percent in the second monthaµer the sale is generated. The Jameses have gathered the following es±mates from their budget for theupcoming year:The company’s cash balance as of January 1 is $22,565. The interest rate on James Confec±oners’ currentline of credit is 8.25 percent.1.Develop a monthly cash budget for James Confec±oners for the upcoming year.2.What recommenda±ons can you o¶er Telford and Ivey James to improve their company’s cash³ow?3.If you were Claudia Fernandes, the James’s banker, would you be willing to increase thecompany’s line of credit? Explain.
Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"
