Excel Problem

Melodic Enterprises' goal is to instill the joy of music in every school-aged child in its community. Its instrument of choice is the classic recorder. Music teachers embrace the purity and power of these classic woodwind instruments, which provide a sure stepping-stone to great musical endeavors. Melodic's managers pulled together the following information to plan for its biggest sales month.

Budgeted sales volume is as follows: June 11,500 units, July 14,800, and August 12,400. Budgeted selling price is $3/unit. Melodic plans to have 15% of its next month's sales volume sitting in ending inventory; beginning inventory in July exactly met this policy, with those units held at a cost of $1.75/unit (from prior purchases). Budgeted purchase price of a recorder as of July 1 is $1.80/unit. Melodic uses the FIFO assumption for inventory valuation. Budgeted variable SG&A cost is $0.20/unit sold. Budgeted monthly fixed SG&A costs consist of the following: sales salaries of $3,000, advertising expense of $600, executive salaries of $4,500, depreciation on the facility of $2,300, administrative salaries of $2,600, and property taxes and insurance of $700.

Required

Prepare the following budgets for Melodic Enterprises for the month of July.

Sales forecast. Purchases budget. COGS budget. SG&A budget.

Prepare Melodic's budgeted income statement for the month of July. Since July is a peak sales month for this company, with schools needing recorders for classes in August, when will its cash inflows likely peak related to sales? When will its cash outflows likely peak in terms of its product costs? If there is any disconnect between the timing of these inflows and outflows, explain how Melodic could manage it.