Complete the Attached Excel File.A workbook pdf is attached to be used as instructions, guide while completing the excel spreadsheet.
CH 7 case study ACC 232NAME CH 7 Case Study Workbook AP-15B on pp 187-190 LimeLemon Inc. Sales Budget For the quarter ended June 30, 2019 AprilMayJuneTotal LimeLemon Inc. Production Budget For the quarter ended June 30, 2019 AprilMayJuneTotal LimeLemon Inc. Direct Material Purchases Budget For the quarter ended June 30, 2019 AprilMayJuneTotal LimeLemon Inc. Purchasing Budget For the quarter ended June 30, 2019 AprilMayJuneTotal LimeLemon Inc. Forecasted Cash Sales and Credit Sales For the quarter ended June 30, 2019 AprilMayJuneTotal LimeLemon Inc. Schedule of Cash Receipts For the quarter ended June 30, 2019 AprilMayJuneTotal LimeLemon Inc. Schedule of Cash Disbursements For the quarter ended June 30, 2019 AprilMayJuneTotal LimeLemon Inc. Cash Budget For the quarter ended June 30, 2019 AprilMayJuneTotal Chapter 7Master Budget 189 AP-15B LO 4 6 a) LimeLemon Inc. is a manufacturer of athletic wear for running and yoga. They sell premium athletic pants. LimeLemon Inc. has a March 30 year end. Estimated sales information for the upcoming fiscal year are as follows: Month Sales in units Sales Price April 440 $75/unit May 520 $75/unit June 480 $90/unit July 510 $85/unit August 300 $85/unit Prepare the sales budget for the first quarter. limelemon inc. Sales Budget For the Quarter ended June 30, 2019 April May June Period end b) When creating the budget, management likes to have extra inventory on hand in the event that actual sales are higher than budgeted. Management has determined that 20% of the following month’s budgeted sales should be sufficient for the extra inventory. Ending inventory at March 31, 2019 was 100 units. Prepare the production budget for the first quarter. limelemon inc. Production Budget For the Quarter ended June 30, 2019 April May June Period end c) Each pair of pants requires 2.5 yards of fabric and each yard costs $12. Management budgets for extra materials in the event that there is a shortage from suppliers. Management would like to have 20% of the following month’s production needs in ending inventory each month. Ending inventory of fabric at March 31, 2018 is 300 yards. Prepare the direct materials budget and purchasing budget for the first quarter. Chapter 7 Master Budget 190 limelemon inc. direct Material Purchases Budget For the Quarter ended June 30, 2019 April May June Period end limelemon inc. Purchasing Budget For the Quarter ended June 30, 2019 April May June Period end d) LimeLemon’s sales history indicates that 35% of sales will be for cash and 65% of sales will be on credit. Using the information from the sales budget, the forecasted cash and credit sales can be determined: limelemon inc. Forecasted Cash Sales and Credit Sales For the Quarter ended June 30, 2019 April May June Period end LimeLemon has reviewed past collection history and has determined that credit sales will be collected as follows: 60% is collected in the month of sale and 40% is collected the following month. The total credit sales for the month of March 2019 were $45,000. Prepare the budgeted schedule of cash receipts for the first quarter. limelemon inc. Schedule of Cash receipts For the Quarter ended June 30, 2019 April May June Period end Chapter 7Master Budget 191 e) LimeLemon will pay for 50% of its purchases of material in the month the material is purchased, and the remaining 50% in the following month. The total credit purchases for the month of March 2019 were $25,000. Prepare the budgeted schedule of cash disbursements for the first quarter. limelemon inc. Schedule of Cash disbursements For the Quarter ended June 30, 2019 April May June Period end f ) Prepare a cash budget for the first quarter. Assume the following: 1) LimeLemon has access to an open line of credit with the bank to borrow money if needed. To keep things simple, ignore interest calculations on the bank borrowings. Also, loan borrowings and repayments are taken in multiples of $1,000. 2) LimeLemon started the year with $11,300 in its bank account and must maintain a minimum cash balance of $4,000 at the end of each month. 3) LimeLemon maintains that it will repay debts as soon as possible with any excess cash that becomes available. 4) LimeLemon has budgeted the following for 2019: • Paymentforoperatingexpenses=$15,600forApril,$14,300forMay,and$12,500forJune • Depreciationexpenseis$2,500amonth. • LimeLemonwillneedtopurchasenewequipmentcosting$38,000.LimeLemonwillpay$8,000in April and the remaining balance in May. • Cashdividendsof$1,000willbepaidtoinallthreemonthsofthefirstquarter. Chapter 7 Master Budget 192 limelemon inc. Cash Budget For the Quarter ended June 30, 2019 April May June Period end